How to run parameterized query from VBA. Parameters sourced from recordset

Question

I have a form where a user selects a vendor's name from a combobox, whose catalog file is to be imported. The combobox selection then drives a query to create a one-record recordset (rsProfile) containing several profile variables queried from a table of all vendor profiles. These variables are then used in a series of different queries to reformat, translate and normalize the vendor's uniquely structured files to a standardized format that can be imported into our system.

I am frustrated that I can't figure out how to build my stored queries that will use one or more parameters that are automatically populated from the profile recordset.

Here is my rsProfile harvesting code. It works. Note that intVdrProfileID is a global variable set and used in other places.

Private Sub btn_Process_Click()

Dim ws As Workspace
Dim db, dbBkp As DAO.Database
Dim qdf As DAO.QueryDef
Dim rsProfile, rsSubscrip As Recordset
Dim strSQL As String
Dim strBkpDBName As String
Dim strBkpDBFullName As String

strBkpDBName = Left(strVdrImportFileName, InStr(strVdrImportFileName, ".") - 1) & "BkpDB.mdb"
strBkpDBFullName = strBkpFilePath & "\" & strBkpDBName

Set db = CurrentDb
Set ws = DBEngine.Workspaces(0)

MsgBox ("Vendor Profile ID = " & intVdrProfileID & vbCrLf & vbCrLf & "Backup file path: " & strBkpFilePath)

' Harvest Vendor Profile fields used in this sub
strSQL = "SELECT VendorID, Div, VPNPrefix, ImportTemplate, " & _
                 "VenSrcID, VenClaID, ProTyp, ProSeq, ProOrdPkg, ProOrdPkgTyp, JdeSRP4Code, " & _
                 "PriceMeth, " & _
                 "ProCost1Frml, ProCost2Frml, " & _
                 "ProAmt1Frml, ProAmt2Frml, ProAmt3Frml, ProAmt4Frml, ProAmt5Frml " & _
         "FROM tZ100_VendorProfiles " & _
         "WHERE VendorID = " & intVdrProfileID & ";"

Set qdf = db.QueryDefs("qZ140_GetProfileProcessParms")
qdf.SQL = strSQL
Set rsProfile = qdf.OpenRecordset(dbOpenSnapshot)
DoCmd.OpenQuery "qZ140_GetProfileProcessParms"
' MsgBox (qdf.SQL)

I have used QueryDefs to rewrite stored queries at runtime, and although it works, it is quite cumbersome and does not work for everything.

I was hoping for something like the sample below as a stored query using DLookups. I can get this to work in VBA, but I can't get anything to work with stored queries. I am open to other suggestions.

Stored Query "qP0060c_DirectImportTape":

SELECT 
    DLookUp("[VPNPrefix]","rsProfile","[VendorID]=" & intVdrProfileID) & [PartNo] AS VenPrtId,
    Description AS Des,
    DLookup("[Jobber]","rsProfile",[VendorID=" & intVdrProfileID) AS Amt1,
INTO tP006_DirectImportTape
FROM tJ000_VendorFileIn;

ADDENDUM: Let me adjust the problem to make it a bit more complex. I have a collection of about 40 queries each of which use a different collection of parameters (or none). I also have a table containing the particular set of queries that each vendor 'subscribes' to. The goal is to have a database where a non-coding user can add new vendor profiles and create/modify the particular set of queries which would be run against that vendor file. I have almost 100 vendors so far, so coding every vendor seperately is not practical. Each vendor file will be subjected to an average of 14 different update queries.

Simplified Example: Vendor1 file needs to be processed with queries 1, 2 and 5. Vendor2 file might need only update queries 2 and 4. The parameters for these queries might be as follows:

query1 (parm1) query2 (parm1, parm4, parm8, parm11) query4 (parm5, parm6, parm7, parm8, parm9, parm10, parm11) query5 () -no parms required

This is the core query processing that loops through only the queries relevant to the current vendor file. rsSubscrip is the recordset (queried from a master table) containing this filtered list of queries.

' Run all subscribed queries
MsgBox "Ready to process query subscription list."
With rsSubscrip
    Do While Not .EOF
        db.Execute !QueryName, dbFailOnError
        .MoveNext
    Loop
    .Close
End With

So once you've identified which queries to run for a particular vendor, you must supply values for any parameters which those queries require. The parameter values for the current vendor are available in the `rsProfile` recordset, right? How can you match one `rsProfile` value to a query parameter? — HansUp, Jun 26 '15 at 14:13
Your conclusion is correct. Your question is also quite the right question. I am looking to code DLookup into the stored queries for each field requiring a parameter, thereby removing the necessity of having parameters. Upon further reflection, If I had only one parameter identifying the unique vendorID, perhaps the DLookups would work, regardless of the actual number of profile variables needed in the query? — Thomas Kerber, Jun 26 '15 at 15:48
So then if your queries include `DLookup` expressions as substitutes for query parameters, the revised queries would all include no more than one actual parameter (for the vendor). Then you wouldn't need the `rsProfile` recordset. And the remaining challenge is then to determine whether or not each query in the vendor's query set requires you to supply a value for the vendor parameter. Is that all correct? (I fear I'm getting lost in the details here.) — HansUp, Jun 26 '15 at 15:56
You've grasped the idea perfectly. I think your right in suggesting that the rsProfile recordset is redundant and unnecessary. I was unsuccessfully attempting to use it as the lookup source, but Access didn't like it. If I reference an actual table (tblVendorProfile) for the lookup source... it might not balk so much. — Thomas Kerber, Jun 26 '15 at 16:16
I think either way could work. Decide which approach seems easier for you (and your colleagues?) ... easier to create initially, and later comprehend/extend/troubleshoot/maintain. — HansUp, Jun 26 '15 at 16:31

score 0 · Answer 1 · answered Jun 26 '15 at 08:07

0

You can set the parameters of a predefined query using the syntax;

Set qdf = CurrentDB.QueryDefs(QueryName)
qdf.Parameters(ParameterName) = MyValue

To add parameters to the query, add the following before the SELECT statement in the sql

PARAMETERS [ParameterOne] DataType, [ParameterTwo] DataType;
SELECT * FROM tblTest;

answered Jun 26 '15 at 08:07

jhTuppeny

820
1
11
16

I have added some other functional criteria to the post. – Thomas Kerber Jun 26 '15 at 13:31
I'll have to think about how to use this when I don't know which query is being called and the identity and number of parameters being used is dependent on the particular query. – Thomas Kerber Jun 26 '15 at 14:00
The code stops on the line: "qdf.Parameters(Parm1) = intVdrProfileID". I get "Item not found in this collection". I used the same ParameterName as in my query. I tried "Dim Parm1 as Integer", and "as Parameter" both result in the same error. Also tried without "Dim" statement and got "variable not defined". Not sure what do do to fix. Help? – Thomas Kerber Jun 26 '15 at 17:10
Ok, fixed that problem, but now need to solve this one: "Err: too few parameters. Expected (1)". Here is the code. Halts on "execute" statement. Dim Parm1 As Long Set qdf = db.QueryDefs(!QueryName) qdf.Parameters(Parm1) = intVdrProfileID MsgBox (!QueryName) & "(" & qdf.Parameters(Parm1) & ")" db.Execute !QueryName, dbFailOnError – Thomas Kerber Jun 26 '15 at 17:16

How to run parameterized query from VBA. Parameters sourced from recordset

1 Answers1

Linked