How to correctly quote in a Where clause? 1

[feipezi]

Hi,
My question is similar to the post: Filtering in VB report.
I tested a Excel macro that tries to run a MS Query to imort data from MS Access. It worked fine until I wanted to put a parameter in the Sub. The error that I have is 'Runtime error 13: type mismatch'. When debugging, it highlights ".CommandText...)" part of the code. It must be a quoting problem.
Thanks in advance!

Sub rawquery(mkt As String)
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\jqz\cadi\aetna\aetna_state_May08.mdb;DefaultDir=C:\jqz\cadi\aetna;DriverId=25;FIL=MS Acce" _
), Array("ss;MaxBufferSize=4096;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT rebt_period, state, formularydesign, PLAN_NAME, mkt, product, mail_retail_cd, med_ind, " & _
"mb_flag, rx_count, total_prod_units, wac_sales, rebates, mkt_rx, mkt_total_prod_units, mkt_shr, unit_shr " & _
"FROM `C:\jqz\cadi\aetna\aetna_state_May08`.aetna_state " & _
"where aetna_state.mkt=" & mkt & """")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Sub runit()
rawquery "ARB"
End Sub

 

[SkipVought]

Hi,

     "where aetna_state.mkt=[b][red]'[/red][/b]" & mkt & "[b][red]'[/red][/b]")

Is there a reason that you are adding the quertable to you sheet via code?

As a matter of course, I'll manually add a QT, and THEN use code to change the CommandText and/or Connection parameter(s) and just Refresh.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

As a matter of course, I'll manually add a QT, and THEN use code to change the CommandText and/or Connection parameter(s) and just Refresh, like this...

Sub rawquery(mkt As String)
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = "C:\jqz\cadi\aetna"
    
    sDB = "aetna_state_May08"
    
    sConn = "ODBC;DSN=MS Access Database;" & _
            "DBQ=" & sPath & "\" & sDB & ".mdb;" & _
            "DefaultDir=" & sPath & ";" & _
            "DriverId=25;FIL=MS Access;MaxBufferSize=4096;PageTimeout=5;"
    
    sSQL = "SELECT rebt_period, state, formularydesign, PLAN_NAME, mkt, product" & _
            ", mail_retail_cd, med_ind, mb_flag, rx_count, total_prod_units, wac_sales" & _
            ", rebates, mkt_rx, mkt_total_prod_units, mkt_shr, unit_shr " & _
            "FROM `" & sPath & "\" & sDB & "`.aetna_state " & _
            "where aetna_state.mkt='" & mkt & "'"
    
    With ActiveSheet.QueryTables
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

It also wonders me why you have a database named like DATA? Seems like there might be Jan08, Feb08, Mar08 etc database. Yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,
Thanks for the quick return and I like your code better.
But after I cut & paste your code and run it. I got run-time error '438' (object doesn't support this property or method). Debug shows the problem is with 'sConn'.

I guess I have to reset the Reference Library. BTW, which library is the right one, ADO or DAO or both?

This is only a test and there is no need to give a name like 'May08' because the report can be re-freshable based on the same database, not monthly data.

I was planning to put the data (from a query table) on a Worksheet and then use Advanced filter and graphs to show the performance of some markets and other economic parameters. It will have about 160 columns.

Thanks again.

 

[SkipVought]

I am so sorry...

    With ActiveSheet.QueryTables[b](1)[/b]
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

It worked like a charm.
Thanks a lot! Skip, you're the man.
John Z.