We help IT Professionals succeed at work.

ms access 2010 execute stored procedures on mysql not working

Medium Priority
160 Views
Last Modified: 2017-10-13
I have a win10 system that updates a mysql db with mysql stored procedures, executed from vba in an access app on this system. This has worked fine for several years. with this update we also updated the mysql ODBC drivers from mysql 5.1 to mysql 5.3 (64bit windows, 32bit access). The connection to the mysql tables are fine, but the SP execution doesn't work and there are no error msgs.
here is the code for one SP

Dim cnnSQL As New ADODB.Connection
Dim cmdSQL As ADODB.command
 
Set cnnSQL = New ADODB.Connection
  'cnnSQL.Open "DRIVER=MySQL ODBC 5.1 Driver; SERVER=192.168.1.162; DATABASE=Wonder; UID=adminstation02;PASSWORD=****; OPTION=3"
  cnnSQL.Open "DRIVER=MySQL ODBC 5.3 ANSI Driver; SERVER=192.168.1.162; DATABASE=Wonder; UID=adminstation02;PASSWORD=****; OPTION=3"

    Set cmdSQL = New ADODB.command
    With cmdSQL
        .ActiveConnection = cnnSQL
        .CommandType = adCmdStoredProc
        .CommandText = "emptyc3"  ' name of mysql stored procedure
       ' .Parameters.Append .CreateParameter("ReturnValue", adInteger, adParamReturnValue)
      '  .Parameters.Append .CreateParameter("@ContactID", adInteger, adParamInput, , ContactID)
        .Execute
     '   If .Parameters(0).Value = -1 Then
     '       MsgBox "Cannot delete record"
     '   Else
     '       MsgBox "Record deleted."
     '   End If
    End With
   
    Set cmdSQL = Nothing
    cnnSQL.Close
    Set cnnSQL = Nothing
Any help greatly appreciated
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Try executing the sp directly on the mySqlServer database and see if it gets executed properly.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
if there is no error, the it means it is working fine...

to test, create a temp table (lastexec date) in mysql and inside your sp put "insert into temp values(now())"
then check your table after calling sp to see if there is a record in that table...
if there is it proves it is called but your logic inside sp is not right or you dont pass parameter or parameters are not correct or there is logical problem in your sp...

can you post the sp code
NerdsOfTechTechnology Scientist
CERTIFIED EXPERT
Commented:
Reinstall the newest version of MySQL Connector/ODBC 5.3.9

Are you using the same x86/x64 type driver for 5.1?

Try running code Option Explicit as top line to see if any warnings/errors occur.

https://dev.mysql.com/downloads/connector/odbc/5.3.html

Please also check the SP as well and post it here.

Author

Commented:
Thanks all. Solution is using 32bit unicode driver.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.