ALTER Table/UPDATE Syntax

[Guest]

Is it possible to pass a variable when using the ALTER Table and UPDATE
commands? I've used the code below, which works when I specify the actual
table name and number for days, but states it can't find the table when I use
a variable. I'm currently using Access 97. Is there a specific syntax I
need to use to differentiate the variable names?

DoCmd.TransferSpreadsheet acImport, 8, strTransFile, strInFile, False, ""
DoCmd.RunSQL "ALTER TABLE strTransFile ADD COLUMN Range Int", 0
DoCmd.RunSQL "UPDATE ExcelTable SET Range = intDayRange", 0

I have three Excel files to import and depending on the specific file, the
intDayRange will be different for each file. I'd like to use reuse the code
for each file. After the updates I plan to append them to a main table.

Thanks!

 

[Dirk Goldgar]

Is it possible to pass a variable when using the ALTER Table and
UPDATE commands? I've used the code below, which works when I
specify the actual table name and number for days, but states it
can't find the table when I use a variable. I'm currently using
Access 97. Is there a specific syntax I need to use to differentiate
the variable names?

DoCmd.TransferSpreadsheet acImport, 8, strTransFile, strInFile,
False, "" DoCmd.RunSQL "ALTER TABLE strTransFile ADD COLUMN Range
Int", 0 DoCmd.RunSQL "UPDATE ExcelTable SET Range = intDayRange", 0

I have three Excel files to import and depending on the specific
file, the intDayRange will be different for each file. I'd like to
use reuse the code for each file. After the updates I plan to append
them to a main table.

Thanks!

You need to build the variable values, rather than their names, into the
SQL strings. Like this:

DoCmd.RunSQL "ALTER TABLE [" & strTransFile & "] ADD COLUMN Range
Int", 0
DoCmd.RunSQL "UPDATE ExcelTable SET Range = " & intDayRange, 0

 

[Guest]

Concatenate the value of the variable into the string expression, e.g.

"ALTER TABLE " & strTransFile & " ADD COLUMN Range Int"

 

[Bas Cost Budde]

Is it possible to pass a variable when using the ALTER Table and UPDATE
commands? I've used the code below, which works when I specify the actual
table name and number for days, but states it can't find the table when I use
a variable. I'm currently using Access 97. Is there a specific syntax I
need to use to differentiate the variable names?

DoCmd.TransferSpreadsheet acImport, 8, strTransFile, strInFile, False, ""
DoCmd.RunSQL "ALTER TABLE strTransFile ADD COLUMN Range Int", 0

currentdb.execute "ALTER TABLE " & strTransFile & " ADD COLUMN Range Int"

DoCmd.RunSQL "UPDATE ExcelTable SET Range = intDayRange", 0

currentdb.execute "UPDATE ExcelTable SET Range = " & intDayRange

 

[Guest]

Thanks so much for the speedy replies! I ended up concantenating my original
code, but I also appreciate the examples of alternative methods.