HELP Syntax error in sql insert into

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_Donor in "C:\AgencySystemVer1.32.mdb" (
[Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
select
([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
Name],[First
Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
from tbl_Volunteer_Donor in "C:\SapulpaCommunity 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

re: HELP Syntax error in sql insert into

Bob Alston wrote:[color=blue]
> 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_Donor in "C:\AgencySystemVer1.32.mdb" (
> [Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
> Name],[First
> Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
> select
> ([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
> Name],[First
> Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
> from tbl_Volunteer_Donor in "C:\SapulpaCommunity 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[/color]
ok SIMPLIFIED A BIT,

The following works fine:

INSERT INTO tbl_Volunteer_Donor IN 'C:\AgencySystemVer1.32.mdb' SELECT
* FROM tbl_Volunteer_Donor IN 'C:\SapulpaCommunity 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?

re: HELP Syntax error in sql insert into

Bob Alston wrote:[color=blue]
> Bob Alston wrote:
>[color=green]
>> 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_Donor in "C:\AgencySystemVer1.32.mdb" (
>> [Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
>> Name],[First
>> Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
>> select
>> ([Company],[ID],[Updated],[PositionTitle],[CompanyMatchInfo],[Last
>> Name],[First
>> Name],[Sex],[Address],[City/State/Zip],[DoNotMail],[email],[Phone1#],[Phone2#],[Phone2Type],[Volunteer],[Donor],[Comments],[OriginalDateService],[Phone1Type])
>> from tbl_Volunteer_Donor in "C:\SapulpaCommunity 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[/color]
>
> ok SIMPLIFIED A BIT,
>
> The following works fine:
>
> INSERT INTO tbl_Volunteer_Donor IN 'C:\AgencySystemVer1.32.mdb' SELECT
> * FROM tbl_Volunteer_Donor IN 'C:\SapulpaCommunity 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?[/color]
Ok think I have it. I have to qualify the field name with a table name
so this works

INSERT INTO tbl_Volunteer_Donor IN 'C:\AgencySystemVer1.32.mdb' SELECT
xxx.company FROM tbl_Volunteer_Donor AS xxx IN 'C:\SapulpaCommunity
Care NEW.mdb';