Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012

    Unanswered: help with this mysql syntax

    Private Sub cmdsave_Click()
      Set cnx = New ADODB.Connection
        With cnx
              .Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
                    "Server=localhost;" & _
                    "Port=3306;" & _
                    "Option=;" & _
                    "Stmt=;" & _
                    "Database=kbase_educ;" & _
                    "Uid=leafy;" & _
                              strSQL = "INSERT INTO tbl_school " & _
                                       "(school_name, school_degree, school_major, school_startdate, school_enddate) SELECT (school_name, school_degree, " _
                                     & "school_major, school_startdate, school_enddate) FROM tmptbl_school;"
                              .Execute strSQL, , adCmdText + adExecuteNoRecords
                              strSQL = "SELECT Last_Insert_ID();"
                                With .Execute(strSQL, , adCmdText)
                                    If Not (.BOF And .EOF) Then
                                        ' Found the new ID - build the second Insert SQL string
                                        lngLastSchoolID = .Fields(0)
                                        ' Abort
                                    End If
                                End With
        End With
      Set cnx = Nothing
    End Sub
    so i need help with this one, the problem is that im trying to append a table from my ms access front end to my mysql back end. basically inserting the records on the ms access front end to the mysql back end while at the same time retrieving the last primary keys to so that i can use those values to insert on another table. my first error is that im getting table not found which makes sense since it doesnt not exist. any idea on how i can remedy this?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you are getting 'table not found'
    if the table doesn't exist then create it
    if the table exists then check the spelling for typo's
    if the problem is in the MySQL tables and the spelling is correct, AND you are connecting to MySQL on a *NIX OS then make certain the capitalistion is the same. its not a problem on Windows OS's

    i wouldn't create a connection each and every time you want to save a record. I'd be temtped to create a connection once when the appliction starts and use that connection for all db interactions from then on
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts