使用vba在ms Access 2003中进行参数化查询 [英] 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.

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