Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    ADO has me trapped in a dark corner (Access 2000 SR1)

    Like most mere mortals, I don't tend to write much SQL from scratch unless it's a very simple statement - when I've wanted to automate the running of a complex query in the past I've designed it from the UI, called it something meaningful, like qryMyQuery, and used DAO to run it, e.g.

    set dbs = CurrentDb
    dbs.Execute "qryMyQuery", dbFailOnError

    Yesterday, in a brand new A2K database, I wanted to automate some updates and deletions from a command button, and foolishly decided to move into the modern world and use ADO from the outset. I wrote my delete query qryDeleteNonSick, ran it from the user interface to check that it would only delete what I wanted it to, then repopulated my test table, and created some code like this:

    set cnn = CurrentProject.Connection
    cnn.Execute "qryDeleteNonSick",,adCmdStoredProc

    And guess what? When run from code, the query deletes ALL records in the table, not just the ones it should!

    Now, the SQL behind the query looks something like this:
    DELETE tblEps.fld1, tblEps.fld2 FROM tblEps WHERE ((tblEps.fld2) NOT LIKE "Sick*"))

    By coding the SQL text directly and using this syntax

    cnn.Execute "DELETE etc etc",,adCmdText

    I've realised that the problem is with the wildcard - the * doesn't work, but % does!

    The question is: what do I do about it? I can't remember seeing that this is documented behaviour for ADO rather than DAO, and reverting to the DAO syntax seems like a regressive step in a pure A2K database. I could understand the logic of using ANSI SQL widcards if I were working with an ADP, but not an MDB only. Coding SQL directly will clearly work, but defeats the object of having a single query that I can test from the UI and automate from code!

    What would the more enlightened/experienced readers recommend?

    Jeremy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    By chance do you have the Use Ansi SQL 92 option turned on for your database? If so that might explain what's happening. What is your back-end - SQL Server by chance where there is no ODBC connection?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Remind me where I find the Ansi SQL 92 option - I cannot spot it under Options...

    And there is NO backend - it's just a simple, single-file MDB with no links, that's what's puzzling me...

    Jeremy

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    You won't find it in Access 2000. In Access XP, it's on the Tables/Queries tab of the dialog.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Thanks Charlotte

    BUT the question is: am I right in thinking that the ADO Execute method of the Connection object EXPECTS SQL-92 syntax, i.e. if you tell it to execute a stored procedure that you have created through Access's user interface and that query uses the * wildcard, then it will FAIL (or execute with unintended results)??? This is quite scary...

    Jeremy

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    What version of ADO do you have referenced? Access 2k installs the ADO 2.1 as a default in spite of its limitations, but you probably have at least 2.5 installed. Which one is referenced and does the behavior change if you reference another version? Have you tried it with a command object instead of using the connection? On my installation of A2k using ADO 2.5, this works:

    <pre> Dim ADOCmd As ADODB.Command
    Dim lngRecsAffected As Long

    Set ADOCmd = New ADODB.Command
    With ADOCmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = qryDeleteNonSick
    .CommandType = adCmdStoredProc
    End With
    ADOCmd.Execute lngRecsAffected

    Set ADOCmd = Nothing</pre>

    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Wait, I forgot the Like operator in the query I used. On my machine, the Like expression is ignored and no records are deleted using the command object, regardless of the wildcard I used in the criteria.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    I had ADO 2.5 installed, probably because I'd seen it in the References list and thought there was no reason not to "upgrade" the default referencing! However, the behaviour does not change if I "downgrade" the referencing to ADO 2.1, and I've also experienced the same behaviour on my home PC (original post was from work).

    Incidentally, it's probably clear by now, but when I refer to executing a "stored procedure" above, I do of course mean a "saved query" in Access. It's just that damned adCmdStoredProc parameter that makes me think in terms of SQL Server concepts!! Anyway, the problem doesn't go away if I omit this parameter.

    I haven't played with the command object yet but I'll give it a try.

    Jeremy

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Don't use ADO 2.1. It was missing some of the functionality available in 2.5 and later. Good luck finding an answer to the Like operator. In the meanwhile, if you're working with comparing to just the first letters of a value, this will work when called from ADO:

    DELETE tblEps.fld1, tblEps.fld2 FROM tblEps WHERE (left(tblEps.fld2),4) <> "Sick"))
    Charlotte

  10. #10
    New Lounger
    Join Date
    Dec 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Hi Jeremy,

    The following lists wildcards supported by Jet SQL that have OLE DB and ADO equivalents:

    Character: *
    Description: Matches any character or block of characters in that position.
    OLE DB and ADO Equivalent: %

    Character: ?
    Description: Matches any single character in that position.
    OLE DB and ADO Equivalent: _ (underscore, not hyphen)

    Character: #
    Description: Matches any single digit (0-9)
    OLE DB and ADO Equivalent: N/A

    Character: [list]
    Description: Specifies any single character not in list.
    OLE DB and ADO Equivalent: (^list)

    Combine these wildcards with the LIKE operator.

    Of course, there are always exceptions to these rules, and the Filter property
    of an ADO recordset is one of them. The following example works just fine:

    rsAnbieterbefragung.Open query_string, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    rsAnbieterbefragung.Filter = "cs_uri_stem Like '*" & strCriteria & "*'"

    Best,

    Bob

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Unfortunately neither of us could find a way to make a saved delete query with a LIKE operator in the criteria execute properly using ADO, regardless of the wildcard.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Thanks for looking - you've really just confirmed that I wasn't going mad after all! (I tried the command object too with the same results as for connection).

    Reading between the lines of the Help on Comparison of Jet and ANSI SQL, it does appear that ADO with the OLE DB provider for Jet expects the ANSI wildcards, and DAO with Jet expects the Access/Jet ones, and they are mutually exclusive.

    I don't have a problem coding SQL specific to the environment I'm working in here, but I must admit to a serious concern that a SAVED query can operate differently when run under ADO from when it's run via the UI (or DAO). If it failed to run at all, that would be a different matter, but just as a test I got ADO to open a recordset based on a crosstab (TRANSFORM) and also to open a recordset based on a saved query containing SELECT WHERE fld BETWEEN val1 AND val2, where val1 was greater than val2 (according to Help, ANSI SQL isn't supposed to like this!), but it was perfectly happy with these constructs.

    In my view, this "feature" is potentially dangerous, as evidenced by my experience with a DELETE query.

    Jeremy

  13. #13
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    Does this mean that queries using "*" written in Access97 for the Jet Database Engine will fail to work if I convert my database to use ADO as part of a port to Access2002/XP?

  14. #14
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    I don't have Access 2002/XP, but I would advise that you test very carefully. If my experience under Access 2000 is anything to go by, the queries will run as expected from the user interface, but NOT if you execute them via a method of an ADO object. I can't find anything explicit to confirm this behaviour, but I did find an MSKB article that warned that DAPs based on queries using Jet wildcards would not work!!

    Jeremy

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO has me trapped in a dark corner (Access 2000 SR1)

    No.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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