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=MySQL ODBC 3.51 Driver DSN;DATABASE=mydatabase;SERVER=XX.xx.xx.X;UID=xxxxxx;PASSWORD=xxxxx"
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

TheDataFerretAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupNerdCommented:
Does  categories need to be in quotes?

db.Execute "Drop table 'categories'"


(wild guess... I'm not familar with the Drop Table statement)
mbizupNerdCommented:
(disregard my last post)
dqmqCommented:
I think you're using "IN" backwards:  

INSERT INTO ... IN .... SELECT ... FROM ...    <==correct

INSERT INTO ... SELECT .... FROM ... IN ...    <==incorrect


CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

dqmqCommented:
There's a fair chance you drop table is not working because of permissions on the MYSQL database.

As for the table copy, try this:

From the second database, use the File, Get External Data menu to Link to THECategories table in C:\ZDB\Prod_2005.mdb   (you can do it in VBA, too, but manually is simpler. FWIW, you can manually link to the ODBC data source, as well, and do this whole thing with a delete table query followed by an append table query. ie. no VBA code required).

Then:
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=MySQL ODBC 3.51 Driver DSN;DATABASE=mydatabase;SERVER=XX.xx.xx.X;UID=xxxxxx;PASSWORD=xxxxx"
Set db = ws.OpenDatabase("", False, False, strConnection)

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;"

end function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TheDataFerretAuthor Commented:
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].THEproducts WHERE categories_id IS NOT NULL;"

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
Alan WarrenApplications DeveloperCommented:
Hi DataFerret

there is nothing wrong with the sql, works fine here.

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 'Prod_2005.mdb'


This works fine access to access db: ?Testit()

Function testit() As Boolean

  Dim strSql As String
 
  strSql = "Delete * From categories"
  CurrentProject.Connection.Execute strSql, , adExecuteNoRecords
 
 
  strSql = "INSERT INTO categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status )"
  strSql = strSql & " SELECT categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status FROM THEcategories IN 'Prod_2005.mdb'"
 
  CurrentProject.Connection.Execute strSql, , adExecuteNoRecords
 
  testit = True

End Function

tell me a bit about the image field... categories_image
Access by default stores images as OLE objects, I'm not sure what datatype mssql uses for image data.

Alan


TheDataFerretAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.