How to save an 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 that 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").

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

I finally worked out a decent solution. It involves saving the ado recordset from memory to an excel file using the copyFromRecordset function, and then linking the file programmatically to a table in excel using the TransferSpreadsheet()...

Sub saveToExcel()

Dim cnn As ADODB.Connection
    'declare variables
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim decision As Integer
    Dim colIndex As Integer
  '  Dim fso As New FileSystemObject
  '  Dim aFile As File

    'set up connection to foxpro database    
    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=''"
    cnn.Open

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

   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   oSheet.Name = "clients"

    ' Copy the column headers to the destination worksheet
    For colIndex = 0 To rs.Fields.Count - 1
        oSheet.Cells(1, colIndex + 1).Value = rs.Fields(colIndex).Name
    Next

   'Transfer the data to Excel
   oSheet.Range("A2").CopyFromRecordset rs

    ' Format the sheet bold and auto width of columns
    oSheet.Rows(1).Font.Bold = True
    oSheet.UsedRange.Columns.AutoFit


   'delete file if it exists - enable scripting runtime model for this to run
    'If (fso.FileExists("C:\Documents and Settings\user\Desktop\clients.xls")) Then
    '    aFile = fso.GetFile("C:\Documents and Settings\user\Desktop\clients.xls")
    '    aFile.Delete
    'End If

    'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Documents and Settings\user\Desktop\clients.xls"
   oExcel.Quit

   'Close the connection
   rs.Close
   cnn.Close
   MsgBox ("Exporting Clients Done")

   'link table to excel file
   DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "clientsTest", "C:\Documents and Settings\user\Desktop\clients.xls", True
End Sub

Answer 2

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 3

Let me just sketch another approach with SQL queries, that could simplify:

'...
'not required for first time test:
'cnn.Execute("DROP TABLE MyNewTable")
'...
'create the new table in the destination Access database
cnn.Execute("CREATE TABLE MyNewTable (MyField1 int, MyField2 VARCHAR(20), MyField3 Int)")

'insert data in the new table from a select query to the original table
Dim sSQL as string, MyOriginalDBPath as String
sSQL = "INSERT INTO MyNewTable (MyField1, MyField2, MyField3) SELECT OriginalField1, OriginalField2, OriginalField3 FROM [" & MyOriginalDBPath & ";PWD=123].clients"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
'...

Note: this 'draft' idea assumes that the connection string is made to the Access database and the connection to the original database would be inside the SQL string, but i have not idea about the correct sintaxis. I have only tested this approach with different access databases. Note that this is for Access: ...[" & MyOriginalDBPath & ";PWD=123]...

The Jet database engine can reference external databases in SQL statements by using a special syntax that has three different formats:

[Full path to Microsoft Access database].[Table Name]

[ISAM Name;ISAM Connection String].[Table Name]

[ODBC;ODBC Connection String].[Table Name]

...

You can use an ODBC Data Source Name (DSN) or a DSN-less connection string:

DSN: [odbc;DSN=;UID=;PWD=]

DSN-less: [odbc;Driver={SQL Server};Server=;Database=; UID=;PWD=]

Some references:

Querying data by joining two tables in two database on different servers

C# - Join tables from two different databases using different ODBC drivers

Answer 4

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