将Access表单中的值插入到SQL传递查询中 [英] Inserting a value from an Access form into a SQL pass through query

问题描述

我正在创建一个小型Access数据库，它对SQL数据库执行一系列更新。

Access数据库由一个主表单组成，用户可以从中运行每次更新都通过一系列命令按钮。


每次更新都是作为SQL传递查询编写的。


示例更新：

UPDATE unit_instance_occurrences uio

SET fes_active_places =

（SELECT COUNT（*）FROM registration_units ru

WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code

AND ru.uio_occurrence_code = uio.calocc_occurrence_code

AND ru.progress_status =''A''

AND ru.uio_occurrence_code = 07）;

每个查询都依赖于一年（例如：07）但是我希望用户从主窗体上的组合框中确定该年份。


如何我最好这个吗？


关键是我希望能够从表单上的按钮运行查询传递。在单击按钮之前，用户必须选择年份。不知怎的，我想要能够将那年作为参数插入到PTQ 或的主体中，如果有必要，我可以将传递更新查询重写为纯粹的Access-update查询并将其插入到那里。


这几乎已经碰壁了所以任何帮助都会非常感激！

解决方案

最好的办法这样做是在VBA中动态创建查询。为了使代码工作，将不得不创建查询。


为了这段代码的目的，我把它放在一个名为cmdExecuteQuery的命令按钮上，我给了组合盒子名称cboYear因为我不知道它。

展开 | 选择 | Wrap | 行号

感谢您的支持回复！

已完全复制代码并相应地重命名我的按钮和组合框但仍然有问题。单击按钮时根本没有发生任何事情。通过在代码中放置一系列消息框，我将其缩小到我的VB似乎无法理解前两个命令的事实。


Dim db作为DAO。数据库

Dim qdfPassThru作为DAO.QueryDef


即使在这两行前面放置一个消息框仍然没有任何反应。

我把这两行剪掉了，我看到了消息框。


任何想法???

Private Sub cmdExecuteQuery_Click（）

MsgBox（" Test1"）

Dim db作为DAO.database

Dim qdfPassThru作为DAO.QueryDef

Dim strSQL As String

MsgBox（" Test2"）

设置db = CurrentDb

strSQL =" UPDATE unit_instance_occurrences uio" &安培; _

" SET fes_active_places =" &安培; _

"（SELECT COUNT（*）FROM registration_units ru"& _

" WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code"& _

" AND ru.uio_occurrence_code = uio.calocc_occurrence_code"& _

" AND ru.progress_status =''A''"& _

" AND ru.uio_occurrence_code ="& Me！cboYear&"）;"

MsgBox（" Test3"）

' '创建查询

设置qdfPassThru = db.CreateQueryDef（" QueryName"）

qdfPassThru.SQL = strSQL

qdfPassThru.Execute''you如果您不想运行查询，可以将其删除

MsgBox（" Test4"）

设置qdfPassThru = Nothing

设置db = Nothing

MsgBox（" Test5"）

End Sub

感谢您的回复！

已完全复制代码并相应地重命名我的按钮和组合框但仍然有问题。已经缩小到这样的事实....


Dim db作为DAO.database

Dim qdfPassThru作为DAO.QueryDef


似乎在我的visual basic中无法识别。有任何想法吗？？？？？

在VBA编辑器中，转到工具 - 引用，在勾选的参考库列表中找到一个名为Microsoft DAO XX Object Library的项目并勾选它。

I am creating a small Access db which performs a series of updates to a SQL database.

The Access db consists of a ?Main Form?, from which the user can run each update via a series of command buttons.

Each update has been written as a SQL pass-through query.

Example Update:

UPDATE unit_instance_occurrences uio
SET fes_active_places =
(SELECT COUNT(*) FROM registration_units ru
WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code
AND ru.uio_occurrence_code = uio.calocc_occurrence_code
AND ru.progress_status = ''A''
AND ru.uio_occurrence_code = 07);

Each query is dependant on a year (Eg: 07) however I want the user to determine that year from a combo box on the ?Main Form?.

How do I best go about this?

The key thing is that i want to be able to run my pass through query from a button on the form. Before clicking the button the user must select the year. Somehow I either want to be able to insert that year as a parameter into the body of the PTQ or if necessary I can rewrite the pass-through update queries as pure Access-update queries and insert it there instead.

Have pretty much hit a wall with this so any help would be greatly appreciated!

解决方案

The best way to do this is to create the query dynamically in VBA. For the code to work the query will already have to be created.

For the purposes of this code I have put it on a command button called cmdExecuteQuery and I''ve given the combo box the name cboYear as I don''t know it.

Expand|Select|Wrap|Line Numbers

Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Nothing is happening at all when i click the button. By placing a series of messsage boxes in the code i have narrowed it down to the fact that my VB doesn''t seem to understand the first 2 commands.

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

Even putting a message box infront of those two lines still nothing happens.
When i cut those two lines out i see the message boxes.

Any ideas???

Private Sub cmdExecuteQuery_Click()
MsgBox ("Test1")
Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
MsgBox ("Test2")
Set db = CurrentDb
strSQL = "UPDATE unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = ''A'' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
MsgBox ("Test3")
''create query
Set qdfPassThru = db.CreateQueryDef("QueryName")
qdfPassThru.SQL = strSQL
qdfPassThru.Execute '' you can leave this out if you don''t want to run the query
MsgBox ("Test4")
Set qdfPassThru = Nothing
Set db = Nothing
MsgBox ("Test5")
End Sub

Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Have narrowed it down to the fact that....

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

doesn''t seem to be recognised in my visual basic. Any ideas?????

In the VBA editor, go to Tools - References and in the list of ticked reference libraries find the one called Microsoft DAO X.X Object Library and tick it.

这篇关于将Access表单中的值插入到SQL传递查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！