ALTER Table/UPDATE Syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
AnnMarie said:
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!
Click to expand...

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
 
Concatenate the value of the variable into the string expression, e.g.

"ALTER TABLE " & strTransFile & " ADD COLUMN Range Int"
 
AnnMarie said:
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
Click to expand...

currentdb.execute "ALTER TABLE " & strTransFile & " ADD COLUMN Range Int"
DoCmd.RunSQL "UPDATE ExcelTable SET Range = intDayRange", 0
Click to expand...

currentdb.execute "UPDATE ExcelTable SET Range = " & intDayRange
 
Thanks so much for the speedy replies! I ended up concantenating my original
code, but I also appreciate the examples of alternative methods.
 
Back
Top