excel vba-ado连接到ms access数据库-筛选结果

g6baxovj 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(423)

我有一个ms access数据库，其中存储了一些产品信息，并有以下字段：
id、客户零件号、说明、价格、观察结果
我还有一个excel文件，其中有一个名为customer part no的列。在这个列中，我粘贴了要筛选的客户部分。
我需要一种方法，使我的access数据库查询使用excel和vba只返回记录，其中我有一个客户零件号之间的匹配。
当时我设法连接到数据库并检索所有记录。但是我仍然不知道如何只选择匹配的记录，我想这可能是sql查询中的某个内容，但我不知道它将如何进行。

Sub ADO_Connection()

'Creating objects of Connection and Recordset
    Dim conn As New Connection, rec As New Recordset
    Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "C:\SourcingDatabase.accdb"
'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
    conn.Open connString
'the query I want to run on the database.
    query = "SELECT * from Data;"

'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn

'clearing the content of the cells
    Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
    If (rec.RecordCount <> 0) Then
        Do While Not rec.EOF
            Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
            rec.Fields(1).Value
            rec.MoveNext
        Loop
    End If
'closing the connections
    rec.Close
    conn.Close

End Sub

sql excel ms-access vba ado

来源：https://stackoverflow.com/questions/62799593/excel-vba-ado-connection-to-ms-access-db-filtering-results

1条答案

按热度按时间

5cnsuln71#

如果有人感兴趣，找到解决方案：
下面是我的代码：

Sub ADO_Connection()

'Creating objects of Connection and Recordset
    Dim conn As New Connection, rec As New Recordset
    Dim DBPATH, PRVD, connString, query, partlist As String

    Dim cel As Range
    Dim selectedRange As Range

    Set selectedRange = Application.Selection

    partlist = "("

    For Each cel In selectedRange.Cells
        partlist = partlist & "'" & cel.Value & "',"
    Next cel

    partlist = Left(partlist, Len(partlist) - 1)
    partlist = partlist & ")"
    MsgBox partlist

'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "C:\SourcingDatabase.accdb"
'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
    conn.Open connString
'the query I want to run on the database.
    query = "SELECT * from Data WHERE CustomerPartNumber IN " & partlist & ";"
    MsgBox query

'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn

'clearing the content of the cells
    Cells.ClearContents

'getting data from the recordset if any and printing it in column A of excel sheet.
    If (rec.RecordCount <> 0) Then
        Do While Not rec.EOF
            Range("C" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = rec.Fields(1).Value

            rec.MoveNext
        Loop
    End If
'closing the connections
    rec.Close
    conn.Close

End Sub

赞(0）回复(0）举报 2021-07-24

我来回答

excel vba-ado连接到ms access数据库-筛选结果

1条答案

相关问题

热门标签

最新问答