Link to home
Start Free TrialLog in
Avatar of Mahesh GS
Mahesh GS

asked on

INSERT INTO is resulting Syntax Error!

I have two access database with same table and same columns. I wanted to copy records from one database to another one. I was trying the below code in VB6 but it provides "Syntax error in INSERT INTO statement"

I was using ADODB

On Error GoTo er1
Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection
dbs.Open "Provider=microsoft.jet.oledb.4.0;data source=" & dbServer

dbs.Execute My_query


My_query is  
INSERT INTO empTable(JobID, Date, EmpOut, EmpIn)  SELECT 515 AS JobID, Date,EmpOut, EmpIn FROM empTable IN ('D:\Application\data\Employees2017.mdb')

Could anyone suggest me a solution!
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Try this -

INSERT INTO empTable(JobID, Date, EmpOut, EmpIn)  
SELECT 515 AS JobID, Date,EmpOut, EmpIn FROM empTable
Avatar of Ryan Chong
I would probably create a linked table in one of your Access databases and do the comparison and insertion from there.
Avatar of Mahesh GS
Mahesh GS

ASKER

Thanks for the answer, But I have two databases. First database is already open by the dbs object. This is also where I wanted to copy the records and I need to specify the source database to get the data!, right?
@Ryan Chong, Can you please describe in detail?
and I need to specify the source database to get the data!, right?
yes, exactly, and the most likely you only need do once for the configuration. and by the way, you can also have the option to import instead of linking.

see if this article is useful explaining the idea?

Import or link to data in another Access database
https://support.office.com/en-us/article/Import-or-link-to-data-in-another-Access-database-095ab408-89c7-45b3-aac2-58036e45fcf6

see how you want the approach to be, you may also consider to automate the process by writing some custom codes. just an idea if you really want to sync the data.
You probably need brackets and a space:

INSERT INTO empTable (JobID, [Date], EmpOut, EmpIn)  
SELECT 515 AS JobID, [Date] ,EmpOut, EmpIn 
FROM empTable IN ('D:\Application\data\Employees2017.mdb')

Open in new window

/gustav
INSERT INTO does not seem to allow for external db references that way, see https://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx. The IN clause for ext. db files is only part of the standard SELECT.
@Qlemo
I think yoy are right. I have got an additional Syntax from the link you have provided above.

[SELECT | INSERT] INTO destination IN     {path | ["path" "type"] | ["" [type; DATABASE = path]]}

this seems the above syntax may work. But I need support  to derive my sql request from the above syntax.
>>this seems the above syntax may work. But I need support  to derive my sql request from the above syntax.

you probably can insert the data into a temp table and then do ur manipulation there before the final insertion into ur target table.
I need support  to derive my sql request from the above syntax.

It will be:

INSERT INTO empTable (JobID, [Date], EmpOut, EmpIn)  
IN 'D:\Application\data\Employees2017.mdb'
SELECT 515 AS JobID, [Date] ,EmpOut, EmpIn 
FROM empTable

Open in new window

/gustav
I guess the source table is in the other DB, not the destination, Gustav.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Gustav Brock
Thanks a lot. It perfectly worked! Thanks all for your above suggestions.

INSERT INTO empTable (JobID, [Date], EmpOut, EmpIn)  
SELECT 515 AS JobID, [Date] ,EmpOut, EmpIn 
FROM empTable 
IN 'D:\Application\data\Employees2017.mdb'

Open in new window

Answer provided.