如何用Access从用友直接取数

pawaluodiii

因为用友凭证导出不方便，想直接用Access从用友数据库里面取数，怎么才能实现，请教高手？

rainless

8.12前的版本也是access做数据库的，所以可以直接用access连接，8.12后的版本改用SQL数据库，所以access要通过ODBC连接。

Scarlett_88

将SQL Server数据库数据保存为Access数据库
Public Sub 实例7_13()
    Dim cnnSQL As New ADODB.Connection
    Dim rsSQL As New ADODB.Recordset
    Dim cnnData As New ADODB.Connection
    Dim rsData As New ADODB.Recordset
    Dim myCat As New ADOX.Catalog
    Dim myTbl As New ADOX.Table
    Dim mySQL As String
    Dim myData As String
    Dim myTable As String
    Dim myName As String, myType As Integer, mySize As Integer
    Dim i As Long, j As Long
    myData = "商品信息"
    myTable = "商品名录"
    CnnStr = "Provider=SQLOLEDB.1;" _
        & "User ID=sa;" _
        & "Data Source=THTFCOMPUTER;" _
        & "Initial Catalog=" & myData
    With cnnSQL
        .ConnectionString = CnnStr
        .Open
    End With
    rsSQL.Open myTable, cnnSQL, adOpenKeyset, adLockOptimistic
    myData = ThisWorkbook.Path & "\商品信息.mdb"
    On Error Resume Next
    Kill myData
    On Error GoTo 0
    myCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myData
    With myTbl
        .Name = myTable
        For i = 0 To rsSQL.Fields.Count - 1
            myName = rsSQL.Fields(i).Name
            myType = rsSQL.Fields(i).Type
            If rsSQL.Fields(i).Type = 200 Then myType = adVarWChar
            If rsSQL.Fields(i).Type = 135 Then myType = adDate
            mySize = rsSQL.Fields(i).DefinedSize
            .Columns.Append myName, myType, mySize
        Next i
    End With
    myCat.Tables.Append myTbl
    Set cnnData = myCat.ActiveConnection
    rsData.Open myTable, cnnData, adOpenKeyset, adLockOptimistic
    For i = 1 To rsSQL.RecordCount
        With rsData
            .AddNew
            For j = 0 To rsData.Fields.Count - 1
                .Fields(j) = rsSQL.Fields(j).Value
            Next j
            .Update
        End With
        rsSQL.MoveNext
    Next i
    MsgBox "已经成功将SQL Server数据库数据保存为Access数据库！" & vbCrLf _
        & "数据库名为：<" & Mid(myData, Len(ThisWorkbook.Path) + 2) & ">" _
        & vbCrLf & "数据表名为：<" & myTable & ">", vbInformation
    rsSQL.Close
    cnnSQL.Close
    rsData.Close
    cnnData.Close
    Set rsSQL = Nothing
    Set cnnSQL = Nothing
    Set rsData = Nothing
    Set cnnData = Nothing
End Sub

zhaozyuan

看来用友还须改进

pawaluodiii

谢谢大侠！