Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting DAO Function to ADO (2000+)

    I'm not sure whether I'm dealing with my ignorance of ADO or the flakiness of Access ADO - or both. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Having imported a DAO-based filter Search form into an ADO DB (2000 format running on 2003), I have been trying to convert the following utility function:<pre>Public Function OldCreateAndTestQuery(strTestQuery As String, _
    strTestSQL As String) As Long

    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Delete old query
    Set dbs = CurrentDb
    dbs.QueryDefs.Delete strTestQuery

    On Error GoTo ErrorHandler 'error hndling excluded

    'Create new query
    Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)

    'Test whether there are any records
    Set rst = OpenDatabase(strTestQuery)
    With rst
    .MoveFirst
    .MoveLast
    CreateAndTestQuery = .RecordCount
    End With

    End Function</pre>

    I have got thisfar:<pre>Public Function CreateAndTestQuery(strTestQuery As String, _
    strTestSQL As String) As Long

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset

    On Error Resume Next

    'Delete old query
    Set cnn = CurrentProject.Connection
    Set cat.ActiveConnection = cnn
    cat.Views.Delete strTestQuery

    On Error GoTo ErrorHandler 'error handling excluded
    Set cmd.ActiveConnection = cnn

    'Create new query
    cmd.CommandText = strTestSQL
    cat.Views.Append strTestQuery, cmd
    'Test whether there are any records
    rst.Open strTestQuery, cnn, adOpenKeyset, adLockOptimistic
    With .rst
    CreateAndTestQuery = .RecordCount
    End With

    End Function</pre>

    At present, rst.RecordCount always turns up as 0 - no matter how many records are actually returned by strTestQuery. strTestQuery is in fact a sub-Query. If the name of the parent Query to strTestQuery is entered, rst.RecordCount dutifully delivers the actual RecordCount.

    Any ideas/workarounds?
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    The following version works OK for me in Access 2002 SP-3:

    Public Function CreateAndTestQuery(strTestQuery As String, _
    strTestSQL As String) As Long
    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset

    On Error Resume Next

    'Delete old query
    Set cnn = CurrentProject.Connection
    Set cat.ActiveConnection = cnn
    cat.Views.Delete strTestQuery

    On Error GoTo 0

    'Create new query
    cmd.CommandText = strTestSQL
    cat.Views.Append strTestQuery, cmd
    'Test whether there are any records
    rst.Open strTestQuery, cnn, adOpenKeyset, adLockOptimistic
    With rst
    CreateAndTestQuery = .RecordCount
    End With
    End Function

    See screenshot.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    Your example works for me in Access 2003 - as the SQL is running against a Table.

    It also works for me in Access 2003 if the SQL is running against a Query based on a Table ("QueryA"). If, however, the SQL runs against a Query based on QueryA ("a sub-Query"), then it falls over. OTOH running the SQL against a sub-Query in the DAO version of the Function (using QueryDef) does not fall over.

    Is there any way to use (non-DAO) code to get at the number of Records returned by the sub-Query?
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    Can you provide more info about the query/subquery? Something like the SQL in the screenshot below still works for me.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    Perhaps the problem becomes clearer when you understand that Main Query is joining 2 Tables: <pre>SELECT
    tblRecordings.RecordingID, tblRecordings.RecordingArtist AS Artist,
    IIf(Left(tblRecordings.RecordingTitle,1)="t"
    Or Left(tblRecordings.RecordingTitle,1)="w",
    "20" & Mid(tblRecordings.RecordingTitle,3),
    "19" & Mid(tblRecordings.RecordingTitle,3)) AS PerformanceDate,
    tblRecordings.AcquiredAs AS Source, tblRecordings.Venue, tblTunes.TuneName AS Tune
    FROM
    tblRecordings INNER JOIN tblTunes ON tblRecordings.RecordingID=tblTunes.RecordingID
    WHERE
    (((tblTunes!TuneCondition)<>"Missing"
    And (tblTunes!TuneCondition)<>"Unavailable" And (tblTunes!TuneCondition)<>"XRef"))
    ORDER BY
    IIf(Left(tblRecordings.RecordingTitle,1)="t" Or Left(tblRecordings.RecordingTitle,1)="w",
    "20" & Mid(tblRecordings.RecordingTitle,3),"19" & Mid(tblRecordings.RecordingTitle,3)),
    tblTunes.TuneNumber;</pre>

    Sub-query<pre>SELECT * FROM qrySearch
    WHERE (((qrySearch.Tune) Like "*Down The Road Again*"));</pre>

    It occurs to me that I might have to turn the Main Query into a Make-Table Query, but that seems rather messy.
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    Similar queries still return the correct record count for me. If you wish, you can post a stripped down version of your database (see <post#=401925>post 401925</post#>)

    Or stay with DAO - in Access 2000 and 2002, Microsoft wanted to make ADO the default and phase out DAO, but in Access 2003, there seems to be a move back to DAO: it is one of the default references again, as it was in Access 97 and before.

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

    Re: Converting DAO Function to ADO (2000+)

    Have you tried setting the CursorLocation to see whether that affects the result? Try an adUseClient CursorLocation and specifically set the CommandType to adCmdText in the Open statement.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    I did try rebuilding the DB by importing all the objects etc into a new DB. This made no difference - nor did switching the the ADO reference back from 2.8 to (the default) 2.1.

    Hans - I'd have to convert the rest of the DB (including the Class Modules) back to DAO. A (very) stripped down version is attached. Are you going to come back with an "it works for me"? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Charlotte - Carrying out your suggestion raises Error -2147217900 (Invalid SQL Statement) - despite its being a SELECT statement. The trigger is adCmdText in the Open Statement.
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    ADO uses the same wildcards as SQL Server, not Access. You're probably getting the error on the asterisk.
    Charlotte

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

    Re: Converting DAO Function to ADO (2000+)

    Edited by HansV because original reply was incorrect

    Charlotte has hit the nail on the head. The * in the WHERE clause won't work in ADO, but you need it in Access. Try the attached version of the function.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    You're right about the WildCards, but....<UL><LI>"Nil returns" continued until I upgraded to 2002/3 format!<LI>Switching from "*" to "%" has made not a pin of difference if I try to run it as Command Text (viz. adCmdText)<LI>If I swap out the WildCards altogether from the String passed to the Function (viz. from strTestSQL - not cmd.CommandText), then the record Count is returned[/list]It would appear that the SQL has been erroring out on something else (as well). OTOH an example of (still) errored out SQL code is <code>SELECT DISTINCT * FROM qrySearch WHERE [Source] LIKE "Hundred Year Hall";</code>
    - which seems unexceptional.

    Thus, at the moment,it only runs when giving up the WildCard functionality altogether. Any further ideas?

    Edit: Changing the ANSI level to ANSI92 brings back the WildCard functionality - although I'm still curious about why adCmdText errors out.
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    SQL Server/ADO don't use "SELECT DISTINCT * FROM", although you should be able to use "SELECT DISTINCT FROM".
    Charlotte

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    >SELECT DISTINCT
    Noted.

    A (somewhat nasty) practical issue has arisen, which I seem to have tracked down to enabling the ANSI 92 format. ComboBox Autofill (AKA Autocomplete) on the Data Entry form (& its two sub forms) ceases to function. The only thing that stops the LimitToList error message is selecting an item from the drop down list.

    I've tried:<UL><LI>Creating a new DB, converting to ANSI 92 straight away & importing all the forms from an old Backup. Autofill works until the DB is closed and re-opened.<LI>Reimporting the corrupted form(s) will re-enable Autofill - but only until the DB is closed & re-opened (no matter how old the Backup). <LI>Re-building one of the sub-forms (using AutoForm) from scratch has the same net effect as reimporting.[/list]Have you heard of this before?
    Gre

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

    Re: Converting DAO Function to ADO (2000+)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Have you seen this and this? Do they offer any enlightenment?
    Charlotte

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting DAO Function to ADO (2000+)

    Not much. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> The queries I'm dealing with on these particular forms are all at the simple end of things. They also don't include any WildCards at all.

    I hope I haven't simply stumbled over a "feature" of ANSI 92 for MDBs. It doesn't stand to reason, but what does?

    Unless some great inspiration is forthcoming, I'm inclined to revert back to ANSI 89 and code my way around the WildCards issue. If I get to test this out on a different DB configuration, I will.
    Gre

Posting Permissions

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