从另一个Excel或Access数据库查询

huangapple go评论60阅读模式
英文:

Querying from another Excel or Access database

问题

Hi,我目前在Excel文件中有两个工作表,其中一个充当我们销售的所有产品的数据库,包含“产品ID”、“产品代码”和“描述”列(如下所示)。

我有另一个工作表,充当产品查找工具,您可以将多个产品ID粘贴到第一列,它将在相邻的列中返回产品代码和描述(如下图所示)。

我目前使用INDEX函数进行搜索,但数据库工作表已经变得太大,无法在同一文件中管理,导致严重减慢速度。对此有什么最简单的解决方案吗?我考虑将数据库工作表分离为Excel或Access数据库文件,但我认为如果这样做,将需要大量的VBA操作。非常感谢任何帮助。

英文:

Hi I currently have two worksheets in an excel file with one of them acting as a database of all the products we sell, with the columns Product ID, Product Code, and Description (sample below).

从另一个Excel或Access数据库查询

I have another worksheet that acts as a product finder tool, where you would paste multiple Product IDs in the first column and it would return the Product code and Description in the adjacent columns (image below).

从另一个Excel或Access数据库查询

I currently use an INDEX search to make this happen, but the database sheet has become too big to manage in the same file, leading to severe slow downs. What would be the easiest solution for this? I was thinking of separating the database sheet as an Excel or AccessDB file but I think I will need a lot of VBA manipulation if I do that. Any help would be much appreciated.

答案1

得分: 1

你可以使用ADO和执行SQL查询来访问Microsoft Access中的数据。

您能告诉我是否可以将单元格范围提供给WHERE子句吗?

是的,有一个技巧。SQL命令是纯文本,您只需要用您的参数构建它。在WHERE子句中使用IN运算符。

我创建了一个示例假数据集。这是我的Excel产品查找器(一个名为Table1的表):

从另一个Excel或Access数据库查询

请注意,我只想要产品6、3和2的信息。现在是我的虚假数据库:

从另一个Excel或Access数据库查询

查询这些特定产品的代码:

Sub TEST()
Dim cnn As Object
Dim RST As Object
Dim DatabasePath As String
Dim i As Long
Dim Allid As String
Dim Arrayid As Variant
Dim SQLQuery As String

DatabasePath = "C:\Temp\temp.accdb" '数据库路径

'创建连接对象。
Set cnn = CreateObject("ADODB.Connection")

'创建记录集对象
Set RST = CreateObject("ADODB.Recordset")

'使用OLE DB连接字符串打开连接。
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath & ";Persist Security Info=False;"

'将所有ID合并成一个单个字符串
Arrayid = Range("Table1[PRODUCT ID]").Value
For i = LBound(Arrayid) To UBound(Arrayid) Step 1
    Allid = Allid & Arrayid(i, 1) & ","
Next i

Allid = Left(Allid, Len(Allid) - 1) '去掉最后一个逗号
Erase Arrayid '清除数组变量

'指定查询
SQLQuery = "SELECT PRODUCT_TABLE.[Product Id], PRODUCT_TABLE.[Product Code], PRODUCT_TABLE.Description FROM PRODUCT_TABLE " & _
    "WHERE PRODUCT_TABLE.[Product Id] In (" & Allid & ") ORDER BY PRODUCT_TABLE.[Product Id]"

'使用Open方法打开记录集
'并使用Connection对象建立的连接。
RST.Open SQLQuery, cnn

'将所有数据复制到单元格。这将带来完整的查询,不包括标题
Range("A6").CopyFromRecordset RST

'关闭和清理变量
RST.Close
cnn.Close
Set RST = Nothing
Set cnn = Nothing

End Sub

执行代码后,我得到了这个:

从另一个Excel或Access数据库查询

请注意,输出不是按照我们之前要求的顺序排序。我们要求的顺序是6、3、2,但输出是2、3、6!这是因为我的SQL查询包含了ORDER BY操作符,它按照ID字段排序。如果没有ORDER BY子句,输出将按照数据库中存储的方式进行排序,而不是按照Excel中的方式。

如果您真的需要输出与您的产品查找器完全相同的顺序,您可以创建一个UDF函数来查询每个单个ID并返回每个产品的单行,但如果您处理大量数据,这可能会消耗大量时间。因此,请仔细考虑如何处理这一部分。

顺便说一句,请确保使用正确的连接字符串。您可以在Access连接字符串上找到很多连接字符串。

英文:

You can access your data in Microsoft Access using ADO and doing a SQL query to gather data.

Could you tell me if it's possible to give a cell range to the WHERE clause?

Yes, there is a trick. SQL commands are plain text, you just need to build it with your parameters. Use the operator IN in the WHERE clause.

I made a fake dataset as example. Here's my Excel Product Finder (a table named Table1):

从另一个Excel或Access数据库查询

Notice I want the info only of products 6,3 and 2. Now my fake database:

从另一个Excel或Access数据库查询

The code to query those specific products:

Sub TEST()
Dim cnn As Object
Dim RST As Object
Dim DatabasePath As String
Dim i As Long
Dim Allid As String
Dim Arrayid As Variant
Dim SQLQuery As String



DatabasePath = "C:\Temp\temp.accdb" 'path to database

'Create a connection object.
Set cnn = CreateObject("ADODB.Connection")

'Create recordset object
Set RST = CreateObject("ADODB.Recordset")

'Open a connection using the OLE DB connection string.
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath & ";Persist Security Info=False;"

'merge all ID into one single string
Arrayid = Range("Table1[PRODUCT ID]").Value
For i = LBound(Arrayid) To UBound(Arrayid) Step 1
    Allid = Allid & Arrayid(i, 1) & ","
Next i

Allid = Left(Allid, Len(Allid) - 1) 'get rid of last comma
Erase Arrayid 'clean array variable

'specify query
SQLQuery = "SELECT PRODUCT_TABLE.[Product Id], PRODUCT_TABLE.[Product Code], PRODUCT_TABLE.Description FROM PRODUCT_TABLE " & _
    "WHERE PRODUCT_TABLE.[Product Id] In (" & Allid & ") ORDER BY PRODUCT_TABLE.[Product Id]"


'Open a recordset using the Open method
'and use the connection established by the Connection object.
RST.Open SQLQuery, cnn

'copy all data into cells. This will bring full query without headers
Range("A6").CopyFromRecordset RST

'close and clean variables
RST.Close
cnn.Close
Set RST = Nothing
Set cnn = Nothing

End Sub

After executing code I get this:

从另一个Excel或Access数据库查询

NOTICE that the output is not sorted as we had before. We asked the products in order 6,3,2 but the output is 2,3,6!

This is because my SQL query got the operator ORDER BY that sorts by ID field. If there is no ORDER BY clause the output will be sorted as it is in the database stored, not as your Excel.

If you really really really need the output to be exactly in the same order that your Product Finder, you can create an UDF function to query each single id once and return a single row for each product but if you work with a lot of data this can consume a lot of time. So think carefully how to approach this part.

By the way, make sure you use the right connection string. You can find many on Access connection strings

huangapple
  • 本文由 发表于 2023年2月8日 19:08:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75384903.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定