current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
1  
Jeff, why did you delete Automation error when trying INSERT INTO Access DB? –  HansUp Oct 18 '13 at 18:14
    
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 Oct 18 '13 at 18:18

1 Answer 1

up vote 0 down vote

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

  1. Run the query without the WHERE clause and store the result in a recordset;
  2. Store the data from the Excel sheet that you require in a dictionary, where the ItemNumber (PK?) is the key;
  3. Run through the recordset, and check with the typical dictionary Exists function if the ItemNumber from each record is available in the dictionary;
  4. 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).
share|improve this answer
    
Thanks! I will check out the link. If I run the query without the WHERE clause, the resulting recordset would be over 100,000 records. Would that cause an issue? –  Jeff Brady Oct 18 '13 at 14:27
1  
Checking 100000 records will take some time (using the alternative I proposed), although I'm not sure how long. You could test if it's doable; is it something you will do daily? Surely try to make the join after all; this would be the fastest. Or can you export the data to Access first and make the query there? That could be an option to consider as well. –  Kim Gysen Oct 18 '13 at 14:36
    
I'm currently trying to DoCmd.Transferspreadsheet acImport to put Sheet1 values into a table in Access, then do the query. Thank you for your help! –  Jeff Brady Oct 18 '13 at 14:45
    
You're welcome . –  Kim Gysen Oct 18 '13 at 14:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.