How to save a ado recordset into a new local table in access 2003?

Question

I'm trying to import tables from a FoxPro 9.0 database into access 2003. So far, from google searches and many trials, the only way for me to connect to the tables is through an OLE DB connection programatically. I have set up 3 ODBC connections with different configurations but none of them work. I get unspecified errors, that I can't find any information on. With OLE DB I can succesfully connect to the FoxPro database, and import tables in ado recordsets. The problem is I can't save the recordset into new table in the local database using SQL. The ado recordsets behave differently than tables, so I can't query them. The code below gives me a 'type mismatch' error at DoCmd.RunCommand ("select * from " & rst & " INTO newClients"). Thanks for you help...

Sub newAdoConn()

Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String Dim decision As Integer

Set cnn = New ADODB.Connection
   cnn.ConnectionString = "Provider=vfpoledb;" & _
       "Data Source=s:\jobinfo\data\jobinfo.dbc;" & _
       "Mode=ReadWrite|Share Deny None;" & _
       "Collating Sequence=MACHINE;" & _
       "Password=''"
strSQL = "Select * from Jobs"
cnn.Open

Set rst = cnn.Execute("Select * from clients")

If rst.EOF = False Then
    Do While Not rst.EOF
        decision = MsgBox(rst.Fields!ID & " " & rst.Fields!fname & " " & rst.Fields!lname & vbCrLf & vbCrLf & "Continue?", vbYesNo)
        If decision = vbYes Then
            rst.MoveNext
        Else
            Exit Do
        End If
    Loop
End If

DoCmd.RunCommand ("select * from " & rst & " INTO newClients")
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

End Sub

Answer 1

What you will have to do is open the FoxPro table as a recordset and open the local table as another recordset. You can then loop through the FoxPro recordset and do something like this

Do until FoxProRst.EOF
   LocatRst.AddNew
      LocalRst!SomeField1=FoxProRst!SomeField1
      LocalRst!SomeField2=FoxProRst!SomeField2
      LocalRst!SomeField3=FoxProRst!SomeField3
   LocalRst.Update
   FoxProRst.MoveNext
Loop

It might not be the quickest way but it will work

Answer 2

Why not use ODBC to link to the table? http://support.microsoft.com/kb/225861