I have a database using Access 2003 for the front end and MySQL to store and process all the online queries for the website. Several of the queries I run on the database are done daily as part of creating the data tables to support the site. Some of these queries were taking so long to run on the remote MySQL tables, that I created stored procedures in MySQL. The difference in speed is enormous, but it is also a pain to login to the mySQL database to call up the procedures each day. I know I can create a function to perform the tasks, but that to will be processing the procedure locally and will take too long or error out.
Is there a way to "launch" the stored procedures as part of the data processing in my Access front end?
Below is the code I tried to execute the stored procedure but when I try to run the code I get this error:
"Run-time error '3709':
The requested operation requires an OLE DB session Object, witch is not supported by the current supplier"
The procedure only updates tables in the MySQL tables and does not return a value.
Private Sub Form_Load()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
strconnect = "Driver={MySQL ODBC 5.1 Driver};Server=server;Port=3306;Database=db;Option =3;"
cnn.Open strconnect, "user", "pswd"
End Sub
Private Sub UpdateInjuryButton_Click()
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim cnn As New ADODB.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_INJURY_UPDATE"
Set rs = cmd.Execute
Set cmd.ActiveConnection = Nothing
End Sub