ODBC 3146 runtime error

[Pilly170]

Hi,

I am using MS access as a front end, and have a query thats meant to delete the MySql table contents. Sometimes it works sometimes it doesnt. when it doesnt it gives me ODBC 3146 runtime error. Any ideas on what could be wrong?? Is it a setting within the ODBC client setup or some sort of timeout??


Heres the query:

Function Delete_Table_Mysql_Lar041()
Dim DelLar041 As String

DelLar041 = "DELETE mysql_lar041.*, * FROM Mysql_lar041;"

DoCmd.SetWarnings warningsoff
DoCmd.RunSQL DelLar041
DoCmd.SetWarnings warningson

End Function

 

[Bastien]

Why not use "truncate table"? It empties the table.

Bastien

Cat, the other other white meat

 

[Pilly170]

I can do this within Access??

 

[cLFlaVA]

You could also try this:

[tt]DelLar041 = "DELETE FROM Mysql_lar041;"[/tt]

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[Pilly170]

I believe its something server / client based settings, as it only happens if i run the same query again.

Works first time, then if i populate the DB again then run the query again within 5mins it gives the error.

 

[ingresman]

Can you turn on odbc tracing ?
run up the odbc admin program (odbcad32) and choosing tracing and switch it on, note the log file file.
Do your program which should fail,
turn off tracing
have a look at the log file.
Sometimes you can see what is going on as it calls mysql it may show you the real error number and not the simple call failed response,, it will be a big file as it logs just about everything.

 

[Pilly170]

I did that and now have a 39mb file.

Im not sure what im looking for.

http://www.philpearce.com/downloads/SQL.LOG

If anyone has the time to scan through...

 

[Pilly170]

i did the log file again and its down to 7mb

http://www.philpearce.com/downloads/SQL2.LOG

Still cant see any obvious errors.

 

[Pilly170]

I believe i have resolved this. still in testing.

I changed the table type to INNO DB, tried again, this time i got a better description of the error within access.

Basically the unique index contains duplicate entries.
So what i did was to recreate the tables on the MySql with a field which was auto increment and set to primary.

So far i havent got the message back.

 

[ingresman]

good stuff, yes the trace can be big !. It sohws what the odbc driver is actually giving to mysql and the real error that mysql returns. Sometimes the driver bails out and just gives you a catch all error.