Is it an Error I am Getting Record Count as -1

vivek_inos · December 14th, 2005, 07:23 AM

hello everyone,

is it possible to share single MS Access appliaction across the network or having centralised SQL server as backend and ACCESS as frontend.
And every use will be access the some data.

And How do we get record count
it returning -1 .

Thanks

Best Regards,
Vivek.

leehambly · December 14th, 2005, 07:36 AM

Vivek...

The answer to your first question is yes, it is possible to share an Access application across a network.
And second: Yes, it is also possible to have SQL as your backend.
However, this is a fairly massive area and it would be very worthwhile investing in some books on the subject. There is no real set methodolgy for doing this, since there are so many different variables, platforms, environment, basic business approach, security requirements etc...

As for your recordcount issue... what are you using to get the recordcount?

vivek_inos · December 14th, 2005, 07:56 AM

Quote:

quote:Originally posted by leehambly
Vivek...

The answer to your first question is yes, it is possible to share an Access application across a network.
And second: Yes, it is also possible to have SQL as your backend.
However, this is a fairly massive area and it would be very worthwhile investing in some books on the subject. There is no real set methodolgy for doing this, since there are so many different variables, platforms, environment, basic business approach, security requirements etc...

As for your recordcount issue... what are you using to get the recordcount?

I m using recordset
rs.RecordCount returns me -1

Best Regards,
Vivek.

Bob Bedell · December 14th, 2005, 03:45 PM

Hi Vivek,

In order to get a record count using the RecordCount method, you will need to use a cursor that supports the functionality to report how many records it contains. Not all cursor types provide that functionality. Without delving too deep into the whys and wherefores, forward-only and dynamic cursors cannot provide a definite value for RecordCount; static and keyset cursors can.

If you need to use a forward-only or dynamic cursor and need a record count, count the records as you fetch them, or use the SQL COUNT function to return the number of records retrieved by a query.

Best,

Bob

echovue · December 14th, 2005, 03:50 PM

If I remember right as well - Having the Cursor on the Client side as opposed to the Server side makes a difference as well.

Mike

Mike
EchoVue.com

Bob Bedell · December 14th, 2005, 05:15 PM

Hi Mike,

Yes, cursor location does make a difference, but for the reasons I specified above. Its all about the cursor type returned.

When you instantiate a client-side cursor with Access, you are passing data from the Access cursor engine to the ADO Cursor Engine. The ADO Cursor Engine only stores data in static (though updateable) cursors, regardless of the cursor type you specify when instantiating your recordset. So:

rst.CursorLocation = adUseClient
rst.Open "MyRecordset", cnn, adOpenDynamic, adLockOptimistic

returns a static cursor with optimistic locking (and a record count if you invoke RecordCount), even though you requested a dynamic cursor. You can always check the recordsetâs CursorType property after you have opened it to verify this.

ALL client-side cursors are static cursors, regardless of requested cursor or lock type.

So can you get a server-side cursor capable of reporting how many records it contains using RecordCount? Yes, if you obtain either a static or keyset cursor. Turns out, a keyset cursor is the only cursor available (when using a server-side cursor with Access) that can report the number of records it contains using RecordCount. To obtain one, instantiate your server-side recordset (the default) as either adOpenStatic or adopenKeyset. Both return a keyset cursor capable of reporting the number of records it contains using RecordCount.

Best,

Bob

echovue · December 14th, 2005, 05:23 PM

Hi Bob,

Thanks for the explanation - It always helps to know the hows and whys!

Thanks again,

Mike

Mike
EchoVue.com

crapanz · December 25th, 2005, 03:25 PM

Bob,
In your explanation, are you referring to using an ADO cursor to access an MDB file or SQL Server?

Database Agreements

Bob Bedell · December 26th, 2005, 12:31 AM

I didn't have either platform in mind particulary. In my experience my explanation applies to both equally. The gist of the matter is that in order for a cursor to accurately report the number of records it contains, it has to support the concept of a records absolute postion within the cursor. Dynamic cursors can't do that because record membership changes with deletions and insertions, and forward-only cursors can't do that because records are discarded as you scroll to the next record.

With ADO, a recordset (regardless of platform) supports the RecordCount property if it supports either the AbsolutePosition or Bookmark property.

If you're curious about whether one of your recordsets opened against either Jet or SQL Server supports these properties, use it as an argument for the following function. I think you'll find that all types of client-side cursors support these properties (because all client-side cursors are static) and that only server-side Static and Keyset cursors support these properties, for both Jet and SQL Server:

Code:

Public Sub DisplaySupport(rstTemp As ADODB.Recordset)

   Dim alngConstants(11) As Long
   Dim booSupports As Boolean
   Dim intIndex As Integer

   ' Fill array with cursor option constants.
   alngConstants(0) = adAddNew
   alngConstants(1) = adApproxPosition
   alngConstants(2) = adBookmark
   alngConstants(3) = adDelete
   alngConstants(4) = adFind
   alngConstants(5) = adHoldRecords
   alngConstants(6) = adMovePrevious
   alngConstants(7) = adNotify
   alngConstants(8) = adResync
   alngConstants(9) = adUpdate
   alngConstants(10) = adUpdateBatch

   For intIndex = 0 To 10
      booSupports = _
         rstTemp.Supports(alngConstants(intIndex))
      If booSupports Then
         Select Case alngConstants(intIndex)
            Case adAddNew
               Debug.Print "   AddNew"
            Case adApproxPosition
               Debug.Print "   AbsolutePosition and AbsolutePage"
            Case adBookmark
               Debug.Print "   Bookmark"
            Case adDelete
               Debug.Print "   Delete"
            Case adFind
               Debug.Print "   Find"
            Case adHoldRecords
               Debug.Print "   Holding Records"
            Case adMovePrevious
               Debug.Print "   MovePrevious and Move"
            Case adNotify
               Debug.Print "   Notifications"
            Case adResync
               Debug.Print "   Resyncing data"
            Case adUpdate
               Debug.Print "   Update"
            Case adUpdateBatch
               Debug.Print "   batch updating"
         End Select
      End If
   Next intIndex

End Sub

HTH,

Bob

Bob Bedell · December 26th, 2005, 01:23 AM

There is something worth mentioning in the context of this discussion where Jet and SQL Server do behave differently. I mentioned that with Jet, the cursor type and lock type you request can differ pretty drastically from the cursor type and lock type you actually get, particulary when requesting dynamic cursors. (Jet doesn't support dymanic cursors and gives you a keyset instead, unless you request a dynamic cursor with read-only locking, in which case you get a static cursor).

Things are different here with SQL Server. SQL Server does support server-side dynamic cursors, so what you request is usually what you get. The exception is: if your request a server-side static cursor, SQL Server maintains a keyset cursor, unless you request a static cursor with read-only locking, in which case you get just that.

Mind-numbing, isn't it. Fortunately for all of us, static, keyset, and dynamic cursors perish with the .NET framework and ADO.NET (which are built on the concept of a totally disconnected, client-side data cache fed by forward-only data streams).

Bob