Yes! It does depend on what you want to do with the data, I use Database queries, check the help files for this function.
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
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).
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.Code: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
HTH, any problems please repost,
Regards,
D
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
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.
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.
Like this thread? Share it with others