Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Issue retrieving records with ADODB

    Hi - I have a peculiar problem and need urgent help. I have written a code in Excel VBA to retrieve data from an Access Database. I am not getting any errors while running the script but the query does not seem to be retrieving any data. I did a debug.print to get the SQL string and ran the same query in Access directly and it works perfectly fine and gets me the records

    If I use "Select * from <Table>" in the code without any conditions I am able to retrieve data.

    Please see below the relevant extract from the code that I am using:

    -------------------------------------------------------------------------
    Dim conn As New ADODB.Connection
    Dim rsTemp As New ADODB.Recordset
    Dim squery as string
    squery = "Select * from VendorTable where " & _
    "(" & pvn & ")" & " AND " & _
    "(" & pgroup & ")" & " AND " & _
    "(" & shortname & ")" & " AND " & _
    "(" & vendorname & ")" & " AND " & _
    "(" & city & ")" & " AND " & _
    "(" & astate & ")" & " AND " & _
    "(" & zip & ")" & " ORDER BY PVN"

    conn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:\VendorDB.mdb"
    rsTemp.Open squery, conn, adOpenKeyset

    'I always get rsTemp.EOF as true
    '
    ' If I do a debug.print then squery =
    Select * from VendorTable where (VendorTable.[PVN] LIKE '*' OR VendorTable.[PVN] Is Null) AND (VendorTable.[VGroup] LIKE '*' or VendorTable.[VGroup]Is Null) AND (VendorTable.[ShortName] LIKE '*' or VendorTable.[ShortName] Is Null) AND ( VendorTable.[VendorName] LIKE ('*aaa*')) AND (VendorTable.[City] LIKE '*' or VendorTable.[City] is Null) AND (VendorTable.[AState] LIKE '*' or VendorTable.[AState] is Null) AND (VendorTable.[Zip] LIKE '*' or VendorTable.[Zip] is Null) ORDER BY PVN
    '
    'If I run the same query from Access it works perfectly fine and gets me the records

    'However the following works fine:
    squery="Select * from VendorTable"
    --------------------------------------------------------------------------

    I am stumped! Please help.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Try using the '%' wildcard character instead of the *...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2009
    Posts
    2
    This worked! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •