Avatar of Shaun Wingrin
Shaun Wingrin
Flag for South Africa asked on

MySql server Win7 32, ODBC connector causing large tables to crash ODBC connector

Say, MySQL server, version: 5.5.29 is running on Win7 32 Bit OS.
Connecting from MSAccess using current 32 bit ODBC driver mysql-connector-odbc-5.2.2-win32
On small tables works fine, but on large ones,the ODBC  driver crashes. It is on an append query.
Pls help!
MySQL ServerMicrosoft Access

Avatar of undefined
Last Comment
Shaun Wingrin

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

does the table have a primary key field?
especially for append operations this should work fine, at least I could work just fine even on large tables (10 million rows) ...

now, what do you call "large" tables?
what type of tables? MyISAM, InnoDB, something else?
Shaun Wingrin

ASKER
The Source table doesn't have a primary key but the destination table does. Its the autonum.
If we append a table of say 3000 records it works fine. A table of say 70,000 records causes the ODBC to crash.
They are linked ODBC tables  using the above odbc driver and created in MS Access 2000
As for MyISAM, InnoDB
Please explain the difference. I just created the tables in MSACCESS, exported the to MySQL and linked them back, as it was easier to use the graphical interface in MSAccess
SOLUTION
Tomas Helgi Johannsson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shaun Wingrin

ASKER
Pls reply to my post too
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Guy Hengel [angelIII / a3]

>As for MyISAM, InnoDB
>Please explain the difference.
it's the table storage engines:
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
it may impact the performance ...
Shaun Wingrin

ASKER
tables stored as MYISAM
Set max_allowed_packet to 16M and same error...
Tomas Helgi Johannsson

Try increasing it to 32M or 64M.
Also you have to set max_allowed_packet into the my.cnf file and restart the database.
Can you post the error you are getting?

Regards,
   Tomas Helgi
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shaun Wingrin

ASKER
and 32M
max_allowed_packet = 32M
The size of the table being appended is a 2Meg Text file imported to a table  and the table to which it is appended is about 800Meg
Shaun Wingrin

ASKER
Unfortunately access just returns a general error with inserting table - no specific error no.
A clue to solution. When I was using the older version 3 ODBC driver and had the database stored in a Linux MySql server this problem did not exist.
Is there a way I can run the query from within MySql & call it from MSaccess? I will have to pass a single parameter to the query. I need a simple insert query.
SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shaun Wingrin

ASKER
I need an automated system for MSAccess to process the queries ...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Guy Hengel [angelIII / a3]

to automate SQL queries, you don't need ms access.
you can scheduled SQL scripts with MySQL easily also:
http://dev.mysql.com/doc/refman/5.1/en/events-overview.html

and if it's based on human input (button click), you could code it all inside a stored procedure, and just run that one ...
Shaun Wingrin

ASKER
But how would I call the MYSQL query from within Access VB
Tomas Helgi Johannsson

Hi!

If you are inserting into a MySQL table  from Access you will need to first create a Linked Table through the MySQL ODBC driver and then you can use the ADO Insert method in the VB.
Same goes for  any other database table.

Regards,
     Tomas Helgi
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

Shaun Wingrin

ASKER
I want to call a stored Query from Access
Guy Hengel [angelIII / a3]

what kind of stored query?
you can translate them into the mysql corresponding code, which will then run on the mysql server, running better...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Shaun Wingrin

ASKER
Want to sort out queries failing for large tables....
Btw, tried
max_allowed_packet. of 128M
Same error
Shaun Wingrin

ASKER
Exact error in Access: 3155 ODBC -- insert on linked table 'name' failed
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shaun Wingrin

ASKER
Yes, It only fails on big tables- see above please
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.