如何将ADO记录集保存到Access 2003中的新本地表中？

Question

我试图从FoxPro 9.0数据库导入表格到Access 2003中。到目前为止，从谷歌搜索和许多试验中，我连接到表的唯一方法是通过编程方式通过OLE DB连接。我已经建立了3个不同配置的ODBC连接，但它们都没有工作：我收到“未指定的错误”，我找不到任何信息。

使用OLE DB，我可以成功地连接到FoxPro数据库，并在ADO记录集中导入表。问题是我无法使用SQL将记录集保存到本地数据库中的新表中。 ADO记录集的行为与表格不同，所以我无法查询它们。下面的代码给我一个“类型不匹配”错误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

你将不得不做的是打开FoxPro表作为记录集并打开本地表作为另一个记录集。然后，您可以遍历的FoxPro记录和做这样的事情

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

它可能不是最快的方式，但它会工作

Answer 2

为什么不使用ODBC链接表？ http://support.microsoft.com/kb/225861

Answer 3

我终于找到了一个体面的解决方案。它涉及到使用的copyFromRecordset功能节省了内存中的ADO记录集到Excel文件，然后使用TransferSpreadsheet的（）在Excel文件程序链接到一个表...

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 4

让我画出了另一途径SQL查询，则可以简化：

'... 
'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 
'... 

注：该“草案”的想法假定连接字符串到Access数据库和连接到原始数据库是SQL字符串内部制定，但我还没有关于正确的sintaxis的想法。我只用不同的访问数据库测试了这种方法。 请注意，这是访问：... [ “& MyOriginalDBPath &”; PW​​D = 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=]

一些参考：

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

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