HELP Syntax error in sql insert into

[Bob Alston]

I am going blind tonight but I cannot figure out the error. I get a
syntax error from this sql statement, being run via vba in access 2003

insert into tbl_Volunteer_D onor in "C:\AgencySyste mVer1.32.mdb" (
[Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
select
([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
from tbl_Volunteer_D onor in "C:\SapulpaComm unity Care NEW.mdb";

I am trying to copy the exact same fields (but not all the available
fields) from a table in one database to a same named table with a
slightly different definition, but having all these fields, in another
database.

Can someone set me straight on the syntax error?
Reserved word? True syntax error? Something else?

Bob

[Bob Alston]

Bob Alston wrote:

I am going blind tonight but I cannot figure out the error. I get a
syntax error from this sql statement, being run via vba in access 2003

insert into tbl_Volunteer_D onor in "C:\AgencySyste mVer1.32.mdb" (
[Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
select
([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
from tbl_Volunteer_D onor in "C:\SapulpaComm unity Care NEW.mdb";

I am trying to copy the exact same fields (but not all the available
fields) from a table in one database to a same named table with a
slightly different definition, but having all these fields, in another
database.

Can someone set me straight on the syntax error?
Reserved word? True syntax error? Something else?

Bob

ok SIMPLIFIED A BIT,

The following works fine:

INSERT INTO tbl_Volunteer_D onor IN 'C:\AgencySyste mVer1.32.mdb' SELECT
* FROM tbl_Volunteer_D onor IN 'C:\SapulpaComm unity Care NEW.mdb';
But if I replace "*" with "([fieldname])" it gives me the error:

circular reference caused by alias 'fieldname' in query definition

I need to be able to select specific fields not all fields.

Ideas?

[Bob Alston]

Bob Alston wrote:

Bob Alston wrote:

I am going blind tonight but I cannot figure out the error. I get a
syntax error from this sql statement, being run via vba in access 2003

insert into tbl_Volunteer_D onor in "C:\AgencySyste mVer1.32.mdb" (
[Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
select
([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInf o],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateSer vice],[Phone1Type])
from tbl_Volunteer_D onor in "C:\SapulpaComm unity Care NEW.mdb";

I am trying to copy the exact same fields (but not all the available
fields) from a table in one database to a same named table with a
slightly different definition, but having all these fields, in another
database.

Can someone set me straight on the syntax error?
Reserved word? True syntax error? Something else?

Bob

ok SIMPLIFIED A BIT,

The following works fine:

INSERT INTO tbl_Volunteer_D onor IN 'C:\AgencySyste mVer1.32.mdb' SELECT
* FROM tbl_Volunteer_D onor IN 'C:\SapulpaComm unity Care NEW.mdb';
But if I replace "*" with "([fieldname])" it gives me the error:

circular reference caused by alias 'fieldname' in query definition

I need to be able to select specific fields not all fields.

Ideas?

Ok think I have it. I have to qualify the field name with a table name
so this works

INSERT INTO tbl_Volunteer_D onor IN 'C:\AgencySyste mVer1.32.mdb' SELECT
xxx.company FROM tbl_Volunteer_D onor AS xxx IN 'C:\SapulpaComm unity
Care NEW.mdb';