Ms Access Error 3293
help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's quick & easy. DoCmd.RunSQL "ALTER TABLE" syntax prblem P: 7 Tomba I have a really annoying problem, which i tried solving for almost 3 days now. (I googled in any variation i could think of, but i couldn't find anything that will help my stupid me understand:\) I'm trying to use a ALTER TABLE ALTER COULMN to change the datatype of a certain column in a table i'm importing by code from an .xls to my access database. I'll cut to the chase and paste the code: Option Compare Database Option Explicit Private Sub cmdImport_Click() Dim myfile Dim mypath Dim sheetnum Dim sheetname mypath = "x:\History\" Do myfile = Dir(mypath & "*.xls") If Right(Left(myfile, Len(myfile) - 4), 2) = "04" Then sheetnum = 6 Else sheetnum = 1 End If Do If sheetnum < 10 Then DoCmd.TransferSpreadsheet acImport, 8, "" & Left(myfile, Len(myfile) - 4) & "-0" & sheetnum, mypath & myfile, False, "0" & sheetnum & "!B:H" sheetname = Left(myfile, Len(myfile) - 4) & "-0" & sheetnum Else DoCmd.TransferSpreadsheet acImport, 8, "" & Left(myfile, Len(myfile) - 4) & "-" & sheetnum, mypath & myfile, False, sheetnum & "!B:H" sheetname = Left(myfile, Len(myfile) - 4) & sheetnum End If UpdateTable (sheetname) sheetnum = sheetnum + 1 Loop Until sheetnum = 13 myfile = Dir Loop Until myfile = "" End Sub Function UpdateTable(tblname As String) Dim strSQL As String strSQL = "ALTER TABLE " & tblname & " ALTER COULMN F1 DATETIME;" DoCmd.RunSQL strSQL End Function I thanx in advance to anyone who'll be able to help me! Tomba. Dec 29 '06 #1 Post Reply Share this Question 9 Replies Expert 2.5K+ P: 3,072 nico5038 Hmm, I know there's a problem with the ALTER TABLE comand, but try: strSQL = "ALTER TABLE " & tblname & " ALTER COLUMN F1 DATETIME;" (Switched the UL into LU) Also make sure that the co
Changing Field properties in an Access DBTable If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 11 of 11 Thread: Changing Field properties in an Access DBTable Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch https://bytes.com/topic/access/answers/582288-docmd-runsql-alter-table-syntax-prblem to Threaded Mode 08-20-2010,09:05 AM #1 Talat View Profile View Forum Posts Registered User Join Date Aug 2009 Posts 38 Changing Field properties in an Access DBTable Hi, I need to change the properties of a couple of databse fields in a table. I am using Office 2003 Access application. Its a filed in a linked table wrongly set to TEXT and it http://forums.devx.com/showthread.php?174863-Changing-Field-properties-in-an-Access-DBTable should be CURRENCY. After researching teh web I tried to use the following code but ist giving me the following error message. "Run time Error '3293' - Syntax Error in ALTER TABLE Statement" This is the code I am running: Private Sub Command33_Click() Dim strDB As String, MySQL As String Dim appAccess As Access.Application Const strConPathToExtDB = "C:\Users\Talāt\Desktop\TestDB.mdb" Set appAccess = CreateObject("Access.Application") appAccess.OpenCurrentDatabase strConPathToExtDB MySQL = "ALTER TABLE Ltbl_Products ALTER COLUMN 'Unit Cost' CURRENCY (2);" appAccess.DoCmd.RunSQL MySQL appAccess.CloseCurrentDatabase Set appAccess = Nothing End Sub Can anyone help out please? Thanks. Talāt Reply With Quote 08-23-2010,12:12 PM #2 tkorsano View Profile View Forum Posts Registered User Join Date Oct 2008 Posts 142 maybe the problem are single quotes hi there, in sql, single quotes denote a string, which could be type ntext, varchar, etc. it is used a lot in string comparisons. therefore, i am suggesting to try: "ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY (2);" instead of "ALTER TABLE Ltbl_Products ALTER COLUMN 'Unit Cost' CURRENCY (2);" moreover, check if your access version supports "alter column" statemente because some access versions don't support such statement
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get https://www.experts-exchange.com/questions/21833938/changing-the-column-name-error-3293.html Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers https://docs.oracle.com/cd/E18464_01/doc.30/e18459/errors_access.htm Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > changing the column name error 3293 Want to Advertise Here? Solved changing the column name error 3293 Posted on 2006-05-01 MS Access 3 Verified Solutions 8 Comments 1,682 Views Last Modified: 2011-09-20 hi I am getting the following error Runtime error 3293 syntax error in ms access ALTER TABLE STATEMENT while implementing Dim sqlStatement1 As String ''' declaring the SQl statement string sqlStatement1 = "ALTER TABLE tblImportedFATS CHANGE F1 ID INTEGER" ''' the reqd SQL sstatement Dim dbImported As DAO.Database ''' declaring the variable for the DB Dim tblImportedFATS As DAO.TableDef '''declaring the table Set dbImported = CurrentDb ''' assinging the Set tblImportedFATS = dbImported.TableDefs("tblFATS1") DoCmd.RunSQL sqlStatement1 in the debug mode, I checked the columns of the tblImportedFATS and ms access error they (F1, F2, F3,...... ) are all there Am I am using any SQL keyword not supported by the MS access/ or do I need to add any libraries ??? Please Help Thanks, st 0 Question by:spacetraveller Facebook Twitter LinkedIn Google LVL 4 Best Solution bypaulgrunner What about using: tblImportedFATS.Fields("F1").Name ="ID" instead of the SQL command? Go to Solution 8 Comments LVL 77 Overall: Level 77 MS Access 67 Message Expert Comment by:peter57r2006-05-01 Hi spacetraveller, Look in on-line Help ALTER TABLE MyTable ALTER COLUMN myfield COUNTER CONSTRAINT PrimaryKey PRIMARY KEY Pete 0 LVL 77 Overall: Level 77 MS Access 67 Message Expert Comment by:peter57r2006-05-01 spacetraveller, sorry - ignore that I thought you were doing something else Pete 0 LVL 77 Overall: Level 77 MS Access 67 Message Assisted Solution by:peter57r2006-05-01 spacetraveller, Now that I realise you want to rename a column I can give you tha bad news that you can't do that on JET sql. To rename a column using DDL SQL (Data Definition Language) you have to add a column: ALTER TABLE mytable ADD COLUMN NewName datatype(size) Then run an update statement to copy the data from the old column to the new one: UPDATE mytable SET NewName = OldName Then drop the old colum
following sections: Section A.1, "Error Message Classes" Section A.2, "Microsoft Jet Database Engine Error Messages" A.1 Error Message Classes Table A-1 describes the classes that the Microsoft Jet database engine error messages belong: Table A-1 Error Message Classes Class Description BTRIEVE BTRIEVE installable ISAM-specific errors (Microsoft Jet 2.5 and earlier). DAO DAO-specific errors. DBASE dBASE installable ISAM-specific errors. DDL Data Definition Language-specific errors. EXCEL Microsoft Excel installable ISAM-specific errors. EXTENDED Errors that may have extended error information. IMEX Generic import/export errors. INST ISAM Generic installable ISAM errors. INTERNET Internet-specific errors. ISAM Generic Microsoft Jet ISAM errors. JPM Microsoft Jet database engine errors related to property management. MISC Microsoft Jet database engine errors that don't fit into another category. PARADOX Paradox installable ISAM-specific errors. PARSE Microsoft Jet database engine expression parsing errors. QUERY Microsoft Jet database engine errors related to queries. REF INTEGRITY Microsoft Jet database engine errors related to referential integrity. REMOTE Microsoft Jet database engine errors specific to ODBC. REPLICATOR Microsoft Jet database engine errors related to replication. SECURITY Microsoft Jet database engine errors related to security. TEXT Text installable ISAM-specific errors. TLV Table-level validation errors. UNUSED Microsoft Jet database engine errors that are no longer used or that have special meaning. Errors that have special meaning are usually translations from other errors and are not generated in the Microsoft Jet database engine code. A.2 Microsoft Jet Database Engine Error Messages Table A-2 lists the Microsoft Jet database engine database engine error messages. An asterisk (*) means that there is no Microsoft Jet error message text for a particular error message. An italicized item between angled brackets, such as