Re: ALTER Table/UPDATE Syntax

Tech-Archive recommends: Fix windows errors by optimizing your registry

"AnnMarie" <AnnMarie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:711B4B91-7FA2-4B98-8689-C1CD8E1239F6@xxxxxxxxxxxxx
> 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.TransferSpread*** 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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Quantcast