Getting a value from a MS Access Database

Hello mate,

This function may help you. First of all, here's how to use it:-

Assume you have a table in Access that looks like this:-

You could use the function DBVlookup like this:-

Here is the code which you should paste into a standard module. You'll also need to change the DatabasePath constant to the path and name of your database.

Let me know how you get on,

Dan<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> adoCN<SPAN style="color:#00007F">As</SPAN> ADODB.Connection<SPAN style="color:#00007F">Dim</SPAN> strSQL<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Const</SPAN> DatabasePath<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN> = "C:\Program Files\foodmart 2000.mdb"<SPAN style="color:#007F00">'Function argument descriptions</SPAN><SPAN style="color:#007F00">'LookupFieldName - the field you wish to search</SPAN><SPAN style="color:#007F00">'LookupValue - the value in LookupFieldName you're searching for</SPAN><SPAN style="color:#007F00">'ReturnField - the matching field containing the value you wish to return</SPAN><SPAN style="color:#00007F">Public</SPAN><SPAN style="color:#00007F">Function</SPAN> DBVLookUp(TableName<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
                          LookUpFieldName<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
                          LookupValue<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
                          ReturnField<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> adoRS<SPAN style="color:#00007F">As</SPAN> ADODB.Recordset
    <SPAN style="color:#00007F">If</SPAN> adoCN<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN> SetUpConnection

    <SPAN style="color:#00007F">Set</SPAN> adoRS =<SPAN style="color:#00007F">New</SPAN> ADODB.Recordset
    strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
             " FROM " & TableName & _
             " WHERE " & LookUpFieldName & "=" & LookupValue & ";"
            <SPAN style="color:#007F00">' If lookup value is a number then remove the two '</SPAN>
    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
    <SPAN style="color:#00007F">If</SPAN> adoRS.BOF And adoRS.EOF<SPAN style="color:#00007F">Then</SPAN>
        DBVLookUp = "Value not Found"
    <SPAN style="color:#00007F">Else</SPAN>
        DBVLookUp = adoRS.Fields(ReturnField).Value
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    adoRS.Close<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN><SPAN style="color:#00007F">Sub</SPAN> SetUpConnection()
    <SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> ErrHandler
    <SPAN style="color:#00007F">Set</SPAN> adoCN =<SPAN style="color:#00007F">New</SPAN> Connection
    adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  <SPAN style="color:#007F00">'Change to 3.51 for Access 97</SPAN>
    adoCN.ConnectionString = DatabasePath
    adoCN.Open
    <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
ErrHandler:
    MsgBox Err.Description, vbExclamation, "An error occurred"<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Edit (JPG): Modified the posted code so it would copy/show better.

Hello,

Thankyou for your reply. I am having some trouble getting it to work though. When I run my code I get an error saying:

'User defined type not defined'

and the line 'Dim adoCN As ADODB.Connection' is highlighted.

I think I am doing something wrong here but can't quite see what it is

Are you able to help?

Many Thanks

Cam

Oops, my mistake. You need to choose Tools, References in the Visual Basic editor and select Microsoft ActiveX Data 2.x Library. The x will depend on how up-to-date your software is - choose the highest one.

Hooray!!!!

It works perfectly!!

Thankyou so much for your help. This is something I have been trying to do for a while now.

Now my little program is complete. Oh, one more thing quickly:

If I share this program with someone else, do they need to change their 'references' settings in VBA too?

Thankyou once again.

Cam

Without trying to step on Dk's toes... no, they won't have to, unless they don't have the ADO reference installed on their computer.

How to run a public function in Excel

I try to work with a function build in VBA, but it still giving a !Name Error.

=dbvlookup("Postal";"postal_code";A2;"Lattitude").

I put the code in the excelsheet VBA, I am use with VBA in access but not in Excel. So is there any option I need to click on to make this work. The code just don't run.

Thanks.

You need to put the code in a general module. (Insert>Module)

I would like to use dk code but I can’t get it to work with an alpha numeric Account Number “ab123”. Works with numeric only….”123”

Any thoughts?

Thanks!!

Mark

You probably need to use single quotes when working with text.