What is wrong with this SQL VBA Query?
Hi experts
I am attempting to update a mysql database with information from another access database. I have the original information in the first access database, the linked tables to the mysql db in a second access database. The ODBC connection works fine and I can run delete queries on the remote database. However, I cannot drop the tables on the remote database (no idea why) from Access VBA and my Insert Records code refuses to run. (Correct references DAO are in place - running Access 2003)
Here is the function I tried using the drop table method:
Function UpdateRemoteCategories()
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=s4donline;DESC=M
Set db = ws.OpenDatabase("", False, False, strConnection)
'Drop the Online Categories Table
db.Execute "Drop table categories"
' Populate the categories table from the offline database
db.Execute "SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status INTO categories FROM THEcategories IN 'C:\ZDB\Prod_2005.mdb'"
End Function
I also tried the Delete Records Method which fails with a syntax error in the INSERT statement. Is it the use of IN 'C:\ZDB\Prod_2005.mdb' causing this? If so can anyone suggest a better way to write this?
db.Execute "Delete * From categories"
db.Execute "INSERT INTO categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status ) SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status FROM THEcategories IN ((('C:\ZDB\Prod_2005.mdb')
Thanks Experts
I am attempting to update a mysql database with information from another access database. I have the original information in the first access database, the linked tables to the mysql db in a second access database. The ODBC connection works fine and I can run delete queries on the remote database. However, I cannot drop the tables on the remote database (no idea why) from Access VBA and my Insert Records code refuses to run. (Correct references DAO are in place - running Access 2003)
Here is the function I tried using the drop table method:
Function UpdateRemoteCategories()
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=s4donline;DESC=M
Set db = ws.OpenDatabase("", False, False, strConnection)
'Drop the Online Categories Table
db.Execute "Drop table categories"
' Populate the categories table from the offline database
db.Execute "SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status INTO categories FROM THEcategories IN 'C:\ZDB\Prod_2005.mdb'"
End Function
I also tried the Delete Records Method which fails with a syntax error in the INSERT statement. Is it the use of IN 'C:\ZDB\Prod_2005.mdb' causing this? If so can anyone suggest a better way to write this?
db.Execute "Delete * From categories"
db.Execute "INSERT INTO categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status ) SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status FROM THEcategories IN ((('C:\ZDB\Prod_2005.mdb')
Thanks Experts
(disregard my last post)
I think you're using "IN" backwards:
INSERT INTO ... IN .... SELECT ... FROM ... <==correct
INSERT INTO ... SELECT .... FROM ... IN ... <==incorrect
INSERT INTO ... IN .... SELECT ... FROM ... <==correct
INSERT INTO ... SELECT .... FROM ... IN ... <==incorrect
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi dgmg
Thanks for the replies - unfortunately manually linking the tables is not an option because I have to send these DB's to a third party who is a non-coder so they can run updates themselves. This is why most of the back-end is in VB.
Your suggestion above resulted in an error (could not find the THEcategories table) so I revisited the code and removed the IN statement completely and it works:
db.Execute "INSERT INTO categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status ) SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status FROM [C:\ZDB\Prod_2005.mdb].THE
I am updating a database with 20,000 records and the update is painfully slow. Is there a faster way to do this?
Options:
1) Create a second database cloning all the online tables, then use an application like navicat to update the database
2) Find some way of reading the local tables and create a mysql dump file complete with insert statements
I would be very interested in option 2 if anyone has any suggestions
Thx
Thanks for the replies - unfortunately manually linking the tables is not an option because I have to send these DB's to a third party who is a non-coder so they can run updates themselves. This is why most of the back-end is in VB.
Your suggestion above resulted in an error (could not find the THEcategories table) so I revisited the code and removed the IN statement completely and it works:
db.Execute "INSERT INTO categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status ) SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status FROM [C:\ZDB\Prod_2005.mdb].THE
I am updating a database with 20,000 records and the update is painfully slow. Is there a faster way to do this?
Options:
1) Create a second database cloning all the online tables, then use an application like navicat to update the database
2) Find some way of reading the local tables and create a mysql dump file complete with insert statements
I would be very interested in option 2 if anyone has any suggestions
Thx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Alan
Yes - there is no problem Access to Access DB - I can pretty much do anything on a local system. It is when connecting to the Access to Mysql DB when things fall down. Access seems to have a problem finding the table THEcategories when doing an ODBC update. However, the revised code I posted seems to have fixed this (although I have to say it is slow).
The categories_image field is just a text string denoting the image location on the online server (e.g. icons/category1.gif), so this is not being interpreted as an OLE object (fortunately).
Your code example looks interesting - Once the current update has finished I will try it out. But I thought db.execute was faster then strSql ????
Have you any thoughts on how to go about creating a mysql dump file from an access table? I can do this from Excel but not from Access (yet).
Thx Alan
Jim
Yes - there is no problem Access to Access DB - I can pretty much do anything on a local system. It is when connecting to the Access to Mysql DB when things fall down. Access seems to have a problem finding the table THEcategories when doing an ODBC update. However, the revised code I posted seems to have fixed this (although I have to say it is slow).
The categories_image field is just a text string denoting the image location on the online server (e.g. icons/category1.gif), so this is not being interpreted as an OLE object (fortunately).
Your code example looks interesting - Once the current update has finished I will try it out. But I thought db.execute was faster then strSql ????
Have you any thoughts on how to go about creating a mysql dump file from an access table? I can do this from Excel but not from Access (yet).
Thx Alan
Jim
db.Execute "Drop table 'categories'"
(wild guess... I'm not familar with the Drop Table statement)