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

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

Do these tables have identity primary keys?

If so you can use

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

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

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
ENDAnd 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.