appending a table in my front end to my mysql back end

[icemonster]

hello.

i am having trouble with this code, the reason being is that i am referencing the table in the front end wrong. can anyone help me correct it?

Private Sub cmdsave1_Click()

  Set cnx = New ADODB.Connection
    With cnx
          .Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
                "Server=localhost;" & _
                "Port=3306;" & _
                "Option=;" & _
                "Stmt=;" & _
                "Database=database;" & _
                "Uid=tree;" & _
                "Pwd=bark"
              
            If IsNull(Me.txtschoolid) Then
            
                          strSQL = "INSERT INTO tbl_school ( school_name, school_degree, school_major, school_startdate, school_enddate ) " _
                                 & "SELECT tmptbl_school.school_name, tmptbl_school.school_degree, tmptbl_school.school_major, tmptbl_school.school_startdate, tmptbl_school.school_enddate FROM tmptbl_school " (the thing here is, tmptbl_school is in the front end, so how do i reference it using this connection type?)
                          .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)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With
                         Me.txtschoolid = lngLastSchoolID
            End If
                            
                                
    .Close
    End With
  Set cnx = Nothing
                                
End Sub

 

[RainLover]

Are you going to answer the question raised in your thread.

http://www.access-programmers.co.uk/forums/showthread.php?p=1159795#post1159795

Or have you solved it without letting anyone know and have moved on.

It would be good to know that if work done on this question was not a waste of time.