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.ol edb.4.0;da ta 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\Empl oyees2017. mdb')
Could anyone suggest me a solution!
I was using ADODB
On Error GoTo er1
Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection
dbs.Open "Provider=microsoft.jet.ol
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\Empl
Could anyone suggest me a solution!
I would probably create a linked table in one of your Access databases and do the comparison and insertion from there.
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?
ASKER
@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')
/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.
ASKER
@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.
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.
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
/gustav
I guess the source table is in the other DB, not the destination, Gustav.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Gustav Brock
Thanks a lot. It perfectly worked! Thanks all for your above suggestions.
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'
Answer provided.
INSERT INTO empTable(JobID, Date, EmpOut, EmpIn)
SELECT 515 AS JobID, Date,EmpOut, EmpIn FROM empTable