how to test parameter passing from excel-access

Posted on 2012-08-11
Last Modified: 2012-08-12
I have a number of subs one for each vehicle category per client (8 clients in all)

The results i am getting do not correspond to the query result.

If i run the query in access i enter the same parameters and the query gives the correct result.

If i run the sub for that category and pass the same parameters I get a different result.

How do I test this?

The only difference between each sub is the value of parameters and the cell destination.

Sub GetKeeTotalNewCodesMatchedCarsSMMT2012()
    Set qdf = db.QueryDefs("Query2")
    Set RstCurrentYearMonth = db.OpenRecordset("SELECT Max(CWSMMTBuilds.BuildYearMonth) AS MaxOfBuildYearMonth FROM CWSMMTBuilds;", dbReadOnly)
    'get current data month
    With RstCurrentYearMonth
    CurrentDataMonth = .Fields("MaxofBuildYearMonth").Value
    End With
    datMonth = #1/1/2012#
    Do While datMonth <= CurrentDataMonth
        qdf.Parameters("[Yes=CW Codes/No=SMMT Codes]").Value = False
        qdf.Parameters("[Enter Cars, Bikes, Lcv, Others]").Value = "Cars"
          qdf.Parameters("[Yes=Matched Abi/No=No Match Abi]").Value = Null
        qdf.Parameters("[Yes=Matched Cap/No=No Match Cap]").Value = Null
        qdf.Parameters("[Yes=Matched Glass/No=No Match Glass]").Value = Null
        qdf.Parameters("[Yes=Matched Adl/No=No Match Adl]").Value = False
        qdf.Parameters("[Yes=Matched Insecom/No=No Match Insecom]").Value = Null
        qdf.Parameters("[Yes=Matched Techdoc/No=No Match Techdoc]").Value = Null
        qdf.Parameters("[Yes=Matched Halfords/No=No Match Halfords]").Value = Null
        qdf.Parameters("[Yes=Matched Kee/No=No Match Kee]").Value = True
        qdf.Parameters("[Yes=Matched Vivid/No=No Match Vivid]").Value = Null
         qdf.Parameters("BYM") = DateSerial(Format(datMonth, "yyyy"), Format(datMonth, "mm"), 1)

          With wks.Cells(84, Format(datMonth, "mm") + 1)

            .Value = "...."
            .Value = qdf.OpenRecordset!Nb
        End With
        datMonth = DateAdd("m", 1, datMonth)

Set RstCurrentYearMonth = Nothing
'Set db = Nothing
Set qdf.Parameters = Nothing

End Sub

Open in new window

Question by:PeterBaileyUk
    LVL 5

    Accepted Solution

    This is very much a suckage job to do but I hope this might help.

    You would be best to use a database query created from within Excel rather
    than a link to the Access database query's results, since I don't know of a
    way to pass parameters to Access directly.

    I would record a macro while using Data / Import External Data / New Database
    Query to extract some data from the database (doesn't matter what).

    Then stop the recorder.

    In Access, use View / SQL in query design mode to see the SQL text of your
    query and replace any newlines within it by spaces.

    In Excel, Tools / Macro / Macros / Macro1 (or the newly recorded macro's
    name) / Edit

    The recorded code will look horrible, something like

    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=MS Access
    Database;DBQ=C:\SomeDir\MyDB.mdb;DefaultDir=C:\Som eDir;DriverId=25;FIL=MS
    Acce" _
    ), Array("ss;MaxBufferSize=2048;PageTimeout=5;")),
    .CommandText = Array( _
    "SELECT zqParamtest.IdentNr, Sum(zqParamtest.StoredVolume) AS 'Sum of
    StoredVolume', Sum(zqParamtest.ConditionedVolume) AS 'Sum of
    ConditionedVolume', Sum(zqParamtest.PackagedVolume) AS 'Sum of Package" _
    , _
    "dVolume'" & Chr(13) & "" & Chr(10) & "FROM
    `C:\SomeDir\MyDB.mdb`.zqParamtest zqParamtest" & Chr(13) & "" & Chr(10) &
    "GROUP BY zqParamtest.IdentNr" _
    .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

    Delete the lines starting with '.CommandText = ' down to the line above the
    one beginning '.Name = '. Replace them with
    .CommandText = ""
    Then paste your Access query text between the ""

    Go to a new worksheet and rerun the macro.
    It should ask you for the parameters and then execute the query, returning
    the data into the worksheet.

    Then, you can refresh the query specifying different parameters by Data /
    Refresh Data (with the cursor in the query results).

    Using Data / Import External Data / Parameters you can specify that you want
    the parameters to be taken from worksheet cells if you wish.

    Author Closing Comment

    thank you for such detailed response

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now