Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO recordset returns -1
I have a connection to the Northwind database from
Excel. My queries work find except when I request the number of records I always get -1. Is there something I'm missing? Here's a section of my code if this helps: Thank you in advance, Dennis *************start code**************** Dim myConnection As String Dim rsData As ADODB.Recordset '=================================== 'Create a connection to Northwind DB '=================================== myConnection = "Provider=Microsoft.jet.oledb.4.0;" & _ "Data source=C:\ProgramFiles\Microsoftoffice\" & _ "Office10 \Samples\Northwind.mdb;" sSQL = "SELECT * FROM Categories" Set rsData = New ADODB.Recordset rsData.Open sSQL, myConnection MsgBox rsData.RecordCount ************end code***************** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO recordset returns -1
Hi Dennis
Recordsets comes with different "cursors". Some let you browse back and forth, some are "forward only" (faster but no recordcount or moves except movenext and movelast). It seems that default is forwardonly from what you report here. Try rsData.Open sSQL, myConnection, adopenkeyset, adopenreadonly The speed issue between forwardonly and keyset depends on database brand and size. For smaller Access apps I don't notice much difference, Oracle likewise, but SQLserver is annoyingly slow using keyset. My personal undocumented experiences only. HTH. Best wishes Harald "Dennis" skrev i melding ... I have a connection to the Northwind database from Excel. My queries work find except when I request the number of records I always get -1. Is there something I'm missing? Here's a section of my code if this helps: Thank you in advance, Dennis *************start code**************** Dim myConnection As String Dim rsData As ADODB.Recordset '=================================== 'Create a connection to Northwind DB '=================================== myConnection = "Provider=Microsoft.jet.oledb.4.0;" & _ "Data source=C:\ProgramFiles\Microsoftoffice\" & _ "Office10 \Samples\Northwind.mdb;" sSQL = "SELECT * FROM Categories" Set rsData = New ADODB.Recordset rsData.Open sSQL, myConnection MsgBox rsData.RecordCount ************end code***************** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO recordset returns -1
Thank you for the reply. I tested your additional code
and received an error. Instead I wrote your first suggestion, adOpenKeyset and it worked great: rsData.Open sSQL, myConnection, adOpenKeyset I will have to study up on this in the future. Thanks, Dennis -----Original Message----- Hi Dennis Recordsets comes with different "cursors". Some let you browse back and forth, some are "forward only" (faster but no recordcount or moves except movenext and movelast). It seems that default is forwardonly from what you report here. Try rsData.Open sSQL, myConnection, adopenkeyset, adopenreadonly The speed issue between forwardonly and keyset depends on database brand and size. For smaller Access apps I don't notice much difference, Oracle likewise, but SQLserver is annoyingly slow using keyset. My personal undocumented experiences only. HTH. Best wishes Harald "Dennis" skrev i melding ... I have a connection to the Northwind database from Excel. My queries work find except when I request the number of records I always get -1. Is there something I'm missing? Here's a section of my code if this helps: Thank you in advance, Dennis *************start code**************** Dim myConnection As String Dim rsData As ADODB.Recordset '=================================== 'Create a connection to Northwind DB '=================================== myConnection = "Provider=Microsoft.jet.oledb.4.0;" & _ "Data source=C:\ProgramFiles\Microsoftoffice\" & _ "Office10 \Samples\Northwind.mdb;" sSQL = "SELECT * FROM Categories" Set rsData = New ADODB.Recordset rsData.Open sSQL, myConnection MsgBox rsData.RecordCount ************end code***************** . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO recordset returns -1
Hi,
the default CursorLocation is server-side (adUseServer) with forward only cursors. To navigate ADO recorsets, and get information like "Recordset.Count" or "Recordset.AbsolutePosition" you need to explicitly set it to adUseClient ... same for default recordsets returned by code like this: Set RS = Connection.Execute sSql To read up on ADO see the MDAC SDK here at the MSDN Library he http://msdn.microsoft.com/library/de...orlocation.asp HTH Philip " wrote: Thank you for the reply. I tested your additional code and received an error. Instead I wrote your first suggestion, adOpenKeyset and it worked great: rsData.Open sSQL, myConnection, adOpenKeyset I will have to study up on this in the future. Thanks, Dennis -----Original Message----- Hi Dennis Recordsets comes with different "cursors". Some let you browse back and forth, some are "forward only" (faster but no recordcount or moves except movenext and movelast). It seems that default is forwardonly from what you report here. Try rsData.Open sSQL, myConnection, adopenkeyset, adopenreadonly The speed issue between forwardonly and keyset depends on database brand and size. For smaller Access apps I don't notice much difference, Oracle likewise, but SQLserver is annoyingly slow using keyset. My personal undocumented experiences only. HTH. Best wishes Harald "Dennis" skrev i melding ... I have a connection to the Northwind database from Excel. My queries work find except when I request the number of records I always get -1. Is there something I'm missing? Here's a section of my code if this helps: Thank you in advance, Dennis *************start code**************** Dim myConnection As String Dim rsData As ADODB.Recordset '=================================== 'Create a connection to Northwind DB '=================================== myConnection = "Provider=Microsoft.jet.oledb.4.0;" & _ "Data source=C:\ProgramFiles\Microsoftoffice\" & _ "Office10 \Samples\Northwind.mdb;" sSQL = "SELECT * FROM Categories" Set rsData = New ADODB.Recordset rsData.Open sSQL, myConnection MsgBox rsData.RecordCount ************end code***************** . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO recordset returns -1
Philip wrote ...
the default CursorLocation is server-side (adUseServer) with forward only cursors. Yes, but you can specify something other than the default! To navigate ADO recorsets, and get information like "Recordset.Count" or "Recordset.AbsolutePosition" you need to explicitly set it to adUseClient Incorrect. Availability of such properties is determined by cursor type, not location. Any cursor type other than 'forward only' will support the RecordCount property. get information like "Recordset.Count" The Recordset object does not have a Count property. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index with mulitple value returns and muliple column returns | Excel Worksheet Functions | |||
Date returns always returns: 00 January 1900 | Excel Worksheet Functions | |||
Recordset.Recordcount Returns -1! | Excel Programming | |||
Type recordset/recordset? | Excel Programming | |||
DAO query/recordset returns with field names | Excel Programming |