DBVLOOKUP and MS Access text

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Hello All
I have searched the board for an answer to this question with no success.
Can anyone comment

Here's the function that has been previously posted:

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:\Program Files\foodmart 2000.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

A person would use this formula (=DBVLookUp("account","account_id",A2,"account_description") in Excel to achieve a vlookup feature that uses Access to obtain the data.

First of all Thanks for posting this cool lookup function, but I can't get it to work when my lookup column field in Access is set to data type text. Is there another way?

Thanks in advance
Romefucan
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Giacomo
I am getting a syntax error in the SQL statement, won't compile

strSQL = "SELECT " & Device Signal & "," & Description & _
" FROM " & Device Signals & _
" WHERE [" & Device Signal & "] = '" & LookupValue & "' ;"

No, I have not tried your alternate function, but its on my list for today

Romefucan
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Is "Device Signal" the variable that holds the name of the lookup field? If so, it should be used with brackets "[ ]"around it in your code:
... & [Device Signals] & ...
Without the brackets, VBA thinks you are using two variables.
Is "Device Signals" the variable that holds the name of the table you are doing the lookup in? If so, again it needs the brackets around it because it also has a space in it's name.
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Variables/ Fields

Gentlemen
Device Signals with an "S" is the Table
Device Signal without the "S"is the field that is being looked up?

Hope this clarifys.

Romefucan
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

I noticed that, but what about putting the brackets "[ ]" around the variable name? It will not work without it, therefore you are getting a compile error.
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Code

Gentlemen
Cell B2=DBVLookUp("Device Signals","Device Signal",B1,"Description")
Cell B1= AAT

Code
'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 " & [Device Signal] & "," & Description & _
" FROM " & [Device Signals] & _
" WHERE " & [Device Signal] & " = '" & 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(Description).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

Still getting #VALUE Error


However, after trying Giacamo's other approach posted above

Cell B2==DBVLookUp2("Device Signals",B1,"Description")
Cell B1 = AAT

Code
Public Function DBVLookUp2(TableName As String, _
Criteria As String, _
ReturnField As String) As Variant

'Make sure to reference the Microsoft Access 9.0 Object Library
Dim ObjAccess As Object
Const DatabasePath As String = "D:\Visual Engineering Tools\GE Acronyms.mdb"

On Error GoTo err_DBVLookUp2

'Open Access Database
Set ObjAccess = New Access.Application
ObjAccess.OpenCurrentDatabase DatabasePath

DBVLookUp2 = ObjAccess.Application.DLookup("Description", "Device Signals", Criteria)

'Housekeeping
ObjAccess.CloseCurrentDatabase
ObjAccess.Quit
Set ObjAccess = Nothing
Exit Function

err_DBVLookUp2:
MsgBox Err.Description, vbCritical, Err.Number
Resume Next
End Function

Getting the Error : The Expression you entered as a query parameter produced this error: Microsoft Access cannot find the name you entered AAT in the expression.

Maybe there is hope for Giacomo's second approach?
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60

ADVERTISEMENT

A breakthru

Gentlemen
The code below is showing some progress thanks to Giacomo. In its present form the code is returning my acronym description however it is returning the first record description in every column

There must be a variable missing. can you please have a look? Thanks again for your patience.

Example of problem
Cell B2=DBVLookUp2("Description","Device Signals",B1)
Cell B1 = AAT
Code Returns to Cell B2 "AA" ("AA" is First record Description in Access)

Cell C2=DBVLookUp2("Description","Device Signals",C1)
Cell C1 = AFPAP
Code Returns to Cell C2 "AA" ("AA" is First record Description in Access)

And so on in every column

Code as follows:

Public Function DBVLookUp2(TableName As String, _
Criteria As String, _
ReturnField As String) As Variant

'Make sure to reference the Microsoft Access 9.0 Object Library
Dim ObjAccess As Object
Const DatabasePath As String = "D:\Visual Engineering Tools\GE Acronyms.mdb"

On Error GoTo err_DBVLookUp2

'Open Access Database
Set ObjAccess = New Access.Application
ObjAccess.OpenCurrentDatabase DatabasePath

DBVLookUp2 = ObjAccess.Application.DLookup("[Description]", "Device Signals", [Device Signal])


'Housekeeping
ObjAccess.CloseCurrentDatabase
ObjAccess.Quit
Set ObjAccess = Nothing
Exit Function

err_DBVLookUp2:
MsgBox Err.Description, vbCritical, Err.Number
Resume Next
End Function
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
please give this a try...

Cell B2=DBVLookUp2("[Description]","[Device Signals]","[Device Signal] = '" & B1 & "' ")
Cell B1 = AAT

Code:
Public Function DBVLookUp2(TableName As String, _ 
Criteria As String, _ 
ReturnField As String) As Variant 

'Make sure to reference the Microsoft Access 9.0 Object Library 
Dim ObjAccess As Object 
Const DatabasePath As String = "D:\Visual Engineering Tools\GE Acronyms.mdb" 

On Error GoTo err_DBVLookUp2 

'Open Access Database 
Set ObjAccess = New Access.Application 
ObjAccess.OpenCurrentDatabase DatabasePath 

DBVLookUp2 = ObjAccess.Application.DLookup(ReturnField, TableName, Criteria) 


'Housekeeping 
ObjAccess.CloseCurrentDatabase 
ObjAccess.Quit 
Set ObjAccess = Nothing 
Exit Function 

err_DBVLookUp2: 
MsgBox Err.Description, vbCritical, Err.Number 
Resume Next 
End Function

EDIT: I have edited this post about a dozen times now... sorry to confuse

hth,
Giacomo
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
RE: A breakthru

Giacomo
When pasted into the cell I get an error "Name not Valid" it seems it can complete the code up to the first single quote.

:oops:

Romefucan
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
Re: RE: A breakthru

Giacomo
When pasted into the cell I get an error "Name not Valid" it seems it can complete the code up to the first single quote.

:oops:

Romefucan

I edited the code a bunch of times due to my own impatience... I think that is there now should be good.... fingers crossed
 

Forum statistics

Threads
1,148,176
Messages
5,745,191
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top