如何从VBA运行参数化查询.来自记录集的参数

我有一个表单，用户可以从其中要导入其目录文件的组合框中选择供应商的名称.然后，组合框选择驱动查询以创建一个单记录记录集(rsProfile)，其中包含从所有供应商配置文件的表中查询的几个配置文件变量.然后将这些变量用于一系列不同的查询中，以将供应商的独特结构文件重新格式化，转换和规范化为可导入到我们系统中的标准化格式.

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.

这是我的rsProfile收集代码.有用.请注意， intVdrProfileID 是一个全局变量集，并在其他地方使用.

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)

我已经使用QueryDefs在运行时重写存储的查询，尽管它可以工作，但它很麻烦并且不能用于所有情况.

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

我希望使用DLookups将类似以下示例的内容作为存储的查询.我可以使它在VBA中工作，但对于存储的查询我什么也做不了.我愿意接受其他建议.

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.

存储的查询"qP0060c_DirectImportTape":

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;

附录: 让我调整一下问题，使其更复杂一些.我有大约40个查询的集合，每个查询使用不同的参数集合(或不使用任何参数).我还有一个表，其中包含每个供应商订阅"的特定查询集.目标是拥有一个数据库，非编码用户可以在其中添加新的供应商资料并创建/修改将针对该供应商文件运行的特定查询集.到目前为止，我已经有近100个供应商，因此单独编码每个供应商都是不切实际的.每个供应商文件将平均受到14个不同的更新查询.

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.

简化示例: 需要使用查询1、2和5处理Vendor1文件.Vendor2文件可能只需要更新查询2和4.这些查询的参数可能如下:

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()-无需参数

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

这是核心查询处理，仅循环遍历与当前供应商文件有关的查询. rsSubscrip 是包含此过滤查询列表的记录集(从主表中查询).

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