VBA modify a table with the SQL Alter Statement Syntax Error

[dowlingm815]

A field needs to be modified from text to date after importing from a CSV file. the CSV does not acknowledge the date/time field through the import specification. therefore, a routine needs to be added immediately following the import of the CSV file.

I have encounter a couple of ways to perform this routine. I have success when changing only one field in the following code.

strSQL = "ALTER TABLE PODataFull ALTER COLUMN [Workflow Completion Date] DATETIME;"
 

However, when i attempted to alter multiple fields within a table using an SQL statement, a syntax error is generated.

strSQL = "ALTER TABLE PODataFull " & _
            "MODIFY ([Workflow Completion Date] DATE, " & _
                    "[PO Closed Date] DATE);"
 

Any help would be appreciated.

mary

[ADezii]

Not positive if can can Modify Multiple Fields within a single SQL Statement, but the following Syntax will work:

Dim strSql As String
Dim strSql2 As String
 
strSql = "ALTER TABLE tblData ALTER COLUMN Field1 DATE;"
  DBEngine(0)(0).Execute strSql, dbFailOnError
 
strSql2 = "ALTER TABLE tblData ALTER COLUMN Field2 DATE;"
  DBEngine(0)(0).Execute strSql2, dbFailOnError

[dowlingm815]

Good morning ADezii,

thank you for your prompt reply. i was hoping for multiple updates. i can't understand why the SQL can't translate VBA as in the second set of code which was obtained from this site:

** Edit ** Illegal link removed.

[ADezii]

Not sure what is going on, since the MODIFY Statement does not show up as an Option with ALTER TABLE for either Jet or Transact SQL.

[NeoPa]

ADezii,s quite right Mary. That syntax is not supported in Jet SQL. For help with that see Finding Jet SQL Help.

Please remember when reporting a problem to include the error message as well as the number and the code that errored (The line is generally identified by the yellow highlight if you select Debug).

PS The link you included was not for Access (Now removed as it breaks the site rules).