Join Excel Table and Access Database

[gramit]

Hi,

I have an Access Database that contains vendor partcode descriptions and list prices. I am trying to create an Excel based tool where employees can paste an inventory, hit a button and it will query the database for the corresponding descriptions and list prices.

I originally started using ADO and creating a string that concatenated all partcodes into a variable called partList and would then query the DB using the following command which works fine for small samples but becomes slow for large inventories as the string the IN command is checking against becomes very large. I have added an interim step to apply advancedfilers and remove any duplicate entries for efficiency.

pDB.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" & _
        "D:\Documents and Settings\gbeuken\My Documents\Grants Documents\Pricing Tool\Price List\Cisco Price List.accdb" & ";"
sql = "select * from [Consolidated Price List] where PARTCODE in (" & partList & ")"
Set pRS = pDB.Execute(sql)

In SQL I would normally use JOIN for large comparisons like this so have tried to get the same resul using ADO in VBA and have the following code

Sub TestQueryDB()

Application.ScreenUpdating = False

Dim pDB As ADODB.Connection
Dim pRS As ADODB.Recordset
Set pDB = New ADODB.Connection
Set pRS = New ADODB.Recordset

Dim sql As String, partList As String, i As Integer

Call CreateUniqueList   'Creates a list of unique entries
 
pDB.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" & _
        "D:\Documents and Settings\gbeuken\My Documents\Grants Documents\Pricing Tool\Price List\Cisco Price List.accdb" & ";"

sql = "SELECT * FROM [Consolidated Price List] INNER JOIN ""D:\TestFile.xlsm"".TESTRNG ON " & _
        "[Consolidated Price List].PartCode = ""D:\TestFile.xlsm"".TESTRNG.PartCode"
Set pRS = pDB.Execute(sql)

With Matches
    .Range("A1").CurrentRegion.ClearContents
    .Range("A1") = "PartCode"
    .Range("B1") = "Description"
    .Range("C1") = "ListPrice"
    .Range("D1") = "ListYear"
    .Range("A1:D1").Font.Bold = True
    .Range("A2").CopyFromRecordset pRS
End With

pRS.Close
Set pRS = Nothing
pDB.Close
Set pDB = Nothing

End Sub

When attempting to run this I get the error message "Syntax Error in From Clause". CreateUniqueList takes the inventory and removes blank cells and duplicae entries. TestFile.xlsm is the worksheet the reduced inventory resides and TESTRNG is the name of the data I in Excel I wish to join to the Access DB, I have tried using TESTRNG as both a named range and a named table to no avail.

In short what I am asking is, is it possible to perform a JOIN operation on an Access database with an Excel table and if so where am I going wrong?

Other alternatives I have left to try if this is not possible are looping through each part code 1 at a time, leaving the IN command and uploading the table to Access, performing the join then deleting it.

Thanks a lot
Grant

 

[xmlfan]

Hi Grant,
Can't you just import the access table into an excel querytable, and do a lookup in this table?
Best regards,
- Frans

 

[gramit]

Hi Frans,

This table is considerably large and continually growing which is why I have been force to move it away from Excel and into a DB. After a days effort and researching I decided that uploading the data into a temporary table in the Access DB and running the queries from there was the best way forward.