PDA

View Full Version : Looking up Access database



herzberg
10-28-2007, 07:44 PM
Hi all,

I've 2 lists, one in an Excel workbook, another in an Access database. For illustration's sake, let's say there are 3 columns in the former: Item no, Item description and Item price. Only the first 2 columns, i.e. Item no and Item description are populated; Item price is empty, save the header.

In the database table, there are 2 columns, Item no and Item price, both are populated.

What I need to do is to lookup the table in Access, compare the Item no and return the associated Item price from Access to Excel. It's very much like the good old Vlookup but with a different twist.

Is there any way I can do it via VBA code? I would like to make the process seamless and transparent for the user, so that the lookup can be completed with a click (OK, maybe a few clicks) of a button. Thanks!

gnod
10-28-2007, 09:59 PM
copy the table of your access file into another sheet of your excel file then use the VLookUp..

this is the code i use to copy the data from Access..

If Not rst_Data.EOF Then
With Worksheets("Data")
.Unprotect strPassword
' Start with Row 5, Col 2
.Cells(5, 2).CopyFromRecordset rst_Data
.Protect strPassword
End With
Else
MsgBox "No records returned", vbCritical
End If

herzberg
10-29-2007, 12:08 AM
Hi gnod,

Initially, I thought of that too but there are some 240 000 records in the Access table (Yes! I'm not kidding!), making importing the full data set into Excel near impossible. Thus, that got me to think if it was possible to do a direct lookup instead.