ACCESS 2010 VBA操作 Viaual Foxpro数据库

时　间:2015-04-01 20:32:43
作　者:Big Young   ID：252  城市：襄阳
摘　要:简要说明了如何用VBA来操作Visual Foxpro的数据库表。
正　文:

一、下载并安装Microsoft OLE DB Provider for Visual FoxPro 9.0

        下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=14839

二、确认VFP数据表的类型

    VFP数据表有库表和自由表两种，不同的类型其连接方式不同。

    数据库表的连接串为：Provider=vfpoledb;Data Source=C:\MyDbFolder\MyDbContainer.dbc;
Collating Sequence=machine;

    自由表的连接串为：Provider=vfpoledb;Data Source=C:\MyDataDirectory\MyTable.dbf;
Collating Sequence=machine;

三、通过了ADO对象库访问VFP

Sub 操作VFP表()
    'Define the OLE DB connection string.
    strConnectionString = "Provider=vfpoledb;Data Source=D:\temp\库存.DBF;Collating Sequence=machine;"
   
    'Instantiate the Connection object and open a database connection.
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strConnectionString
   
    'Define SQL Select statement.
    strSQL = "Delete FROM 库存.DBF Where  A='产品 ID';"
   
    'Use the Execute method to issue a SQL query to database.
    cnn.Execute strSQL, , AdCmdTable + adExecuteNoRecords

End Sub

Public Sub 查询VFP表()
    Dim strConnectionString As String
    Dim strSQL As String
    Dim cnn As Object
    Dim rst As Object
   
    '建立与数据源的连接
    strConnectionString = "Provider=vfpoledb;Data Source=D:\temp\库存.DBF;Collating Sequence=machine;"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strConnectionString
   
    '实例化 Recordset 对象
    Set rst = CreateObject("ADODB.Recordset")
   
    '用 Open 方法打开一个记录集并用 Connection 对象建立连接
    strSQL = "Select * FROM 库存.DBF;"
    rst.Open strSQL, cnn
   
    'rst.MoveFirst
    Dim i As Long
'    i = rst.Fields.Count
'    Debug.Print i
    '用 MoveNext 方法循环访问整个记录集并显示结果
    Do Until rst.EOF
        Debug.Print rst.Fields(0).Value & "  " & rst.Fields(1).Value & "  " & rst.Fields(2).Value & "  " & rst.Fields(3).Value & "  " & _
          rst.Fields(4).Value & "  " & rst.Fields(5).Value & "  " & rst.Fields(6).Value & "  " & rst.Fields(7).Value & "  " & _
          rst.Fields(8).Value & "  " & rst.Fields(9).Value & "  " & rst.Fields(10).Value & "  " & rst.Fields(11).Value & "  " & _
          rst.Fields(12).Value & "  " & rst.Fields(13).Value & "  " & rst.Fields(14).Value
     rst.MoveNext
   Loop

    Set rst = Nothing
    Set cnn = Nothing
End Sub