Importing from Excel - How to handle relationship insert

[BlahBlahBlah]

Hi.

I have implemented functionality that lets the user import data from an Excel spreadsheet into the Access tables in my database. As an example, the user can import many Rules records from a spreadsheet after they've chosen which Overview the Rules records are associated with. The data is stored in a temporary table that has the same structure as the real table, sans the primary key ID field, to allow preview/editing before importing. Once the user has clicked Import, this code is run:

Private Sub importBtn_Click()
    Dim sqlQuery As String
    sqlQuery = "INSERT INTO " & getCorrectDestinationTableName & " (" & _
        getTableFieldNames(getCorrectDestinationTableName) & ")" & _
        " SELECT * FROM " & getCorrectPreviewTableName & ";"
        
    CurrentDb.Execute sqlQuery
End Sub

This works fine, but I need to insert a relationship pair (OverviewID, RulesID) into a relationship table for each Rules record the user imports. Each Overview can have 0 to many Rules associated with it.

So what would be the best way to handle this? I'd probably need to insert one record into the Relationship table after every Rule is inserted, I'm guessing. Usually I'd let a form handle this, but I don't think there's a way to automate it.

Oh, and both the Rules and Overview tables have an AutoNumber as their ID field if that helps, with the Relationship table containing only two Number fields.

Cheers.

Edit: I realised I could probably use a recordset instead of the Execute function, will try that out.

 

[BlahBlahBlah]

I think I've got it working using Recordset:

Private Sub importRules()
    Dim fieldNames As Collection
    Dim rulesRst As Recordset
    Dim prvwRulesRst As Recordset
    Dim relationshipRst As Recordset
    Set fieldNames = getTableFieldNames("rules")
    Set rulesRst = CurrentDb.OpenRecordset("rules")
    Set prvwRulesRst = CurrentDb.OpenRecordset("tempPreviewRules")
    
    ' Want to get the last (AutoNumber) ID after inserting each new record.
    rulesRst.MoveLast
    
    If prvwRulesRst.RecordCount > 0 Then
        prvwRulesRst.MoveFirst
        Do While Not prvwRulesRst.EOF And Not prvwRulesRst.BOF
            rulesRst.AddNew
            ' Set each field to be added to the rules table.
            For Each field In fieldNames
                rulesRst(field) = prvwRulesRst(field)
            Next
            ' Insert one record into the rules table.
            rulesRst.Update
            ' Move to the next record in the preview table.
            prvwRulesRst.MoveNext
            
            ' Add an overview/rules ID pair to the relationship table.
            relationshipRst.AddNew
            relationshipRst("ID") = importRulesOverviewCombo.value
            relationshipRst("rulesID") = [B]rulesRst("ID")[/B]
            relationshipRst.Update
        Loop
    End If
    
    rulesRst.Close
    prvwRulesRst.Close
    relationshipRst.Close
End Sub

Will the bolded line get the AutoNumber ID of the latest inserted record?

 

[BlahBlahBlah]

For anyone who comes across this thread seeking a resolution, this works:

' Add an overview/rules ID pair to the relationship table.
relationshipRst.AddNew
relationshipRst("ID") = importRulesOverviewCombo
[B]rulesRst.Move 0, rulesRst.LastModified
relationshipRst("rulesID") = CLng(rulesRst!ID)[/B]
relationshipRst.Update