Querying an Access database using the values in a range in excel.

[whult]

Querying an Access database using the values in a range in excel.

See i have some values in say column A of sheet1. I have to use these values to Query an Access database. I am trying to connect to the database using ADODB.

Another thing is that, I tried to use the SQL IN command to query the database. But that ended in vain.

can anyone help me out

 

[xenou]

It's a lot your asking but to try to get to the issue of the values in Excel directly, it depends on what kind of data/values they are.

It's possible you need to create a subquery for the IN clause:
SELECT W, X, Y FROM ACCESS_TABLE
WHERE X IN (SELECT Z FROM EXCEL_TABLE)

If you're using ADO you could possible construct the query string with the values as literals:

Dim x(2) As String
Dim myString As String
Dim mySQL As String
x(0) = Range("A1").Value
x(1) = Range("A2").Value
x(2) = Range("A3").Value
[COLOR="RoyalBlue"]myString [/COLOR]= "(" & x(0) & "," & x(1) & "," & x(2) & ")"
MySQL = "SELECT W, X, Y FROM ACCESS_TABLE WHERE X IN " & [COLOR="royalblue"]myString[/COLOR]

 

[SydneyGeek]

A variation on Xenou's approach...

Sub Test()

    Dim arValues()
    Dim x As String
    Dim myString As String
    Dim mySQL As String
    
    arValues = WorksheetFunction.Transpose(Range("A:A").SpecialCells(xlCellTypeConstants))
    
    'comment out whichever does not apply
    
    'for text values:
    x = Join(arValues, "','")
    myString = "('" & x & "')"
    
    'for numbers:
    x = Join(arValues, ",")
    myString = "(" & x & ")"
    
    
    mySQL = "SELECT W, X, Y FROM ACCESS_TABLE WHERE X IN " & myString
    
    'create the connection here, run the query
End Sub

You may also find this page helpful.

Denis