Lookup

[blooneptune]

Hello all.

I am trying to find out if there is a vLookup type formula to retieve data from an access database (91000 entries). I want to enter a product number in excel, and for it to return description and location in warehouse from Access.

Thanks in advance.

 

[Richard Schollar]

Hi

You can only do this via a user-defined formula that will link to the database. The code is not overly complicated - here's one I wrote for myself:

Function GetQl(ByVal GLcode As String, GLfield As String)
Dim dbLedger As DAO.Database
Dim rsData As DAO.Recordset
'Need to set a reference to Microsoft DAO object library
Set dbLedger = OpenDatabase("G:\Accounts\z_Financial Reporting\" & _
    "2006\QL Data\FSA-MGMTaccts.mdb", False, True, ";pwd=finance")
    
Set rsData = dbLedger.OpenRecordset("SELECT * FROM tblCategorisedQLDATA " _
    & "WHERE Ledger_Key like'" & GLcode & "';", dbOpenSnapshot)
 
GetQl = rsData.Fields(GLfield)
End Function

Hope this helps!

Richard

 

[blooneptune]

Thanks for this. However my VBA skills are nil. I know that parts of this code need to be edited to suit my own database - just not sure which..

 

[Richard Schollar]

OK

What's the location of your Access Db? eg C:\Database\Access.mdb

What is the name of the Table that you want to Lookup a value in, and what field has the unique lookup value (like a productID) and what field do you want to return (what's the name of it in the table)?

Does your access db require a password?

Richard