• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

how to test parameter passing from excel-access

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

1 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( _
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.
PeterBaileyUkAuthor Commented:
thank you for such detailed response

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now