DoCmd.RunSQL中的VBA变量 [英] VBA Variables inside DoCmd.RunSQL

问题描述

只是一个问题.情况如下:

Just a question. Situation is as follows:

我有15个make table查询，这些查询可为索赔中使用的不同提交澄清代码提取数据；例如，将所有声明拉到提交澄清代码5处.到现在为止，我有一个宏，它将运行所有15个查询，但是由于[What Region?]提示，每次需要输入要过滤的区域时，我都会已在条件"字段中输入.

I have 15 make table queries that pulls data for a different submission clarification code that was used on a claim; i.e. Pull all claims where submission clarification code 5. As of right now I have a macro that will run all 15 queries, but each time I am required to type in the region I am filtering for due to the [What Region?] prompt I had put in the criteria field.

我的问题是:

是否可以使用VBA通过DoCmd.RunSQL运行全部15个查询，而我只需键入一次区域号，它将应用于所有查询?

Is it possible to use VBA to run all 15 queries using the DoCmd.RunSQL where I only have to type in the region number once and it will apply it to all queries?

最初的想法是让VBA提示我要过滤的区域，将其存储在变量中，然后在SQL语句中使用该变量.但是我什至不知道你能做到吗?预先感谢您提供的任何建议！

My initial thoughts were I would have VBA prompt me for what region I'm filtering for, store that in a variable, and then use that variable in the SQL statement. But I'm not even sure if you can do that? Thanks in advance for any advice that may be given!

更新:因此，在阅读了一些线程之后，我创建了一个模拟数据库来尝试一些概念，我想我可能走在正确的轨道上?

Update: So after reading a few threads, I created a mock database to try out some of the concepts and I think I might be on the right track?

Private Sub btnTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim SQLString As String

SQLString = "SELECT tblEmployees.ID, tblEmployees.Last_Name,  tblEmployees.First_Name, tblEmployees.SS_ID INTO Test" _
        & "FROM tblEmployees" _
        & "WHERE (((tblEmployees.ID)=[What number]));"

Set qdf = CurrentDb.QueryDefs("Query1")

qdf.Parameters(0).Value = InputBox("What is the ID Number")

qdf.Execute
qdf.Close

Set db = Nothing
Set qdf = Nothing

End Sub

因此，将其应用于15个查询，我只是将其他变量作为DAO.QueryDef调暗，对不对?我注意到我真的确定我需要SQLString部分吗?另外，我注意到在运行此模拟代码时，花了相当长的时间才能创建新表.这正常吗?同样，如果正在创建的表已经存在，则该代码将不会运行.有没有一种方法可以让代码将现有表替换为新表?此VBA有点新功能，感谢您的耐心等候.

So to apply this to the 15 queries I would just Dim other variables as DAO.QueryDef right? I'm note really sure i need the SQLString part either? Also, I noticed that when running this mock code it took quite a while for it to create the new table. Is this normal? Also also, the code will not run if the table it is creating already exists. Is there a way to just have the code replace the existing table with the new one? Kind of new to this VBA so thanks for your patience.

推荐答案

简短的回答是，可以.您想熟悉的一些关键字是参数"，它们是带有提示和"Querydef"或查询定义的变量.

Short answer is yes, this is possible. Some keywords you want to familiarize yourself with are "parameters" which are the variables with the prompt and "Querydef" or query definition.

有很多文章详细介绍了如何以编程方式将参数值传递给查询.在此页中查看有关如何完成此操作的完整概述.最值得注意的是，最后一个示例使用输入框来提示用户提供参数值，这使您接近所需的内容. (即cmd.Parameters(0).Value = InputBox("Enter a country name"))

There are quite a few articles detailing how to pass parameter values to a query programmatically. Check out this page for a solid overview of how to accomplish this. Most notably, the last example uses an inputbox to prompt the user to provide the parameter value, which gets you close to what you need. (ie. cmd.Parameters(0).Value = InputBox("Enter a country name"))

根据您的设计进行修改，最好创建一个字符串变量，然后先询问参数，然后在单独声明参数时使用该变量，这将允许将单个参数提交应用于所有查询.

Modified to your design, it might be best to create a string variable and ask for the parameter first, then use the variable in declaring the parameters individually, which would permit a single parameter submission that gets applied to all queries.

编辑

我已对您的代码进行了调整，以向您展示如何进行处理.您将不得不为每个查询重复执行该程序段.

I have adjusted your code to show you how to go about it. You will have to repeat the block for each query.

Private Sub btnTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strParamter As String

Set db = CurrentDb

strParamter = InputBox("What is the ID Number")

Set qdf = db.QueryDefs("Query1")

qdf.Parameters(0).Value = strParameter

qdf.Execute
qdf.Close

'Now move to next query

Set qdf = db.QueryDefs("Query2")

qdf.Parameters(0).Value = strParameter

qdf.Execute
qdf.Close

'...etc.

Set qdf = Nothing
Set db = Nothing


End Sub

这篇关于DoCmd.RunSQL中的VBA变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！