Querying from another Excel or Access database

Question

I have two worksheets in an Excel file.

One worksheet acts as a database of the products we sell, with the columns Product ID, Product Code, and Description.

The other worksheet 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.

I use an INDEX search, but the database sheet has become too big to manage in the same file, leading to severe slow downs.

I was thinking of separating the database sheet as an Excel or AccessDB file but I think it will need a lot of VBA manipulation.

Answer 1

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):

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

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:

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