Query SELECT and INSERT using two ODBC .mdb's 1

[ranshe]

NOTE: The code below works just fine!

Private Sub cmdCreate_Click()
    Dim DBo As New ADODB.Connection
    Dim DBi As New ADODB.Connection
    Dim rsRead As New ADODB.Recordset
    Dim rsWrite As New ADODB.Recordset
    Dim InsertFields As String
    Dim SelectCriteria As String
        
    DBi.Open "TestDB"
    DBo.Open "CampDump"
    
    SelectCriteria = "Svc_first_nm = 'Bea' AND Svc_cmpy_nm = 'Goodyear' OR Svc_cmpy_nm = 'Goodbank'"
    
    rsRead.Open "SELECT * FROM tblCustomerDump WHERE " & SelectCriteria & " ", DBo, adOpenDynamic, adLockOptimistic
    
    Do Until rsRead.EOF
        InsertFields = "'" & rsRead.Fields(0) & "', " & _
                       "'" & rsRead.Fields(1) & "', " & _
                       "'" & rsRead.Fields(2) & "', " & _
                       "'" & rsRead.Fields(3) & "', " & _
                       "'" & rsRead.Fields(4) & "', " & _
                       "'" & rsRead.Fields(5) & "', " & _
                       "'" & rsRead.Fields(6) & "', " & _
                       "'" & rsRead.Fields(7) & "', " & _
                       "'" & rsRead.Fields(8) & "', " & _
                       "'" & rsRead.Fields(9) & "', " & _
                       "'" & rsRead.Fields(10) & "', " & _
                       "'" & rsRead.Fields(11) & "'  "
        rsWrite.Open "INSERT INTO tblOurCustomerDump VALUES(" & InsertFields & ")", DBi, adOpenDynamic, adLockOptimistic
            
        rsRead.MoveNext
    Loop
   
    MsgBox ("Campaign Database has been created")
    
    'close database(s)
    DBo.Close
    DBi.Close

End Sub

I would like to change the statement:

        rsWrite.Open "INSERT INTO tblOurCustomerDump VALUES(" & InsertFields & ")", DBi, adOpenDynamic, adLockOptimistic

So that I would not have to use the:

Do Until rsRead.EOF
    InsertFields = "'" & rsRead.Fields(0) & "', " & _
                   "'" & rsRead.Fields(1) & "', " & _
                   "'" & rsRead.Fields(2) & "', " & _
                   "'" & rsRead.Fields(3) & "', " & _
                   "'" & rsRead.Fields(4) & "', " & _
                   "'" & rsRead.Fields(5) & "', " & _
                   "'" & rsRead.Fields(6) & "', " & _
                   "'" & rsRead.Fields(7) & "', " & _
                   "'" & rsRead.Fields(8) & "', " & _
                   "'" & rsRead.Fields(9) & "', " & _
                   "'" & rsRead.Fields(10) & "', " & _
                   "'" & rsRead.Fields(11) & "'  "

Once I have the row from the select statement

    rsRead.Open "SELECT * FROM tblCustomerDump WHERE " & SelectCriteria & " ", DBo, adOpenDynamic, adLockOptimistic

I would like to just write the whole row into the Insert table without having to load all the fields.

Is it posible to do this and if so how?

 

[johnwm]

Look in your JETSQL reference (jetsql40.chm) for the SELECT...INTO statement. It will do the whole job, as you can select a recordset into another database directly.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller