ADO Connection to Access and filter in NamedRange 1

[neemi]

OK... so I have an ADO connection to a database and I also have a named range with a list of ID's in the same worksheet where I am trying to make an ADO connection to the database. what I want to do is only pull of the data from the database where the ID is listed in the named range. If you get what I mean.

SO for example i need an ado connection which will pull of data referencing the database and the excel named range in the current worksheet:

    Set cn = New ADODB.Connection
    cn.CommandTimeout = 1000
    cn.Open ConnectionStringtoDB

    Set rs1 = New Recordset
    rs3.Open "SELECT IDField,Field1,field2,field3" & _
             "FROM Table1" & _
             "WHERE IDField IN(" & range([ExcelNamedRange]) & ")"
[code]


Hope you understand my problem and can help.
much appreciated,
Neemi

 

[SkipVought]

Hi,

I do this often.

My most preferred method is to use MS Query and a MakeList function that produces an IN list. That could also be substituted in your SQL in your ADO query.

Optionally, you could

1. open a connction to the DB

2. loop thru the items in your list one at a time and substitute in your SQL

3. close the connection.

Here's a UDF I use to make an IN list...

Function MakeList(rng As Range) As String
'SkipVought/2005 Jun 13/817-280-5438
'--------------------------------------------------
' Access: N/A
'--------------------------------------------------[b]
':this function returns a single-quoted list that can be used, for instance _
in an IN Clause in SQL _
"WHERE [YourField] IN (" & MakeList([SomeRange]) & ")"[/b]
'--------------------------------------------------
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

Thanks. works a treat!