INSERT INTO Query Converted to VBA

[claencemo]

Hello all,

I have a append query that works great if I just run the query via Access. I
need to convert that query into VBA code. Here is the SQL View of the query
I'm trying to convert:

INSERT INTO TblInitiator IN 'I:\Archive\TestFinal\080107\TS3000v101_be.mdb'
SELECT tblAddInitiator.*
FROM tblAddInitiator;

What the query does is append the table called tblInitiator located in a
network location from values in a table called tblAddInitiator. There is only
one field in each of the tables both called fldInitiator

I tried this VBA statement (all on 1 line)

AppendDB = "INSERT INTO 'TblInitiator' IN [I:\Archive\TestFinal\080107\
TS3000v101_be.mdb] SELECT 'tblAddInitiator.*' FROM 'tblAddInitiator';"

I get the message:
Syntax error in query. Incomplete query clause

What am I missing from the statement? Help!!

Thanks in advance,

Clarence

 

[Douglas J. Steele]

You don't need the single quotes around the table names.

AppendDB = "INSERT INTO TblInitiator IN
[I:\Archive\TestFinal\080107\TS3000v101_be.mdb] SELECT tblAddInitiator.*
FROM tblAddInitiator;"

 

[claencemo]

Douglas,

Thanks for the info. I tried that: (all on one line)

AppendDB = "INSERT INTO TblInitiator IN [I:\Archive\TestFinal\080107\
TS3000v101_be.mdb] SELECT tblAddInitiator.* FROM tblAddInitiator;"

I get this dialog box message:
Invalid bracketing of name '[I:\Archive\TestFinal\080107\TS3000v101_be.mdb]'.

HELP!

Thanks again,

C

You don't need the single quotes around the table names.

AppendDB = "INSERT INTO TblInitiator IN
[I:\Archive\TestFinal\080107\TS3000v101_be.mdb] SELECT tblAddInitiator.*
FROM tblAddInitiator;"

Hello all,

[quoted text clipped - 27 lines]

Clarence

 

[claencemo]

Douglas...never mind last post...I got it to work...you da man

Thanks,

C

Douglas,

Thanks for the info. I tried that: (all on one line)

AppendDB = "INSERT INTO TblInitiator IN [I:\Archive\TestFinal\080107\
TS3000v101_be.mdb] SELECT tblAddInitiator.* FROM tblAddInitiator;"

I get this dialog box message:
Invalid bracketing of name '[I:\Archive\TestFinal\080107\TS3000v101_be.mdb]'.

HELP!

Thanks again,

C

You don't need the single quotes around the table names.

[quoted text clipped - 7 lines]