Excel VBA - how to query Access database using column values as a parameter?

Question

I have an Excel worksheet that has a list of about 1000 Item Numbers in column A on Sheet1. Currently, I import Sheet1 into an Access table named ItemNumbers and run the following query:

SELECT MyTable.ItemNumber, MyTable.ItemName, MyTable.ItemPrice
FROM [ItemNumbers] INNER JOIN MyTable ON [ItemNumbers].ItemNumber = MyTable.ItemNumber
ORDER BY MyTable.ItemNumber;

And then I copy/paste the output to Sheet2.

How can I do this in VBA in Excel and put the results in a recordset? I can figure out how to loop through the recordset and put the results in Sheet2. I'm just not sure on the code to run the query.

I have the following so far. It just needs to be modified to use the values in Sheet1 Column A.

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\MyDatabase.accdb"
strSql = "SELECT MyTable.ItemNumber, MyTable.ItemName, MyTable.ItemPrice " & _
         "FROM MyTable " & _
         "WHERE WHERE (((MyTable.ItemNumber)= ??? IS IN Sheet1!A:A ???  )) " & _
         "ORDER BY MyTable.ItemNumber;"             
cn.Open strConnection
Set rs = cn.Execute(strSql)
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks!!!

Jeff, why did you delete Automation error when trying INSERT INTO Access DB? — HansUp
OOPS! I thought it was a duplicate of something else I was working on, but I forgot this was something different that I was working on this morning. I accidentally deleted it! — Jeff Brady

html_programmer html_programmer · Accepted Answer · 2013-10-18T14:23:31

If I understand right; what you ask is to join a table from Access with a table in Excel (ADODB).

Check this link from SO, and see if it's helpful:
Selecting 2 tables from 2 different databases (ACCESS)

I haven't tried to combine Access and Excel before, but my guess is that it will work for Excel as well.

An alternate way (and that will certainly work):

Run the query without the WHERE clause and store the result in a recordset;
Store the data from the Excel sheet that you require in a dictionary, where the ItemNumber (PK?) is the key;
Run through the recordset, and check with the typical dictionary Exists function if the ItemNumber from each record is available in the dictionary;
If the record is availabe, store the recordset values in a separate array (or dictionary) that you can use for further manipulation, (or perform direct actions if that's what you want to do).

Excel VBA - how to query Access database using column values as a parameter?

1 Answers