Results 1 to 2 of 2
  1. #1
    MrGrinch12 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    3

    stored procedures failure

    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

  2. #2
    MrGrinch12 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    3

    solved

    I figured it out by looking on the web:
    Code:
        Dim cnn As New ADODB.Connection
        Set cnn = CurrentProject.Connection
    
        cnn.Open "DRIVER={MySQL ODBC 5.1 Driver};SERVER=server;Database=db;uid=uid;PWD=pwd"
    
        cnn.CursorLocation = adUseClient
    
        If cnn.State = ADODB.adStateOpen Then
    
        cnn.Execute "CALL SP_INJURY_UPDATE( )"
    
        End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with date stored as string
    By weisslakeguy in forum Queries
    Replies: 8
    Last Post: 05-26-2010, 11:14 AM
  2. Event procedures
    By GIS_Guy in forum Forms
    Replies: 1
    Last Post: 05-11-2010, 02:34 PM
  3. Help with functions / procedures
    By curnil in forum Programming
    Replies: 3
    Last Post: 03-09-2010, 05:41 PM
  4. Duplicate record failure
    By bugchaser in forum Access
    Replies: 5
    Last Post: 05-21-2009, 08:38 AM
  5. Replies: 0
    Last Post: 01-08-2009, 05:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums