[AccessD] ADO Record Counts

Not true, you can use recordcount with adOpenDynamic, you just need to set the CursorLocation to adUseClient (I had previously said CursorType, but it's CursorLocation)

Here's an example:

Sub Suspense_File_Recon()
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strFile As String

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient

strSQL = "SELECT TOP 10 InvDate FROM dbo.tVSA_Sales"

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Debug.Print rst.RecordCount

           
End Sub



-Chris Mackin


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Garraway, Alun
Sent: Wednesday, February 02, 2005 11:15 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] ADO Record Counts


IRRC RecordCount does not work with

adOpenDynamic

try
adOpenKeyset

instead

hth
alun


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeremy Toves
Sent: Wednesday, February 02, 2005 6:29 PM
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