[AccessD] ADO Record Counts

See code below.

Also you might want to check your dim of rs.  In the beginning you declare
rs and in the code you use rs1.


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 not rs1.bof and not rs1.eof then 'Check to see if any records returned


       rs1.AddNew
       rs1!txtFilename = strFile
       rs1!Update
   End If

   rs1.Close
   cn.Close

   Set rs1 = Nothing
   Set cn = Nothing

End Sub


                                                                           
             "Jeremy Toves"                                                
             <itsame2000 at sbcgl                                             
             obal.net>                                                  To 
             Sent by:                  accessd at databaseadvisors.com        
             accessd-bounces at d                                          cc 
             atabaseadvisors.c                                             
             om                                                    Subject 
                                       [AccessD] ADO Record Counts         
                                                                           
             02/02/2005 11:29                                              
             AM                                                            
                                                                           
                                                                           
             Please respond to                                             
                  "Access                                                  
                Developers                                                 
              discussion and                                               
             problem solving"                                              
             <accessd at database                                             
               advisors.com>                                               
                                                                           
                                                                           




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




------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================