Getting a value from a MS Access Database

[larsson7]

Hello,

I was wondering if there was any code that would 'lookup' a value in a MS Access database in the same way that a Vlookup formula looks up values in tables in Excel.

Even better, if there was a fuction already written to do this.

Thankyou for your time.

Regards,

Cam

 

[Richard Schollar]

It might be because I should have used [] for the table name as well. Try the following:

Dim adoCN As ADODB.Connection 
Dim strSQL As String 

Const DatabasePath As String = "C:\test.mdb" 

'Function argument descriptions 
'LookupFieldName - the field you wish to search 
'LookupValue - the value in LookupFieldName you're searching for 
'ReturnField - the matching field containing the value you wish to return 

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 
If adoRS.BOF And adoRS.EOF Then 
DBVLookUp = "Value not Found" 
Else 
DBVLookUp = adoRS.Fields(ReturnField).Value 
End If 
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 = DatabasePath 
adoCN.Open 
Exit Sub 
ErrHandler: 
MsgBox Err.Description, vbExclamation, "An error occurred" 
End Sub 

Sub test() 

MsgBox DBVLookUp("LC Process Settings", "ABC NUMBER", "ABC4139", "PAPER TENSION") 

End Sub

 

[Thebatfink]

Your not going to believe this. There was no "PAPER TENSION" in the database, it was "TOP PAPER TENSION". Im so sorry, it looks as though I have caused the error myself. I can confirm this now works with spaces in the field names etc. All looks good!!!

Well, I cant express my gratitude for your help in getting this working. Absolutely delighted!!

I do have one other query though if you have any idea of the answer. I need to know that under no circumstances can this script edit or delete any data in anyway. I need it purely as a look up and nothing more. I cant see anything in the script that is more than readonly but I'd like to make 100% sure before I let it loose on our main database.

Thanks again Richard. Much appreciated!

 

[Richard Schollar]

I'm glad you got it working

The recordset created by the code is ReadOnly:

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

so it won't amend the database in any way.

 

[Thebatfink]

Ok so it works great, it does everything it should. But I'm wondering if its possible to modify it some more. I'm having problems with slow down.

In my sheet I have say 9 columns. "A" has a part number, this is what each lookup is searching for in the database. Columns "B:I" have the DBVLookUp's in and are searching different information on the part number in column "A".

Whenever I change a part number, the wholesheet recalculates, and theres a mass of lookups go off. Can it be made to calculate the whole sheet when the workbook is opened, but from then on, all the lookups in say row 1 (looking at the part number in A1 as the search field entry) only update if the part number in A1 is changed and so on..

Does that make sense at all?

 

[evdokimos]

Hi all

I use the function in provided by "dk" in the second quote (first page) and it works perfect. However my database has many records with the same LookupValue. This function returns the first record with the specific LookupValue. However I want the function to return the most recent (last) record. How can the function be modified?

I thank you in advanced

Elias Koumides

 

[Norie]

Elias

I think all you would need would be the correct SQL statement for returning the result you want.

If you can, and this is what I would do, try creating the required query in whatever database you are using.

Once you have that, and the SQL for it, then you can use it with the original function.

 

[Ruddles]

I use the function in provided by "dk" in the second quote (first page) and it works perfect. However my database has many records with the same LookupValue. This function returns the first record with the specific LookupValue. However I want the function to return the most recent (last) record. How can the function be modified?

Sorry, I've just spotted this so I'm coming in cold. Your code only uses the first record of the recordset - is that correct? In that case if you sort your recordset in descending order (by date?), the first record should be the latest one.

Try this (untested):-

strSQL = "SELECT [" & LookUpFieldName & "], [" & ReturnField & _ 
"] FROM [" & TableName & _ 
"] WHERE [" & LookUpFieldName & "]='" & LookupValue & "' " & _
"ORDER BY [[COLOR=red][B][I]SortFieldName[/I][/B][/COLOR]] DESC;"

You'll need to decide which field you want to sort on to get the latest record sorted at the top and replace the code in red with the correct fieldname.

If you have problems getting this to work, the other alternative is to do an adoRS.MoveLast as soon as the recordset's open so your record pointer is pointing at the last record which is effectively the most recent one, although if you do this you have to satisfy yourself that the last record in the recordset really is always the most recent one.

HTH...

 

[Norie]

Elias

How are you determining the most recent record?

By date?

Let's say you have a table with 3 fields - ID, OrderDate and Description.

This query will return the most recent date and description for the record with ID 54.

SELECT tblTest.ID, tblTest.OrderDate, tblTest.Description
FROM tblTest
GROUP BY tblTest.ID, tblTest.OrderDate, tblTest.Description
HAVING (((tblTest.ID)=54) AND ((tblTest.Date)=(SELECT Max([OrderDate]) As MaxDate FROM tblTest As T2 WHERE [T2].[ID]=[tblTest].[ID])));

 

[evdokimos]

Thanks Norie, this is what I was looking for. I modify the original macro with the new query and it works fine.

evdokimos

 

[evdokimos]

Hi again
I now want to create a new function which will look for a field in Access. The field has 9 characters and I want to enter the first (say 8 ) characters and the function will return the whole number as stored in Access. I have made the following modifications to the original function:
1. I modified the arguments as: DBVLookUp(TableName As String, LookUpFieldName As String, LookupValue As String) As Variant
2. I modified the sql statement as: strSQL = "SELECT " & TableName & ".[" & LookUpFieldName & "] FROM " & TableName & " WHERE ((" & TableName & ".[" & LookUpFieldName & "]) Like (" & Chr(34) & LookupValue & "*" & Chr(34) & "));"
3. I modified the following statements as:
If adoRS.BOF And adoRS.EOF Then
DBVLookUpAll = "Value not Found"
Else
DBVLookUpAll = adoRS.Fields(LookUpFieldName).Value
End If

Although I have checked the strSQL created and works ok if I pasted it in a query in Access, the function returns "Value not Found".

Can anyone help me.

I thank you in advanced for your kind help.

evdokimos