sql - MS Access using VBA and Form, build a query string and run it
问题描述
I have a MS Access form where it will ask users to select or type in words that will be used in the where
clause for the query.
When the button is clicked, it will build the string and use that to run the query. This will be a read only and to view the subset results from a link table in SQL Server. Nothing more. I tried it with DoCmd.RunSQL
but that is only for action type like update, delete, etc.
On click button event, it will have similar query.
strSQL = "Select top 10 * Where ID = ''" + textbox1.value + "'' from linked_Table_Name;"
This did not work.
DoCmd.RunSQL strSQL
解决方案
Firstly, and most importantly, never concatenate user-obtained data as part of a SQL statement. Any application which uses this technique is wide open to a SQL injection attack and will also fail if the user includes a string delimiter (such as a single quote) or other reserved character in the input they provide.
Instead, use parameters.
For your scenario, I might suggest creating a saved query with the SQL:
select top 10 t.*
from linked_Table_Name t
where t.ID = Forms![Your Form Name]![textbox1]
Where Your Form Name
is the name of your form containing the control textbox1
.
Then, your on-click event can simply call the OpenQuery
method of the DoCmd
object:
DoCmd.OpenQuery "YourSavedQuery"
推荐阅读
- python - 在 macOS 11.3.1 上从 cv2.VideoCapture 读取失败
- docker - 这是什么意思?“需要 gitlab docker runner,通过“pass-thru”到主机 docker 套接字。”
- github - Dependabot 版本 2 和 Github 包
- python - db.session.commit() 不保存数据库中的更改
- sccm - 在 SCCM 中搜索成员资格查询
- activerecord - Rail ActiveRecord 获取所有多对多关系
- flutter - 第一次颤动后如何停止循环
- javascript - 如何使用两个不同的下拉列表对两个不同的数据属性进行排序?
- javascript - 如何限制 JavaScript .filter 结果?
- python - tqdm 进度条显示在两行