Preserving Relationships on imported data
===

I'll start with the background story before explaining the problem with my code. I'm using MS Access 2010. I've been able to import a table of data with two columns. Then I was able to curate the data by adding fields with appropriate values to the imported table. Now, I need to take the curated table and integrate it into my data base. However, I cannot use any of Microsofts built in queries as none of these appear to be able to do what I need. The integration breaks the table apart, yes, but it needs to **preserve the relationships** of the data in each record. 

To this end I've been writing some code in VBA:

    Function IntegrateNIRData(curatedTable, queryRecords)
        
        On Error GoTo Error_Handler
        Dim db As DAO.Database
        Dim rsCuratedTable, rsDBRecords As DAO.Recordset
        Dim iCount As Integer
    
        Set db = CurrentDb()
        Set rsCuratedTable = db.OpenRecordset(curatedTable, dbOpenTable) 'open the recordset for use (table, Query, SQL Statement)
        Set rsDBRecords = db.OpenRecordset("NIR_Samples_verify", dbOpenDynaset, dbExecDirect, dbOptimisticValue)
        
        With rsCuratedTable
            If Not (.BOF And .EOF) Then
                Do While Not .EOF
                    ' Rest of your code here.
                    rsDBRecords.AddNew
                        'Assign Fields here.
                        rsDBRecords![Product Name] = rsCuratedTable![productName]
                        rsDBRecords![Lot Number] = rsCuratedTable![lotNumber]
                        rsDBRecords!counts = rsCuratedTable![counts]
                        rsDBRecords![subsets] = rsCuratedTable![subsets]
                        rsDBRecords![Date Taken] = rsCuratedTable![dateTaken]
                    rsDBRecords.Update
                    rsDBRecords.Bookmark = rsDBRecords.LastModified
                    .MoveNext
                Loop
            End If
        End With
     
        rsCuratedTable.Close 'Close the recordset
        rsDBRecords.Close 'Close the recordset
    
    Error_Handler_Exit:
        On Error Resume Next
        'Cleanup after ourselves
        Set rs = Nothing
        Set db = Nothing
        Exit Function
     
    Error_Handler:
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
        Err.Number & vbCrLf & "Error Source: IntegrateNIRData" & vbCrLf & "Error Description: " & _
        Err.Description, vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    
    End Function

The Function hangs on this line, the second OpenRecordset:

    Set rsDBRecords = db.OpenRecordset("NIR_Samples_verify", dbOpenDynaset, dbExecDirect, dbOptimisticValue)

To my understanding this may have something to do with Workspaces and the Jet engine not accepting a ms query that spans multiple tables. Of course, I could also be way off. Any advice at this point would be greatly appriciated.


