Vlookup call to access

[VXCyndre]

I've created a workbook I'm currently using at work to monitor traffic movements which uses a number of drop downs and vlookups to reference a database worksheet containing all my customer data, haulier data etc.

The database is starting to become very large and is a pain to constantly have to remove everyone from the shared spreadsheet to add new data as it comes in.

I've been looking at whether I can create access tables for each of the database tables and then use the same vlookups in excel to call the data I want out of the respective tables but I'm struggling to work out what I need to learn and how to do so.

E.g.

In a cell on the worksheet region 1 I enter the customers number (K1) at present. Then I use a vlookup function to return the customers name, km's and cost from an array on a separate worksheet (csdata).
So for the customer name in cell L1 i have vlookup function:

=VLOOKUP(K1,csdata!$A$2:$B$4000,2,FALSE)

Same for each respective cell on the sheet for km's, cost etc.

I also do the same for loading point's to return driver list for that point.

E.g.

H7 is a list that references csdata (=plant) and a list of loading points. I then have a list in F7 with the function:

=OFFSET(DTerminal,MATCH(H7,DTerminal,0)-1,-3,COUNTIF(DTerminal,H7),1)

This to return a list of drivers specific to the terminal selected in H7

What I want to do is place each of these tables I'm referencing currently in the worksheet csdata into access databases and then make the same function calls.

I'd also then like to be able to enter new data directly into the access databases via a script?

What do I need to do and can anyone help?

 

[VXCyndre]

Quick follow up, I've managed to work out how to use a vlookup with an access database using a brilliant module on this forum.
I copied the code:

Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "Path"
'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 then with the excel command DBVLOOKUP("DB","Lookkupname",Lookupvalue,"Returnvalue")

What I now need to work out is can this also be done for the following excel operators I'm using:

=OFFSET(DTerminal,MATCH(H7,DTerminal,0)-1,-3,COUNTIF(DTerminal,H7),1)

DTerminal refers to the name of the current list I am using and what I need to do is change it so it looks up the value in H7 (Terminal name) then returns all the driver names with the matching terminal name.

This list I want to refer to is a separate table in a new access db.

Any ideas?

 

[VXCyndre]

I tried creating an msquery with

SELECT DIndex.`Driver Name`, DIndex.TBase
FROM DIndex DIndex
WHERE (DIndex.TBase='H7')
ORDER BY DIndex.`Driver Name`

However this doesn't return a drop down list and I can not see how you would reference the query in validation.

 

[VXCyndre]

Managed to come close to the result I want but still can't make the final connection.

Used an msquery on Tbase and Driver sorting by driver name. I set the parameter to [User defined Tbase] with the source as the drop list in H7 (Which has a list of the terminals).

When you select from the drop list this is returning all the driver names for that terminal in the cell F7 but I can't do it as a drop list that can be selected from rather than one huge list.
It's also returning the terminal names as well in column G which I don't want.

Is it possible to 1. Make the returned values a drop list and 2. Stop the terminl names being returned (I did remove TBase from the msquery but I then could not alter the parameters).

 

[VXCyndre]

So far no luck it seems, tried on a few forums and no responses on any so not sure if what I want to do is even possible or if I'm explaining it badly.

Would the solution to my problem be to create a vb module making a query on my table to return all driver names with the matching field value selected from an excel validation list and then return the range into a combobox?