[AccessD] INSERT Syntax Error

When appending, it is 

INSERT INTO TableName(Field, OtherField) In 'X:\SomePath\SomeFile.ext' 
SELECT Atable.Field, Atable.OtherField FROM Atable

But for Make Tables it is

SELECT Atable.Field, Atable.OtherField INTO SomeTable IN 'X:\SomePath\SomeFile.ext' FROM Atable

But if all fields are being appended you don't need to specify the field names in parentheses

So Rocky's solution would in fact be

db1.Execute "INSERT INTO tblProductStructure in '" & Me.txtTargetDatabase & "' SELECT tblBOMReport.* from tblBOMReport"

You can find all this out by imply building the query in design mode and then selecting 'Append' or 'Make-Table' fro the menus and choosing the 'Another Database' radio button in the dialog box. Then just look at the SQL Access has built for you.


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, November 10, 2011 4:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] INSERT Syntax Error

Does the tblProductStructure already exist?  If so, you would have to append records rather than inserting the table.

Charlotte Foust

On Thu, Nov 10, 2011 at 12:06 PM, Rocky Smolin <rockysmolin at bchacc.com>wrote:

> Dear List:
>
> Trying to append all records in a table to a table in a second 
> database, the following gives me 'syntax error in INSERT INTO' 
> statement.
>
> db1.Execute "INSERT INTO tblProductStructure in " & 
> Me.txtTargetDatabase & "
> SELECT tblBOMReport* from tblBOMReport"
>
> where me.txtTargetDatabase has the full path and file name of the 
> target and tblBOMReport has the same structure as tblProductStructure. 
> and db1 is set to CurrentDb.
>
> What am I doing wrong?
>
> MTIA
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.bchacc.com
>
> <http://www.bchacc.com/
>
> >
> www.e-z-mrp.com
>
> <http://www.e-z-mrp.com/
>
> >
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com