sql - 在 Excel 中查找 Access 数据库

Question

我想做一些非常简单的事情:我有一个 Access 数据库，其中一张表将数千个产品 ID 映射到产品信息字段。在 Excel 工作表中，用户可能会在第一列中输入 100 个产品 ID。我需要剩余的列从 Access 数据库中提取相应 ID 的信息。具体来说:

如果我使用 MS-Query，它似乎坚持输出是一个表。我只是希望输出在单个单元格内。最好是涉及 SQL 类型查询的公式。

我不希望自动更新任何值，而是希望仅根据用户需求更新所有列(用户可以通过菜单选择刷新，或者工作表上基于 VBA 的刷新按钮也可以)。

我认为这将是一个简单的用例，但似乎很难找到解决方案。先感谢您!

Answer 1

在 Excel 中工作，您可以使用 ADO 连接到数据库。对于 Access 和 Excel 2007/2010，您可以:

''Reference: Microsoft ActiveX Data Objects x.x Library
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

''Not the best way to refer to a workbook, but convenient for 
''testing. it is probably best to refer to the workbook by name.
strFile = ActiveWorkbook.FullName

''Connection string for 2007/2010
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"";"

cn.Open strCon

''In-line connection string for MS Access 
scn = "[;DATABASE=Z:\Docs\Test.accdb]"
''SQL query string
sSQL = "SELECT a.Stuff, b.ID, b.AText FROM [Sheet5$] a " _
& "INNER JOIN " & scn & ".table1 b " _
& "ON a.Stuff = b.AText"
rs.Open sSQL, cn

''Write returned recordset to a worksheet
ActiveWorkbook.Sheets("Sheet7").Cells(1, 1).CopyFromRecordset rs

另一种可能性是从 MS Access 返回单个字段。此示例使用后期绑定(bind)，因此您不需要库引用。

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

strFile = "z:\docs\test.accdb"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open strCon

''Select a field based on a numeric reference
strSQL = "SELECT AText " _
       & "FROM Table1 a " _
       & "WHERE ID = " & Sheets("Sheet7").[A1]

rs.Open strSQL, cn, 3, 3

Sheets("Sheet7").[B1] = rs!AText