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]

Does the code successfully step thru the Connection creation sub? I am struggling to think of a reason why this might be happening. The code works as intended on my computer.

 

[pcc]

Sorted. Prompted by your question I dug in a bit further, and the setup routine was not working properly. This was because I was using the 3.51 option as sugessted in the original code. I changed it to 4.0 and it's fine now (but I am using Access 97!!)


Thanks very much for your suggestion, all OK now

 adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97

 

[Thebatfink]

Hi

You need to step thru the code and see why it might be erroring. In the same module, create a simple test sub:

Sub test()

msgbox dbvlookup("Table","LookupField","LookupValue","ReturnField")

End Sub

and whilst the cursor is in this macro, hit F8 (repeatedly) and step thru the code. See if it errors out anywhere. Report back with what you find.

EDIT: Don't forget to amend "Table", "LookupField" etc with your actual values.

Firstly, thanks for trying to help!! Appreciate it, this is really killing me now!!

I've inserted your line at the bottom of the module and stepped through.. Heres what I found.

It cycles through the test sub and then jumps to the public function DBVLookup, it then steps through this function fine, until it see's

If adoCN Is Nothing Then SetUpConnection

it then jumps to the SetUpConnection sub. It steps through that fine and then returns back upto the DBVLookup function here,

Set adoRS = New ADODB.Recordset

It then gets as far as,

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

And the following error is displayed.

Run-time error '-2147217900 (80040e14)':

Syntax error (missing operator in query expression 'ABC NUMBER'.

I have this in my code -

strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "='" & LookupValue & "';"
' If lookup value is a number then remove the two '

as my fields, and field entries have a mixture of numbers and letters in them. Does this shed any light on the problem? I can't understand how it has worked for several people including yourself (there are several threads like this one) yet for some reason its not working for us??

Cheers!

 

[Richard Schollar]

What are the values you are passing to the function? ie what are "TableName", "LookUpFieldName", "LookupValue" and "ReturnField" - the one I am most interested in is "LookupValue".

 

[Thebatfink]

Sub test()

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

End Sub

TableName is - LC Process Settings
LookUpFieldName is - ABC NUMBER
LookUpValue is - ABC4139
ReturnField is - PAPER TENSION

Incidently if I change all the names to have no spaces (with underscores) it then errors it can't find the tablename, so it must be connecting to the database ok? Is this something to do with having numbers and letters in the names and field entries or maybe because of spaces in the table and field names?

Thanks! Hope you can see whats wrong!!

EDIT:
Just a little more information. I didnt write the DB, but I am a user of it. To get all the field and table names what I am doing (Im not used to Access at all) is opening the .mdb in excel, then selecting the table from the list and then viewing the field names and the field entries like looking at a spreadsheet. I can confirm now that the field names and table names are correct (I think?) and the entry I am trying to do the lookup on is at the very top of the table so I know its there!

 

[Richard Schollar]

I strongly suspect it's all those spaces in the field names etc. Try the test macro with the following amended line of code:

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

I'm assuming that you are using single quotes in the SQL string itself around the ABC4139 value? If not, then you may need to amend to:

DBVLookUp("[LC Process Settings]", "[ABC NUMBER]", "'ABC4139'", "[PAPER TENSION]")

 

[Thebatfink]

Thanks for the reply. Here is the code I am using in full. Im still very new to vb and maybe I am missing something that is obvious to others.

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

I have tried both your suggestions for the DBVLookUp test sub.

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

This gave the error as follows -

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

DBVLookUp("[LC Process Settings]", "[ABC NUMBER]", "'ABC4139'", "[PAPER TENSION]")

This gave the error as follows -

Run-time error '-2147217900 (80040e14)':

Syntax error (missing operator) in query expression '[ABC NUMBER]="ABC4139".

It errors at the same point as before

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then

Does this shed any more light on the situation? My biggest problem here is its not under my control to edit the database, is there any way to get around the spaces??

Thank you for your help

 

[Richard Schollar]

Hi

I'm absolutely certain now it's the spaces. Give the following amended code a go (and use your old line "MsgBox DBVLookUp("LC Process Settings", "ABC NUMBER", "ABC4139", "PAPER TENSION") "):

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

 

[Norie]

Batfink

Why the single quotes here "'ABC4139'"?

Single quotes are added when the SQL is created.

strSQL = "SELECT [" & LookUpFieldName & "], [" & ReturnField & _
"] FROM `" & TableName & _
"` WHERE [" & LookUpFieldName & "]='" & LookupValue & "';"

 

[Thebatfink]

Batfink

Why the single quotes here "'ABC4139'"?

Single quotes are added when the SQL is created.

strSQL = "SELECT [" & LookUpFieldName & "], [" & ReturnField & _
"] FROM `" & TableName & _
"` WHERE [" & LookUpFieldName & "]='" & LookupValue & "';"

Those where one of the lines of code he asked me to try thats all.

Richard,

Sorry for the late reply. I have now tried the whole new code and the test sub line, however it is "still" giving me an error!!! So frustrating! The error is as follows:

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

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

Thats my code pasted out of the module..
One thing I tried was changing the name of the table in the test sub to something that didnt exist. When I do this I get an error saying it cant find the table "blah blah blah", what confuses me is that in the error message it states the purposely incorrect table name I entered with spaces and all. So does that mean its actually seeing the names correctly?

Run-time error '-2147217865 (80040e37)':

The Microsoft Jet database enging cannot find the input table or query 'AA Process Settings'. Make sure it exists and that its name is spelled correctly.

I decided to use a correct table name, but everything else (field return field etc etc) with bogus names to see which part of the query it was stumbling at. With a correct table name and all the others bogus names that do not exist, it simply says the no value given for some parameters error.. Not that a field doesnt exist as I would of expected maybe?

Does that help at all?

Thanks again.