Access 2007 VBA SQL查询返回记录数为0，即使查询直接在Access中工作 [英] Access 2007 VBA SQL Query returns a recordcount of 0 even though the Query works in Access directly

问题描述

我一直在努力解决以下问题：获取一段VBA代码，针对给定的一组条件对Access数据库表执行SQL查找。我试图根据给定的姓氏，名字，地址，
和邮政编码查找捐赠者记录。我已经尝试过这个Outlook 2007 VBA，当它失败时，我重写了我的代码，以便在Access 2007中的VBA模块中运行。再次失败的是它产生的结果记录数为0.如果我修改代码以删除Where子句它将成功
返回表中所有记录的完整记录集。 Watch窗口向我显示Select语句具有正确的值，如果我直接在Access中的新Query中键入该语句，它将工作并检索正确的记录。我损失了
来解释为什么这个查询的VBA版本不起作用。以下是我要运行的代码：

选项比较数据库

选项明确

     ; Dim lngdonationId As Long

    Dim strfirstName As String

    Dim strlastName As String

    Dim strStreet1 As String

    Dim strZip As String

    Dim strDate As String

    Dim strTranId As String

    Dim strAmount As String

    Dim lngDonorId As Long

    Dim strTicketNumber As String

   

    Dim variables（）As String'流媒体值的缓冲区

   

    '初始化Access数据库连接变量

   

    Dim rsttblDonor As ADODB.Recordset

    Dim rsttblDeposit As ADODB.Recordset

    Dim rsttblDeposit_Sub As ADODB.Recordset

    Dim strAccessTableName As String

    Dim strSQL As String

    Dim strFilter As String

   

    '初始化文件流变量

    Dim fso As New FileSystemObject

    Dim fsoErrata As New FileSystemObject

   

    '声明一个TextStream。

    Dim stream As TextStream

    Dim streamErrata As TextStream

   

Sub List_All_Merchant_Transactions（）

    Dim strNames As String

       

'设置文件流连接

     

    '创建一个TextStream来保存数据并覆盖它（如果存在）。

   设置stream = fso.OpenTextFile（" c：\donord~1\MerchantEmailReceipt.log"，ForReading）

   设置streamErrata = fsoErrata.CreateTextFile（" c：\donord~1\MerchantEmailReceipt-Errata.log"，True）

     

 

  '打开tblDonor表

 设置rsttblDonor =新ADODB.Recordset

 

  '打开tblDeposit表

 设置rsttblDeposit =新ADODB.Recordset

 

  '打开tblDeposit_Sub表

 设置rsttblDeposit_Sub =新ADODB.Recordset

  '从流文件中读取并处理记录。

  Do While stream.AtEndOfStream<>真实
    strNames = stream.ReadLine

   

    '现在拆分此行中的数据

   变量（）=分割（strNames，"，"）

    strlastName = variables（0）

    strfirstName = variables（1）

    strStreet1 =变量（2）

    strZip =变量（3）

    strDate = variables（4）

    strTicketNumber =变量（5）

    strTranId =变量（6）

    lngdonationId =变量（7）

    strAmount = variables（8）

     

    '现在使用此交易更新Access数据库

    '在tblDonor中查找此捐赠者，以匹配第一个，最后一个，街道和邮政编号为
    strSQL =" select * from tbldonor其中tbldonor.lastName like'" &安培; strlastName& " *""&NBSP; &安培; "和tbldonor.street1一样" &安培; """ &安培; strStreet1& " *"" &安培; "和
tbldonor.zip一样" &安培; """ &安培; strZip& " *'"

    'strFilter =" lastName like'" &安培; strlastName& " *"" &安培; "和firstName喜欢" &安培; """ &安培; strfirstName& " *"" &安培; "和street1一样喜欢" &安培; """ &安培;
strStreet1& " *"" &安培; "和拉链一样" &安培; """ &安培; strZip& " *'"

        'rsttblDonor.Close

    rsttblDonor.Open strSQL，CurrentProject.Connection，adOpenStatic，adLockReadOnly

    MsgBox" RecordCount =" &安培; rsttblDonor.RecordCount'即使存在记录，也显示记录数为0！

    rsttblDonor.MoveFirst

代码继续......

我需要更改什么来检索数据库中的这些参数的一条记录？

提前致谢，

Reuven

解决方案

我很确定问题出在你使用通配符'*'上。 这是DAO和JET SQL的通配符，但是您使用ADO来处理查询，而ADO使用ANSI SQL通配符'％'。 尝试用'％'替换SQL语句中的'*'通配符字符
：

strSQL =" select * from tbldonor其中tbldonor.lastName like'" &安培; strlastName& "％""&NBSP; &安培; "和tbldonor.street1一样" &安培; """ &安培; strStreet1& "％"" &安培; "和tbldonor.zip一样" &安培; """ &安培; strZip& "％""

I have been struggling to resolve the following issue with getting a piece of VBA code to do a SQL lookup against an Access database table for a given set of criteria. I am trying to look up a Donor record based upon a given Last Name, First Name, Address, and Zip Code. I have tried doing this Outlook 2007 VBA and when it failed I rewrote my code to run in a VBA module in Access 2007. Again it failed in that it produced a resulting record count of 0. If I modify the code to remove the Where clause it will successfully return the complete recordset of all the records in the table. The Watch window shows me that the Select statement has the correct values and if I type that statement into a new Query in Access directly it works and retrieves the correct record. I am at a loss to explain why the VBA version of this query does not work. Here is the code I am trying to run:

Option Compare Database
Option Explicit

    Dim lngdonationId As Long
    Dim strfirstName As String
    Dim strlastName As String
    Dim strStreet1 As String
    Dim strZip As String
    Dim strDate As String
    Dim strTranId As String
    Dim strAmount As String
    Dim lngDonorId As Long
    Dim strTicketNumber As String
   
    Dim variables() As String ' Buffer for streamed values
   
    'Initialize Access Database connection variables
   
    Dim rsttblDonor As ADODB.Recordset
    Dim rsttblDeposit As ADODB.Recordset
    Dim rsttblDeposit_Sub As ADODB.Recordset
    Dim strAccessTableName As String
    Dim strSQL As String
    Dim strFilter As String
   
    'Initialize File Streaming variables
    Dim fso As New FileSystemObject
    Dim fsoErrata As New FileSystemObject
   
    ' Declare a TextStream.
    Dim stream As TextStream
    Dim streamErrata As TextStream
   
Sub List_All_Merchant_Transactions()
    Dim strNames As String
       
' Setup the File Streaming Connection
     
    ' Create a TextStream to save the data and overwrite it if it exists.
    Set stream = fso.OpenTextFile("c:\donord~1\MerchantEmailReceipt.log", ForReading)
    Set streamErrata = fsoErrata.CreateTextFile("c:\donord~1\MerchantEmailReceipt-Errata.log", True)
     
 
  ' Open the tblDonor table
  Set rsttblDonor = New ADODB.Recordset
 
  ' Open the tblDeposit table
  Set rsttblDeposit = New ADODB.Recordset
 
  ' Open the tblDeposit_Sub table
  Set rsttblDeposit_Sub = New ADODB.Recordset

  ' Read from the stream file and process the records.
  Do While stream.AtEndOfStream <> True
    strNames = stream.ReadLine
   
    ' Now split up the data in this line
    variables() = Split(strNames, ",")
    strlastName = variables(0)
    strfirstName = variables(1)
    strStreet1 = variables(2)
    strZip = variables(3)
    strDate = variables(4)
    strTicketNumber = variables(5)
    strTranId = variables(6)
    lngdonationId = variables(7)
    strAmount = variables(8)
     
    ' Now Update the Access Database with this transaction
    ' Lookup this Donor in tblDonor matching first, last, street and zip
    strSQL = "select * from tbldonor where tbldonor.lastName like '" & strlastName & "*'"  & " and tbldonor.street1 like " & "'" & strStreet1 & "*'" & " and tbldonor.zip like " & "'" & strZip & "*'"
    'strFilter = "lastName like '" & strlastName & "*'" & " and firstName like " & "'" & strfirstName & "*'" & " and street1 like " & "'" & strStreet1 & "*'" & " and zip like " & "'" & strZip & "*'"
        ' rsttblDonor.Close
    rsttblDonor.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    MsgBox "RecordCount =" & rsttblDonor.RecordCount ' Displays a record count of 0 even when one exists!
    rsttblDonor.MoveFirst

Code continues...

What do I have to change to retrieve the one record in the database for these parameters?

Thanks in advance,

Reuven

解决方案

I'm pretty sure that the problem is with your use of the wildcard character '*'.  That is the wild card for DAO and JET SQL, but you are using ADO to process the query, and ADO uses the ANSI SQL wild card '%'.  Try replacing the '*' wild-card character in your SQL statement with '%':

strSQL = "select * from tbldonor where tbldonor.lastName like '" & strlastName & "%'"  & " and tbldonor.street1 like " & "'" & strStreet1 & "%'" & " and tbldonor.zip like " & "'" & strZip & "%'"

这篇关于Access 2007 VBA SQL查询返回记录数为0，即使查询直接在Access中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！