在Excel中查找Access数据库 [英] Looking up Access database in Excel

问题描述

我想做一些非常简单的事情：我有一个Access数据库，一个表将数千个产品ID映射到产品信息字段。在Excel工作表中，用户在第一列中键入了大约100个产品ID。我需要剩下的列从Access数据库中提取相关ID的信息。具体来说：

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


2. 我不希望任何值被自动更新，而是希望所有列只根据用户需求进行更新用户可以通过菜单选择刷新，也可以在工作表上选择基于VBA的刷新按钮。）

m认为这将是一个直接的用例，但是看起来很难找到一个解决方案。谢谢你提前！

解决方案

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

 ''参考：Microsoft ActiveX数据对象xx库
 Dim cn As New ADODB.Connection 
 Dim rs As New ADODB.Recordset 
 
''不是最好的方式来引用一个工作簿，但方便
''测试。最好是以名称来引用工作簿。 
 strFile = ActiveWorkbook.FullName 
 
''2007/2010的连接字符串
 strCon =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& strFile _ 
& ;扩展属性=Excel 12.0 xml; HDR = Yes;; 
 
 cn.Open strCon 
 
''MS Access的连接字符串
 scn =[; DATABASE = Z：\Docs\Test。 accdb]
''SQL查询字符串
 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 
 
''将返回的记录集写入工作表
 ActiveWorkbook.Sheets（Sheet7）。 （1，1）.CopyFromRecordset rs 
  

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

  Dim cn As Object 
 Dim rs As对象
 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 
 
''后绑定，所以不需要引用
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB。 Recordset）
 
 
 cn.Open strCon 
 
''根据数值引用选择一个字段
 strSQL =SELECT AText_ 
& FROM Table1 a_ 
& WHERE ID =&表格（Sheet7）。[A1] 
 
 rs.Open strSQL，cn，3，3 
 
表格（Sheet7）[B1] = rs！ 
  

I want to do something very simple: I have an Access database with one table mapping thousands of product IDs to product information fields. In an Excel worksheet, the user types in perhaps 100 product IDs in the first column. I need for the remaining columns to pull in information from the Access database for the corresponding IDs. Specifically:

1. if I use MS-Query, it seems to insist on the output being a table. I simply want the output to be inside a single cell. Preferably, a formula that involves a SQL-type query.
2. I don't want any of the values to be updated automatically, but rather want all the columns updated only on user demand (the user could either choose refresh through a menu, or a VBA-based refresh button on the sheet is fine as well).

I'm thinking this would be a straightforward use case, but it seems surprisingly hard to find a solution. Thank you in advance!

解决方案

Working from Excel, you can use ADO to connect to a database. For Access and Excel 2007/2010, you might:

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

Another possibility returns a single field from MS Access. This example uses late binding, so you do not need a library reference.

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

这篇关于在Excel中查找Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！