Excel & Access ADO: excel as recordset?

[ossuary]

Hello,

I have an access database set up with an Excel frontend, and I regularly use an ADO connection to send data back and forth between the two. For the current task I'm working on, I want to filter the data I am pulling from Access based on a list of account numbers I will have listed in the Excel program (the list will change each time).

I can't use the list of account numbers as a WHERE clause, as there may be as many as 500 account numbers at a time. For the same reason, I don't want to query the database once for each account number. Is there a way I can use the list of account numbers in Excel as though it were a recordset or table in Access, so that I can do some kind of join to filter my Access results?

If so, how would I accomplish this? What would the syntax look like?

Thanks in advance for any help.

 

[xenou]

It is possible to query Excel but its not recommended to do this in an open workbook (including, of course, the one you are using at the moment).

I wouldn't rule out an IN() clause with 500 elements. Try it first and see if performance is adequate. I've used IN clauses with over 100 elements and there was no discernable impact whatsoever (I think this was in SQL Server, though, not in Access).

If that fails you could push the accounts out to Excel or a text file and use it as a virtual table to join on, or drop the accounts into a temporary table in Access. Perhaps someone else will also have more ideas.

But try the monster IN() clause and maybe it will work just fine. T'would be interesting to find out too.

Regards,
ξ

 

[SydneyGeek]

I've used this technique to build a huge IN construct before:

    arRegion = WorksheetFunction.Transpose(Range("PickRegion"))
    strFilter = "IN('" & Join(arRegion, "','") & "')"
    sSQL = "SELECT * FROM tblPopulation WHERE Region " & strFilter

Define a range in the worksheet, containing all the items you want to filter by. It should be a dynamic range; in the example above it is called PickRegion.

The above will:

• Load the items into an array
• Build a string with all the items, with the correct syntax for text items
• Filter the Access table.

Obviously you will need to declare the variables...

Denis

 

[ossuary]

Thanks for the feedback, guys. I'll try the IN statement and see if it works.

I had thought of using a temp table to add the records to and then dumping it when I was done, but unfortunately the database in question is not "mine" so I cannot add a table to it (quel domage).

I'll post again later today and let you know the results. For science!

 

[ossuary]

Sydney,

I'm getting a syntax error on the WorksheetFunction line... can you please clarify exactly how I'm supposed to be making a dynamic range? I defined a range variable "rng" and set it to the current list of account numbers, but it's not working.

I'm not sure what to do to correct this.

Thanks.

 

[xenou]

If you've defined the range in the vba procedure (rng) then try:

    arRegion = WorksheetFunction.Transpose(rng)
    strFilter = "IN('" & Join(arRegion, "','") & "')"
    sSQL = "SELECT * FROM tblPopulation WHERE Region " & strFilter

Dynamic ranges are created in the workbook without vba:
http://www.contextures.com/xlnames01.html

I think that arRegion is a variant (array), not a range, in this code.

 

[ossuary]

xenou,

Thanks for the tip. I'm still getting an error on that line, even though I've changed arRegion to be a variant instead of a range. I've also tried manually naming the range for my test run just to make sure it's not a problem with the dynamic range, and I still get the same error.

run-time error 1004, method 'range' of object '_worksheet' failed

the named range is not on the same worksheet as I am running the code on - will that matter? I am pointing the range in the transpose section to the correct sheet.

 

[xenou]

You are making some error setting the range, but I don't know what it is. You might need to post your code. It may be that you are not fully qualifying the range when you set it. In the following two statements, the first is not fully qualified, but the second is (actually, strictly speaking, fully qualified would have a workbook qualifier too).
-------------------------------------------
Set rng = Range("A1")
Set rng = Worksheets("Sheet1").Range("A1")
-------------------------------------------

Here's test code (Excel VBA) to demonstrate how this works (without using a named range). It creates a new workbook for the test data:

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] Workbook
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] arRegion, i
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] sSQL [COLOR="Navy"]As[/COLOR] String, strFilter [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'//Create test data[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = Workbooks.Add
    [COLOR="Navy"]Set[/COLOR] ws = wb.Worksheets(1)
    [COLOR="Navy"]Set[/COLOR] rng = ws.Range("A1:A7")
    rng.Cells(1).Value = "WA"
    rng.Cells(2).Value = "NT"
    rng.Cells(3).Value = "VIC"
    rng.Cells(4).Value = "NSW"
    rng.Cells(5).Value = "QLD"
    rng.Cells(6).Value = "TAS"
    rng.Cells(7).Value = "SA"
    
    [COLOR="SeaGreen"]'//Create SQL String[/COLOR]
    arRegion = WorksheetFunction.Transpose(rng)
    strFilter = "IN('" & Join(arRegion, "','") & "')"
    sSQL = "SELECT * FROM tblPopulation WHERE Region " & strFilter
    
    [COLOR="SeaGreen"]'//View result[/COLOR]
    MsgBox "SQL String: " & vbNewLine & sSQL
    rng.Cells(7).Offset(1).Value = sSQL

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Norie]

What range does the named range refer to?

Using Transpose and Join would be an ideal solution but there's nothing stopping you constructing the IN clause using a simple loop.

By the way, are the account numbers actually numbers?

 

[SydneyGeek]

See if this helps:

Sub Ranges()
    Dim sSQL As String
    Dim strFilter As String
    Dim Rng As Range
    Dim Nm As Name
    Dim arRegion()
    
    On Error Resume Next
    ActiveWorkbook.Names("Items").Delete
    On Error GoTo 0
    
    Set Rng = Sheets("Sheet1").Range("A2").Resize(Sheets("Sheet1").Range("A65536").End(xlUp).Row - 1, 1)
    ActiveWorkbook.Names.Add Name:="Items", RefersTo:=Rng
    Set Rng = Nothing
    
    arRegion = WorksheetFunction.Transpose(Range("Items"))
    strFilter = "IN('" & Join(arRegion, "','") & "')"
    Debug.Print strFilter
    sSQL = "SELECT * FROM tblPopulation WHERE Region " & strFilter
    Debug.Print sSQL
End Sub

Denis