vlookup V's Access

[mnmhenry]

I currently use vlookup to lookup products in a seperate woorkbook. Can I have this database of products in Access and still refer to the infomation from excel ?

If I can, How do I do it ?

Thanks.
Mark Henry

 

[Ian Mac]

Yes! It does depend on what you want to do with the data, I use Database queries, check the help files for this function.

 

[dk]

Hi,

I don't think there's a way of doing this with built in formulas (I may be wrong). You could use this function which works in a similar way to VLOOKUP but for Access databases. You need to set a reference to the Microsoft ActiveX Data Object 2.x Library (from Tools, References in the VB editor).

Dim adoCN As ADODB.Connection
Dim strSQL As String

Public Function DBVLookUp(TableName As String, LookUpFieldName As String, LookupValue As String, ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & _
         "='" & LookupValue & "';"    ' If lookup value is a number then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
DBVLookUp = adoRS.Fields(ReturnField).Value
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler

Set adoCN = New Connection

adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
adoCN.ConnectionString = "K:financemanagement reportingfinance reporting 2002.mdb" 'Change to your DB path
adoCN.Open
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"

End Sub

Hopefully, the function should be straightforward to use. TableName is obviously the name of the table in the database, LookupFieldName is the name of the field whose values you are searching, LookupValue is the value in LookupfieldName you are looking for and ReturnField is the name of the field whose matching value you wish to return. If there are more than 2 entries matching LookupValue then it will only return the first one.

HTH, any problems please repost,

Regards,
D

 

[wi_guy]

Yes you can do it, as Ian Said, it's called ODBC linking (Open database Connectivity i think)....A very handy / powerful feature to pull from Access or mainframe or other databases. The only problem I've found is that it can be a bit tricky to set up the linking, but if you are familiar with access, it shouldn't be too bad. It's under: DATA:GET EXTERNAL DATA: CREATE NEW QUERY
it walks you through, asking where the database and tables reside. One important step is where it asks you if you want to save the query......if you are going to use this more than once, save it to a name, then you can go through the DATA:GET EXTERNAL DATA: RUN EXTERNAL QUERY and the data will repopulate. Hope this helps

 

[wi_guy]

I have an MS-Word document that I created for a presentation that gives step by step instructions for this....I'll email it to you.

 

[Ziggy]

From Access Use the link table(get external data or from new table button) option to create a link between Excel for each sheet.

Then you'll have 2 tables in access changes in access will be referenced in Excel and vice versa.

Then create a new query from the 2 tables, to keep it simple you can just drag a join between the 2 tables (in query). Right click the join line and choose the appropriate selection for you. Then put the fields in you want to include in the query.

I do this all the time I have one table with stock listing and the other with order info, the query tells me how much stock is available for the orders.