need help writing this code

[icemonster]

so my goal here is to capture the last_insert_id then set it as a variable. because my tables are set up that the PK for table 1 and table 2 become the FKs for table 3 so that there is a reference for those records.

so the idea is,
insert record to table 1, capture last_insert_id
insert record to table 2, capture last_inser_id
insert record to table 3 using tbl1_lastid and tbl2_lastid

i got myself started with someone's help but i have not done this for awhile and have become really rusty. thank you for any help you guys can offer.

Dim cnx As ADODB.connection
      Dim strSQL As String
      
      Set cnx = New ADODB.Connection
      With cnx
          .Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "Server=myServerName;" & _
                "Port=3306;" & _
                "Option=16384;" & _
                "Stmt=;" & _
                "Database=mydatabaseName;" & _
                "Uid=myUsername;" & _
                "Pwd=myPassword"  
          strSQL = "INSERT INTO qry_employee " & _
                   "(EMP_LASTNAME, EMP_FIRSTNAME) VALUES ('" & Me.txtLastName & "', '" & Me.txtFirstName & "');"
          .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
                  strSQL = "INSERT INTO qry_employee_education (" & _
                           "EMPED_ID_EMPLOYEE, EMPED_NAME_OF_SCHOOL, EMPED_SCHOOL_ZIPCODE" & _
                           ") VALUES (" & _
                           .Fields(0) & ", '" & Me.txtNameofSchool & "', '" & Me.txtSchoolZipCode & "');"
              Else
                  strSQL = vbNullstring
                  MsgBox "Previous Insert Attempt Failed"
              End If
              .Close
          End With
          ' If the previous insert succeeded we should have found the new ID
          ' and have a new SQL command to execute - if it failed then strSQL will be zero-length
          If Len(strSQL) Then .Execute strSQL, , adCmdText + adExecuteNoRecords
          .Close
      End With
      Set cnx = Nothing

 

[icemonster]

I have attached an image better explaining what i want to accomplish.

 

[SQL_Hell]

Do these tables have identity primary keys?

If so you can use

select scope_identity() to get the latest key inserted for your scope

 

[icemonster]

yeah they do, how do i integrate the scope? this is with a mysql background.

 

[mdlueck]

Does MySQL support...

BEGIN TRAN
INSERT INTO [dbo].[projects] (authid,logtimestamp,title)
OUTPUT INSERTED.id
VALUES (@authid,CURRENT_TIMESTAMP,@title);
COMMIT TRAN

SELECT @id AS [id]

SET NOCOUNT OFF
END

And that ID is received back into VBA via...

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProjectsTbl_Insert"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@authid").Value = Me.authid
    .Parameters("@title").Value = Me.title
    Set adoRS = .Execute()
  End With

  'Retrieve the ID the new record is stored at
  Me.id = Nz(adoRS!id, 0)

So then code continues to download into the FE temp table the newly created record based on its ID.