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

 

[Norie]

evdokimos

What are you actually trying to do?

It's not really standard practice to use this sort of thing to return individual values from tables in databases.

 

[evdokimos]

Thanks Norie for the reply. Each record of the field contains 9 characters. The first 8 characters are numeric (which represent the number of the identity card of each taxpayer in the population ) and the last character is a letter ( is a check digit - created from a secret algorithm that I do not know). The first 8 character number is unique and cannot be found again in the other records. I want to create a function to enter the first 8 numeric digits and return the full number (8 numeric characters plus the check digit). If I have the full number I will use the vlookup function to return the name of the taxpayer (since my access database have two fields, the 9 character code and the name of the taxpayer).

I thank you in advanced and god bless you.

evdokimos

 

[PeterP525]

Hello,

I tried DK's code and it works...but can anyone tell me how to modify the code so I can connect to an Access 2007 database which is password protected?...Also if multiple users pull from the database simultaneously will the software crash?..Please advise. Thanks.

 

[PeterP525]

Can anyone help me?... PLEASE... I need to connect to a password protected Access 2007 database..below is my code:


Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "D:\Peter\Feb 11\Connect_ACCESSDB\TEST\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.ACE.OLEDB.12.0"
adoCN.ConnectionString = DatabasePath
adoCN.Open
adoCN.DatabasePassword = "connect123"
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

 

[evdokimos]

Hi

I 've had a similar problem and I used the following function which works fine:

' choose tools/references in visual basic editor and select microsoft activex
' data2.x library. The x depend on how up-to-date your software is -
' choose the highest one

Dim adoCN As ADODB.Connection
Dim strSQL As String

Dim DatabasePath As String
Dim AccessPassword As String

'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

On Error GoTo ErrHandler

Dim adoRS As ADODB.Recordset

If adoCN Is Nothing Then SetUpConnection
LookupValue = Chr(34) & LookupValue & Chr(34)

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()
DatabasePath = "D:\TEST\test.mdb"
AccessPassword = "123"
On Error GoTo ErrHandler
Set adoCN = New Connection
'adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
''adoCN.Provider = "Microsoft.Jet.OLEDB.12.0" 'Change to 3.51 for Access 97
'adoCN.ConnectionString = DatabasePath
adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& DatabasePath & ";" & "Jet OLEDB:Database Password=" & AccessPassword

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

End Sub

 

[PeterP525]

Code works!..Thank you very much.

 

[PeterP525]

evdokimos,

Do you know how i can program the code so I can lookup by LookupFieldName1 OR LookupFieldName2? My data has information about each individual store, property taxes, sales, etc. How can I program the code above so I can lookup by store number or store name. I am thinking i have to use some kind of IF statement...if store number(cell number A1) is null then use store name(cell number B1). Any help will be much appreciated.

 

[evdokimos]

Hi
If I understood properly, you can write a formula like :
=IF(ISBLANK(a1);DBVLookUp(.....);DBVLookUp(....))

replace ; with , if your regional requirements requires this.

Forgive me but I would look again this threat in weakend.

Regards
evdokimos

 

[PeterP525]

evdokimos,

I figured it out. I connected to my Access database through connection wizard in Excel. I brought in my table. I then used Excel's built in function IF and Vlookup.

IF(A851=0,VLOOKUP(A850,Table_Default__STOREINFO[[Store_Name]:[DEAL_TYPE]],3,FALSE),VLOOKUP(A851,Table_Default__STOREINFO[[STORENBR]:[DEAL_TYPE]],4,FALSE))

The vlookup function in the code is not flexible enough to use a string (store name) as lookup. Thanks for your help.

 

[avbanovice]

Hi, I know it's been about a year since there's been activity on this thread but I'm wondering if this code can be made into an add-in so that it can be used as a user defined function in any worksheet. I was able to get the code working but when I tried to create an add-in I get a compile error - user-defined type not defined, it's refering to adoCN As ADODB.Connection. From what I researched, it's looks like a UDF needs to begin with Function and end with End Funtion. In this case, Sub SetUpConnection() is outside of that function so it does seem to work.

Is it possible?

Hope that makes sense.

Thanks in advance for any advice!