Vlookup inside Access

how do I do something similar to look up values in Access?

right now I use queries in Excel to pull large tables into separate sheets in Excel and then I use vlookups to query these sheets. I want to get rid of that middle step if possible. Can I query the access database with a function or through VBA code?

Ex:

Say I have code 556752 and I want to look up the description from a table in Access, how do I do that?

I am trying to do this in Excel. If I have a list of Product Numbers in one column, I want to use sometime of Function like dlookup to pull the product descriptions from access or another database. How would I do this? I do not want to put the product number in the parameter field because that will not be dynamic. I want to pull it directly from the excel spreadsheet.

I want an excel solution to pull data from Access. I know you cannot use a V-lookup to pull data from Access but I was wondering if something equivalent can be done to pull data from Access.

Hi --

Did you get an answer yet? If you are looking to stay within Excel then your best bet is to use VBA to create a connection to the database and then create a loop in your Excel VBA to run down your rows one at a time. Something similar to the following . . .

'Create your connection to your Access db - depending on if you are Access 2007 or 2003 different connection strings - if you don't know the connection string reply and I'll give you two samples . . . I just don't want to get too long winded if you have already received an answer. You'll have to make sure your references are set to handle ADODB sets.

Assuming the value you are looking up (ie, 556752 from your question) is in column A and you want to put the value you are looking up in column B next to your value you looked up in column A then see the following code. the code is very rudimentary, but it might give you an idea . . .


Sub HereGoes()

dim jRecordset as ADODB.Recordset
dim jSQL as string
dim i as integer
dim EndRow as integer


Range("A1").select
i=0
EndRow=selection.end(xldown).row
for i=1 to EndRow

jSQL="Select FieldLookingFor FROM tblLookingFrom WHERE FieldMyLookup = " & activecell.offset(i,0) & ";"
set jRecordset= new adodb.recordset
jRecordset.Open 'plus your paramaters - if you don't have then I can forward - once again, not sure if you already have answer so . . .
if not jrecordset.eof then activecell.offset(i,1)=jrecordset!FieldLookingFor
end if
j

recordset.close
next i
set jrecordset = nothing

End Sub

Let me know if you need more or if this is going in the right direction. I assume from your question that your intent is to stay within Excel to get your lookup accomplished. You do have other options like a DLookup - but I like simple select queries for something like you mentioned.


Thanks -

Jim