0

I need to query an XLS "database" from within an Excel Workbook via ADO.

I am using the following:

...code

objRecordset.Open "SELECT * FROM [MY_TABLE$]  WHERE Code_ID = "  & the_ID & ", objConnection, adOpenStatic, adLockOptimistic, adCmdText

...code

It runs well if I am only searching for an Id (the_ID), eg 1234

But what I need is to search for various the_ID's at the same time....

So for example any matches of ID's 1234, 1225, 6225, 5656 should return on the query.

So more or less an array of Id's.

Any help is appreciated...

matzone
  • 5,703
  • 3
  • 17
  • 20
user1135218
  • 393
  • 1
  • 8
  • 26

1 Answers1

2

You can join the array of ids using Join then use IN in the sql, e.g.

Dim ids(3) As String

ids(0) = "1234"
ids(1) = "1225"
ids(2) = "6225"

Dim sql As String
sql = "SELECT * FROM [MY_TABLE$] WHERE Code_ID IN (" & Join(ids, ",") & ")"

If you are getting your ids from a range then this answer will be of interest to you.

Note

Since you are only querying your own spreadsheet I have assumed security may not be a major concern, however I would normally recommend using parameterised queries, this would either require a known number of ids, or you would have to generate the sql on the fly, something like:

WHERE Code_id IN (@Param1, @Param2, @Param3, @Param4)

It should not be too hard to make your sql like this then add your ids as parameters to the recordset. I haven't used VBA in a long time though, so I can't quite recall the right way to add the parameters (or if it is even possible). If I remember I will update the answer.

Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123