db.Execute failure

[LMC]

I am trying to change the datatype of an AutoNumber field located in a table
where the table and field names are passed to the applicable Funtion via Text
Strings. The code exceprt I am using below errors on the db.Execute line.

strSQL = "ALTER TABLE " & strTmpTbl & _
" ALTER COLUMN " & strID & " INTEGER"
db.Execute strSQL, dbFailOnError

Immediately before the code above, the applicable table structure is a
copied from an existing table via DoCmd.TransferDatabase; therefore I don't
think there are any relationships that could be causing the failure.

Debug shows that the table and field variables are interrupted correctly;
therefore I suspect it's something with the strSQL syntax.

Many thanks for any replies regarding how to fix it.

 

[Douglas J. Steele]

Before you execute it, check what the string is using Debug.Print strSQL.

Once your code runs (and fails), go to the Immediate Window (using Ctrl-G)
and see what's written there. To handle the possibility of either the table
or field name having special characters (which includes spaces) or being a
reserved word, try

strSQL = "ALTER TABLE [" & strTmpTbl & "] " & _
" ALTER COLUMN [" & strID & "] INTEGER"

 

[LMC]

Thank You, adding the brackets around the variable string names fixed it.

Before you execute it, check what the string is using Debug.Print strSQL.

Once your code runs (and fails), go to the Immediate Window (using Ctrl-G)
and see what's written there. To handle the possibility of either the table
or field name having special characters (which includes spaces) or being a
reserved word, try

strSQL = "ALTER TABLE [" & strTmpTbl & "] " & _
" ALTER COLUMN [" & strID & "] INTEGER"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to change the datatype of an AutoNumber field located in a
table
where the table and field names are passed to the applicable Funtion via
Text
Strings. The code exceprt I am using below errors on the db.Execute line.

strSQL = "ALTER TABLE " & strTmpTbl & _
" ALTER COLUMN " & strID & " INTEGER"
db.Execute strSQL, dbFailOnError

Immediately before the code above, the applicable table structure is a
copied from an existing table via DoCmd.TransferDatabase; therefore I
don't
think there are any relationships that could be causing the failure.

Debug shows that the table and field variables are interrupted correctly;
therefore I suspect it's something with the strSQL syntax.

Many thanks for any replies regarding how to fix it.