Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO SQL problem (Office 2000)

    I'm using an Excel named range as a data source for some VBA in Word. I can populate a listbox OK using the SQL query:
    "SELECT SupplierName FROM `Suppliers`"

    When I try to retrieve a recordset using:
    "SELECT * FROM `Suppliers` WHERE SupplierName=`" & ListBox1.Value & "`"

    (or any variations I can dream up) an error 3061 is thrown "Too few parameters. Expected 1". Both queries appear to be syntactically correct in Debug.Print, although being used to Oracle I don't know if MS has its own SQL quirks.

    I have seen multitudes of references to this situation (mainly for Access though, like on the Lounge) but can't identify what might be wrong in this case. I suspect I'm missing something in terms of what DAO can interpret. Any ideas please?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO SQL problem (Office 2000)

    The Microsoft version expects ordinary single or double quotes around the value, not <code>`</code>. You can omit the <code>`</code> around the range name (but they won't harm if you keep them):

    <code>"SELECT * FROM Suppliers WHERE SupplierName='" & ListBox1.Value & "'"</code>

    BTW Don't you mean ADO instead of DAO?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO SQL problem (Office 2000)

    Have you tried using a hardcoded criterium in the WHERE clause instead of a variable (just to test whether the SQL syntax is OK)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO SQL problem (Office 2000)

    Many thanks Hans - so simple to fix too! It shows you how little I dabble in MS SQL - the online reference I was using used the ` mark, probably to make it distinctive on screen against the enclosing " marks... although I'm rather <img src=/w3timages/censored.gif alt=censored border=0> off that they didn't point this out.

    I'm sure I'm using DAO:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    ... pretty much blindly following the bouncing ball at the moment. Is it better to use ADO in this simple context?

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO SQL problem (Office 2000)

    Thanks Jan. The Flying Dutchman pulled me out of the water, as you see.

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO SQL problem (Office 2000)

    For this kind of thing, ADO is more generic than DAO, but since DAO works, there is no pressing need to change.

    If you'd like to try ADO, see ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO SQL problem (Office 2000)

    I find this to be a good resource too:

    http://www.erlandsendata.no/english/...php?t=envbadac
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO SQL problem (Office 2000)

    Thanks!

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO SQL problem (Office 2000)

    Thanks gentlemen. Lots to read and lots to learn.

    cheers
    Alan

Posting Permissions

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