ADO Function Needed

From: Toco (Toco_at_discussions.microsoft.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 13:11:05 -0700

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

Relevant Pages

  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Connecting to Query
    ... practice examples of the ADO Command, Parameters, etc. you explained below. ... Second, with the selected territory ... >> combo box to select a product from a query based primarily on a link ...
    (microsoft.public.access.formscoding)
  • Re: On ADSI and LDAP
    ... the problem is how can I retrieve the value for myuser using the ... would be more efficient to use ADO to query AD for the attributes values. ... For more on using ADO, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ...
    (microsoft.public.scripting.vbscript)
  • Re: cant get query to run over ADO, but works fine in Access inte
    ... Thanks for the suggestions, Richard. ... the query so that unchanging parts of it are stored as a query with the part ... As I've had trouble with synchronizing ADO ... > Execute method of the ADO Connection object, but the issue is the same - you ...
    (microsoft.public.data.ado)
  • Re: Stored Procedure is running slow!
    ... > I run stored procedure from QA, it takes some 15 seconds to execute. ... > seconds when called from MS Fox Pro, via ADO. ... Try this in Query Analyzer: ... By default Query Analyzer runs with ARITHABORT ON, ...
    (microsoft.public.sqlserver.programming)