VLOOKUP from Access DB

[ndendrinos]

Hello to all,
I have an Access database named : Vault
In Vault I have a table named : Contacts
In Contacts the first column is named : Company

I need in Excel a VLOOKUP that will read the company names from the access database.

I already have the code to link to the DB it is just with the Vlookup that I have a problem with when using "Data/Validation.

Thank you.

 

[Justinlabenne]

What code are you using to link to the Access database? What does the validation list have to do with what will be returned from the database?

 

[Richard Schollar]

One possible solution would involve a UDF which would pass a parameterized SQL statement to the database. I have used this before with a connection to an SQL Server Db - please note that this is a very slow solution and I wouldn't recommend it for more than say 10 calculations on a sheet.

Other than that, I would pull the lookup data into the excel sheet (say on spreadsheet open) and hold it as a lookup table on another sheet.

Best regards

Richard

 

[ndendrinos]

Hello Justin
Here is the code :
Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:\Documents and Settings\Nick\Desktop\Vault.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

This is how I've started .
I use in excel in cell B2: DBVLookUp"Contacts","Invoice",A2,"BilltoCompany")

So if I type in A2 an invoice number I get the Bill to Company name.

What I need is a Vlookup that will allow me to choose a company name from the Bill to Company column in the Access DB.

I am having a problem making myself clear here but I hope you will get the idea, and thank you.
Nick

 

[ndendrinos]

Hello parsnip and thanks for your reply.
I've done that already ... I've linked a sheet to the DB and use it like You suggest... but the linking on open makes me uneasy ... the customers list is over 1000 and growing.
pls give your opinion on that.
SQL I'm not familiar with at all, then again you mention the slow process and that's not an option here.
Thanks,
Nick

 

[Richard Schollar]

Nick

Your function code that you included does exactly what I suggested re a parameterized SQL query (the embedded SQL is the SELECT ... FROM ... WHERE ... bit).

When you say you want to look up a company name, could you indicate with what info you wish to specify a particular company - or do you wish to have a list of all companies, from which you can make a particular selection?

I don't think 1000 records is too great a size (even 2000) - especially if you only need one column (eg company names).

Richard

 

[ndendrinos]

Thanks for the help Richard.
If I can stay with the example I give in my reply to Justin at the bottom I explain that :
I use in excel in cell B2: DBVLookUp"Contacts","Invoice",A2,"BilltoCompany")

So if I type in A2 an invoice number I get the Bill to Company name.

What I hope for is a Lookup box from where I would choose the invoice number .... If I solve that one I think I can change things aroud and instead of an invoice number I will extract from the DB the name of the Company, address tel fax email ship address etc... and populate an invoice that way.

I already have all I need (at least I'm confident I do) what I do not have is the Data/validate part of the Lookup (I call it VLookup)

So in answer to your question : yes I want to be able to choose from a dropdown list.
Thank you again
Nick

 

[Richard Schollar]

Nick

As far as I know (and I will freely admit that my knowledge could well be very deficient in this area) there is no easy way of achieving what you want from a dynamic lookup to the database itself. I do somewhat similar things to what you have outlined at work, but I always achieve it by populating querytables on my spreadsheet on Workbook_Open and then referring to these QueryTables via dynamic named range(s). This has not been a problem for me because the number of data returned to my querytables has been relatively static (and in one instance, about 5,000 lines). Yours I can see as being more of a pain, because you want Invoice Numbers, and thus your data will be continuously growing and perhaps could get unmanageably large. If you would like to give it a try (and I think it is an easy solution if one accepts the potential limitations) then i am happy to talk you thru the steps if necessary.

Best regards

Richard

 

[ndendrinos]

Hi Richard and thanks for your reply.
Had a go at it all afternoon, thought of creating a lookup in the table inside the database, created a query on the Lookup and tried to link that to the excel sheet and you guess it failed big time.

The solution you suggest is the one I'm presently using ... and since I extract from it the Company name address etc.. and not invoice numbers I have about 1100 names right now and while I hope the list will grow I cannot see it going over 1200 to 1300 as some companies will stop dealing with me while new ones come on board.

Thank you again,
Nick

 

[Richard Schollar]

Nick

I am somewhat reassured with the approach I am using because you have basically come to the same conclusion about using the same approach with your data. I suspect that you are slightly uncomfortable with it (in the same way I am) because it is inefficient (ie we have the data existing in two places - our Db and our spreadsheet - instead of just in the one place -our Db). Should you determine a way to achieve what you want directly from the database, I would be most interested to hear it!!! I will of course let you know if I figure out some method myself!

An interesting and challenging post!

Best regards

Richard