ms access 2003 中使用 vba 的参数化查询 [英] parameterized query in ms access 2003 using vba

问题描述

好的.我想使用参数化查询来避免处理数据中嵌入的双引号或单引号(" 或 ').

Ok. I want to use parameterized queries to avoid dealing with embedded double or single quotes (" or ') in my data.

举个简单的例子，这个参数化版本的 VBA 代码是什么样的?

As a simple example, what would the VBA code look like for the parameterized verion of this?

Dim qstr as String

Dim possiblyDangerousString as String

qstr = "SELECT MyTable.LastName from MyTable WHERE MyTable.LastName = '" & possiblyDangerousString & "';"

我没有从我的代码中剪切和粘贴它(现在在另一个盒子上)，所以可能有错字.

I did not cut and paste this from my code (on a different box right now), so there might be a typo.

一旦我弄清楚了这个简单的例子，我就需要转向更复杂的语句(多个参数和连接).感谢您的建议

Once I figure out this simple example, I need to move on to more complex statements (multiple parameters and joins). Thanks for any advice

推荐答案

在 VBA 中，您可以使用以下内容:

In VBA, you can use something like:

Dim db As DAO.Database
Dim qdf As QueryDef
Dim strSQL as String

Set db = CurrentDb
strSQL = "PARAMETERS txtLastName Text(150); " _
    & "SELECT LastName FROM MyTable " _
    & "WHERE LastName=txtLastName"

''Create a temporary query 
Set qdf = db.CreateQueryDef("", strSQL)

qdf.Parameters!txtLastName = Trim(possiblyDangerousString)

这个例子没有多大用处，因为你现在要对查询做什么?请注意，您可以在 VBA 中存储参数查询并分配参数.另请注意，备注字段会成为一个问题，因为参数只能接受 255 个字符.

This example is not much use, because what are you going to do with the query now? Note that you can store parameter queries and assign the parameters in VBA. Note also that memo fields become a problem because a parameter can only accept 255 characters.

这篇关于ms access 2003 中使用 vba 的参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！