Re: ADO Function Needed

From: ScottShell (ScottRShell_at_comcast.net)
Date: 09/29/04

  • Next message: AA2e72E: "RE: Problem creating access Query on the fly" 
    Date: Tue, 28 Sep 2004 21:44:03 -0700

    If all you need is the record count, you could try

    SET rstOne = CurrentProject.Connection.Execute("SELECT COUNT(custID) FROM
    tbCustomerLetters")

    and then have the SELECT CASE... structure below that, so you only execute
    it one time.

    Or, could you just move the recordset code above the SELECT CASE, and assign
    the RecordCount property to a local variable?

    "Toco" <Toco@discussions.microsoft.com> wrote in message
    news:D65581CA-6082-4739-8C09-99E4F11D7AE2@microsoft.com...
    > Hello. I have an ADO statement that executes the same query, in the body
    of
    > a case statement. I seem to repeat the same ADO call because the
    > .RecordCount is dynamic, even though the actual SQL is the same. My goal
    is
    > to create a function that executes the query, opens the recordset, then
    > returns a .RecordCount. Is it possible to implement this using a
    Function,
    > that uses the .RecordCount as the return value? That way I can call the
    > function from within the case statements? As it stands now I have dimmed
    > about 5 different strSQL and rstCounts, all within one Select Case
    statement.
    > This is very unmanaged, spaghetti code I have going. Here is the ADO
    > procedure I have repeated 5 times for every case option
    >
    > Dim strSQL1 As String, rstOne As New ADODB.Recordset
    > strSQL1 = "Select custID from tbCustomerLetters"
    > rstOne.Open strSQL1, CurrentProject.Connection, adOpenStatic,
    > adLockReadOnly
    > With rstOne
    > If .RecordCount = 0 Then
    > MsgBox "No customers were processed in your query", 64
    +
    > 0, "No Letters printed"
    > GoTo ExitPrint
    > End If
    > If MsgBox("Continue?", vbYesNo, "There are " &
    .RecordCount
    > & " Letters to be printed ") = vbYes Then
    > Call PrintReport("rptCustomerLetter")
    > Else
    > rstOne.Close
    > End If
    > End With
    >
    > Next Case Statement---
    >
    > Toco
    >
    >
    >

  • Next message: AA2e72E: "RE: Problem creating access Query on the fly"

    Relevant Pages

    • ADO Function Needed
      ... I seem to repeat the same ADO call because the ... .RecordCount is dynamic, even though the actual SQL is the same. ... to create a function that executes the query, opens the recordset, then ...
      (microsoft.public.data.ado)
    • Re: AbsolutePosition Problem
      ... > It sounds like you are still using ADO Recordsets, ... > DataTables and DataSets, right? ... > AbsolutePosition and RecordCount were never recommended for reliable ... > you are recieving -1 for AbsolutePosition, ...
      (microsoft.public.dotnet.languages.vb)
    • Re: Problems querying DB2 through ADO
      ... you cannot count on an accurate recordcount from any driver. ... > ADO you can use adUseClient cursor and it will cursor the records on the ... > client side an provide recordcount for you, but the price for this is caching ... Unfortunately my code relies entirely on it. ...
      (microsoft.public.data.ado)
    • Re: VB6 & SQL2000 Stored Proc problem
      ... Consider that COM-based ADO returns control to the application BEFORE rowset population is complete for some cursors. ... See "ADO and ADO.NET Examples and Best Practices " or "Hitchhiker's Guide to Visual Basic and SQL Server " for details--and suggestions about why it's not a good idea to use SELECT *. ... Are you assuming theres' too many records based on the return from .RecordCount? ...
      (microsoft.public.vb.database.ado)
    • Re: AbsolutePosition Problem
      ... > opening to get an accurate recordcount. ... >> It sounds like you are still using ADO Recordsets, ... >> you are recieving -1 for AbsolutePosition, ... >> You should consider using ADO .NET and loading your data into DataTables ...
      (microsoft.public.dotnet.languages.vb)