[AccessD] ADO Record Counts

Mackin, Christopher CMackin at quiznos.com
Wed Feb 2 11:50:45 CST 2005


You could also set the cursor type to the Client side (before opening), then you can get accurate recordcounts.

For your purpose though, you could just change the SQL statement to:

SELECT Count(*) FROM....

then evaluate rs1(0) to see if the value is > 0

-Chris Mackin

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Wednesday, February 02, 2005 10:37 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] ADO Record Counts


Check for EOF and BOF being true instead.

Drew

-----Original Message-----
From: Jeremy Toves [mailto:itsame2000 at sbcglobal.net]
Sent: Wednesday, February 02, 2005 11:29 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] ADO Record Counts


Can somebody tell me what is wrong here?  I am trying to change from using
DAO to ADO.  I've seen where several of you use ADO.  
 
I'm writing filenames to an archive table.  If the file I'm looking at
doesn't exist in the archive table, then I would append.  I get a -1 for the
record count, whether there is a match or not to the file I'm evaluating.
Here is a sample of what I'm running.  
 
Thanks,
Jeremy Toves  
 
**************************************************************
 
Sub Suspense_File_Recon
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strSQL As String, strFile As String
 
  Set cn = CurrentProject.Connection
  Set rs1 = New ADODB.Recordset
 
  strFile = "File1.txt"
 
  strSQL = "SELECT " _ 
                      & "tblFileArchive.* " _ 
                & "FROM " _ 
                       & "tblFileArchive " _ 
                & "WHERE " _ 
                        & "(((tblFileArchive.txtFilename)=" & Chr(34) &
strFile & Chr(34) & "));"
           
   rs1.Open _
         strSQL, _
         cn, _ 
         adOpenDynamic, _
        adLockOptimistic
 
   If rs1.RecordCount < 1 Then '================> Here is where the
recordcount _
 
works with DAO but not ADO.
       rs1.AddNew
       rs1!txtFilename = strFile
       rs1!Update
   End If
 
   rs1.Close
   cn.Close
 
   Set rs1 = Nothing
   Set cn = Nothing
 
End Sub
 
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list