Mackin, Christopher
CMackin at quiznos.com
Wed Feb 2 13:00:58 CST 2005
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