Insert records from one recordset into another

[CanuckBuck]

First let me say thank you to those who provided support to resolve my first post

I've hit another bit of a road-block;

I need to take input (Record identifiers) from a user (up to 1000!) and look up records in a database and then return those records to my database.

My application is a MS Access Data Project (.adp) so I'm using ADO. My database is SQL Server 2008R2 as is the database from which I need to retrieve records.



My strategy so far is;

1. Open a dialog box enabling the user to paste in record identifiers
2. parse the textbox contents into an array
3. Create a temp table in the database I need to look up the records in
4. Insert the record ids into the temp table
5. run a query which joins the table containing the records I want to look up with the temp table

This leaves me with a recordset containing the records I want to insert into a table in my database. Here's what the code looks like;

Option Compare Database
Option Explicit
Public con As ADODB.Connection
Public cat As ADOX.Catalog
Public tbl As ADOX.Table
Public rsT1 As ADODB.Recordset
Public rsLocations As ADODB.Recordset

Public Sub GetLocations(Frm As Access.Form)
Dim strLocationQuery As String
Dim strServerName As String
Dim strDBName As String
Dim i As Integer
Dim aDisp_ID As Variant
'parse the textbox into a list
aDisp_ID = Split(Frm.txtDispositionList.Value, vbCrLf)
'define a temporary table to hold disposition IDs
Set tbl = New Table
tbl.Name = "#T1"
With tbl.Columns
    .Append "Disposition_Id", adVarChar, 10
End With
'make a connection to the database and open it
strServerName = "UATDLDBV1\DL1"
strDBName = "GLIMPS"
Set con = New Connection
con.ConnectionString = "Provider=SQLOLEDB;Server=" & strServerName & ";Database=" & strDBName & ";integrated security=SSPI"
con.Open
'create the temporary table in the database
Set cat = New Catalog
Set cat.ActiveConnection = con
cat.Tables.Append tbl
'put the list of Disposition IDs into the temporary table
Set rsT1 = New ADODB.Recordset
With rsT1
    .Source = "SELECT * FROM #T1"
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .ActiveConnection = con
    .Open
    
    'loop through array and load into recordset
    For i = 0 To UBound(aDisp_ID)
        .AddNew
        .Fields("Disposition_Id") = Left(aDisp_ID(i), 3) & Space(10 - Len(aDisp_ID(i))) & Mid(aDisp_ID(i), 4, Len(aDisp_ID(i)) - 3)
        .Update
    Next i
    ' verify content of recordset in immediate window
'    .MoveFirst
'    Do Until rsT1.EOF
'        Debug.Print .Fields("Disposition_ID")
'        .MoveNext
'    Loop
End With

strLocationQuery = "SELECT DISTINCT " & _
        "T.Disposition_Id " & _
        ",REPLICATE('0',2-LEN(L.ATSL_SECTION)) + CAST(L.ATSL_SECTION AS VARCHAR(2)) " & _
        "+ '-' " & _
        "+ REPLICATE('0',3-LEN(L.ATSL_TOWNSHIP)) + CAST(L.ATSL_TOWNSHIP AS VARCHAR(3)) " & _
        "+ '-' " & _
        "+ REPLICATE('0',2-LEN(L.ATSL_RANGE)) + CAST(L.ATSL_RANGE AS VARCHAR(2)) " & _
        "+ 'W' " & _
        "+ CAST(L.ATSL_MERDIAN AS VARCHAR(1)) AS [LOCATION] " & _
    "FROM [GLIMPS].[PLT_ATAC_ATSL_ACTI] A  " & _
    "JOIN [UATDLDBV1\DL1].[GLIMPS].[GLIMPS].[PLT_ATSL_ATSLAND] L " & _
        "ON L.ATSL_ID = A.ATSL_ID " & _
    "RIGHT JOIN #T1 T " & _
    "ON T.Disposition_Id = A.ACTI_NBR"
Set rsLocations = New ADODB.Recordset
With rsLocations
    .Source = strLocationQuery
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .ActiveConnection = con
    .Open
    
    ' verify content of recordset in immediate window
'    .MoveFirst
'    Do Until rsLocations.EOF
'        Debug.Print .Fields("Disposition_ID")
'        Debug.Print .Fields("LOCATION")
'        .MoveNext
'    Loop
End With

End Sub

The records in the recordset called rsLocations must be inserted into a subform on the form which calls the dialog box which enables the user to paste in record IDs. Here's the code I have so far;

Private Sub btnPasteDispositionIDs_Click()
Dim rs As ADODB.Recordset
Dim i As Integer
    DoCmd.OpenForm "Subform - Assignment Disposition IDs", , , , , acDialog
        
    Set rs = Me.Locations.Form.Recordset 'Recordset of the subform
    rsLocations.MoveFirst 'Recordset containing records I've looked up from the other database
    While Not rsLocations.EOF 'go to the first record
    'Debug.Print rsLocations.Fields("Location")
        With rs ' Insert a new record into the locations table
            .AddNew
            .Fields("Milestone_Dates_DB_Key") = txtDBKey 'the primary key of the parent record
            .Fields("Location_Type_DB_Key") = DLookup("DB_Key", "R_Location_Type", "Name = 'LandId'") 'the db_key of the location "type"
            .Fields("General_Location") = rsLocations.Fields("Location") 'This is where the problem occurs.
            .Update
        End With
        rsLocations.MoveNext
    Wend
    
End Sub

I get an error on the line .Fields("General_Location") = rsLocations.Fields("Location"). The error is "Multiple-step operation generated errors. Check each status value." If I replace rsLocations.Fields("Location") with a simple text string e.g. "test" things work fine. I still get the error if I put rsLocations.Fields("Location") into a string variable and then use the variable.

Can someone see what I'm doing wrong? If I'm taking the wrong approach I'm open to other solutions which achieve the same end.

Thank you in advance for any assistance you can provide.

[June7]

Perhaps scenario 1 in the attached link is cause http://www.adopenstatic.com/faq/80040e21.asp

[CanuckBuck]

Thanks for the assistance!

It turned out to be an ID10T error

I was passing in a formatted string

        ",REPLICATE('0',2-LEN(L.ATSL_SECTION)) + CAST(L.ATSL_SECTION AS VARCHAR(2)) " & _
        "+ '-' " & _
        "+ REPLICATE('0',3-LEN(L.ATSL_TOWNSHIP)) + CAST(L.ATSL_TOWNSHIP AS VARCHAR(3)) " & _
        "+ '-' " & _
        "+ REPLICATE('0',2-LEN(L.ATSL_RANGE)) + CAST(L.ATSL_RANGE AS VARCHAR(2)) " & _
        "+ 'W' " & _
        "+ CAST(L.ATSL_MERDIAN AS VARCHAR(1)) AS [LOCATION]

Which looks like this "09-085-09W5"

when I should have been passing an unformatted string

        ",REPLICATE('0',2-LEN(L.ATSL_SECTION)) + CAST(L.ATSL_SECTION AS VARCHAR(2)) " & _
        "+ REPLICATE('0',3-LEN(L.ATSL_TOWNSHIP)) + CAST(L.ATSL_TOWNSHIP AS VARCHAR(3)) " & _
        "+ REPLICATE('0',2-LEN(L.ATSL_RANGE)) + CAST(L.ATSL_RANGE AS VARCHAR(2)) " & _
        "+ CAST(L.ATSL_MERDIAN AS VARCHAR(1)) AS [LOCATION]

Which looks like this "09085095"

Still not sure if this is the best approach overall but it works.