MS Query with Range of Parameters repost from thread68-1634657 2

[sterlecki]

What is the best way to use MS Query to query against an Access Table to return the data from a range of cells as a parameter for a field in the query.

Example: I would like each of the values for say sheet(1).Range(a1:a10) to be used as a parameter on the Access table, return the row of data and then move to the next cell(a2) as the next parameter and return the results to the next row down on the sheet with the query results.

When I select a range of cells at the prompt it says I can only select a single cell as a parameter. I'm looking for something like and In statement or a For Each type of set up.

I am just learning about MS Query so I'm looking for some helpful suggestions.


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!




SkipVought (Programmer) 17 Jan 11 20:09


Hi,

You cannot use the PARAMETER feature of MS Query is you have a LIST, as the SQL statement is different in these instances, ie

CODE
Where FieldName = ?
CODE
vs
Where FieldName IN (YourList)
It can only be done with VBA (macro) code.

If your list is STATIC, U can give you a simple function that produces a list that you can PASTE into your query SQL code ONE TIME.

However, it appears that you want to provide a dynamic list to your query. If that's the case, you'll need a procedure to run whenever your list changes. In that event, here's what you need to do.

TURN ON your macro recorder.

EDIT your query thru to the MS Query grid

File > Return data to Excel from MS Query.

Turn off your macro recorder.

COPY your recorded code and post your question and recorded code in forum707: VBA Visual Basic for Applications (Microsoft).
Skip,

I created a new macro as Skip suggested

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Destination:=Range("$A$1")) _
        .QueryTable
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
        .Refresh BackgroundQuery:=False
    End With
End Sub

But I'm not sure how to make it loop through a range of parameters.

 

[SkipVought]

That is NOT what I asked you to do.

You ADDDED NEW Querytable.

I asked you to EDIT the existing querytable!

Please macro record Editing the existing querytable and returning the data to Excel.

Please post that code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sterlecki]

Sorry Skip took me awhile to catch up.

Sub Macro7()
'
' Macro7 Macro
'

'
    With ActiveWorkbook.Connections("Query from MS Access Database").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT data_WellHeader.WaterDatum, data_WellHeader.WellName, data_WellHeader.WellNum" & Chr(13) & "" & Chr(10) & "FROM data_WellHeader data_Wel" _
        , "lHeader" & Chr(13) & "" & Chr(10) & "WHERE (data_WellHeader.WaterDatum=?)")
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Steven Terlecki\My Documents\UpdatedQuestarGeologicPrognosis.mdb;DefaultDi" _
        ), Array( _
        "r=C:\Documents and Settings\Steven Terlecki\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query from MS Access Database")
        .Name = "Query from MS Access Database"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Query from MS Access Database").Refresh
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Destination:=Range("$A$1")) _
        .QueryTable
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_ExternalData_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Hope this is what you were looking for.

 

[SkipVought]

This solution assumes that your list is a Named Range, named ListName. Paste this in a module. Run Macro7.

Sub Macro7()
    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  data_WellHeader.WaterDatum"
    sSQL = sSQL & ", data_WellHeader.WellName"
    sSQL = sSQL & ", data_WellHeader.WellNum"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM data_WellHeader data_WellHeader"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE data_WellHeader.WaterDatum IN (" & MakeList([ListName]) & ")"
'
    With ActiveWorkbook.Connections("Query from MS Access Database").ODBCConnection
        .CommandText = sSQL
        .Connection = "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Steven Terlecki\My Documents\UpdatedQuestarGeologicPrognosis.mdb;DefaultDir=C:\Documents and Settings\Steven Terlecki\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Function MakeList(rng As Range, Optional QUO As String = "'", Optional COM As String = ",") As String
'SkipVought
'returns an APSTROPHY delimited list separated by COMMA
    Dim r As Range
    
    For Each r In rng.SpecialCells(xlCellTypeVisible)
        MakeList = MakeList & QUO & r.Value & QUO & COM
    Next
    MakeList = Left(MakeList, Len(MakeList) - Len(COM))
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sterlecki]

Got an Error message:

Compile error:
Wrong number of arguments or invalid property assignment

Cursor goes to .Refresh BackgroundQuery:=False

Yellow highlight is on Sub Macro7() at the top

 

[PHV]

.Refresh [!]'[/!]BackgroundQuery:=False (NO parameter for the ODBCConnection.Refresh method ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Yes, commenting out that last little bit did the trick.
works like a charm now.

A star to both of you.