Recordset returns -1

[JAPA1972]

I have a view in SQL server (VIEW_TRIP_DATES). I am using Microsoft access to calculate the number of records in this table. While I can browse the recordset, I get -1 with the recordcount property. Am I missing something here?


Thanks

Public Function GetADOConn() As ADODB.Connection
Dim conn As ADODB.Connection
Dim adoConn As String

Set conn = New ADODB.Connection

adoConn = "Provider='SQLOLEDB';Data Source='co-nt-dmn6';" & _
"Initial Catalog='FLD';Integrated Security='SSPI';"



conn.Open adoConn
Set GetADOConn = conn
End Function




MyCode sub


Set rst = New ADODB.Recordset
rst.Open "SELECT TRIP_DATE FROM VIEW_TRIP_DATES", GetADOConn, adOpenForwardOnly


Debug.Print rst.RecordCount 'returns -1




rst.MoveNext
Debug.Print rst.RecordCount 'this also returns -1


end sub



Thanks

[Welshgasman]

What do you get when you MoveLast?

Then again, there is always Google?
https://www.google.com/search?q=get+...obile&ie=UTF-8

[June7]

Also review http://allenbrowne.com/ser-29.html#R...thout_MoveLast

[Welshgasman]

June7, I belive the issue is the command is not supported?, at least with that driver, which is what that link reports.
Never used ADO, so cannot be sure.

[Micron]

Actually, I was going to post that link, but I think the answer is in paragraph 3, not 4.

[Welshgasman]

I was going on just the first block of text at that link.
In fact if one select that firts link https://adodb.org/dokuwiki/doku.php?...he%20operation. more detail is available.

[jojowhite]

Public Function GetADOConn() As ADODB.Connection
Dim conn As ADODB.Connection
Dim adoConn As String


Set conn = New ADODB.Connection


adoConn = "Provider='SQLOLEDB';Data Source='co-nt-dmn6';" & _
"Initial Catalog='FLD';Integrated Security='SSPI';"






conn.Open adoConn
Set GetADOConn = conn
End Function








MyCode sub




Set rst = New ADODB.Recordset
With rst
   .CursorType = adOpenDynamic ' This is for a Dynaset
   .LockType = adLockOptimistic ' Allows updating records
   .Open "SELECT TRIP_DATE FROM VIEW_TRIP_DATES", GetADOConn
   if not (.BOF and .EOF) Then
       .MoveLast
       .MoveFirst
   End If
End With
Debug.Print rst.RecordCount 








rst.MoveNext
Debug.Print rst.RecordCount 'this also returns -1




end sub

[JAPA1972]

What do you get when you MoveLast?

Then again, there is always Google?
https://www.google.com/search?q=get+...obile&ie=UTF-8

Thanks for your response. If I set it to MoveLast I get this error. "Rowset does not support fetching backward "

[madpiet]

Why are you using a recordset for this at all?
Why not just do a COUNT()?

SELECT COUNT(*) FROM MyTable... sort of thing.

[Welshgasman]

Rowset does not support fetching backward

Is that because of "adOpenForwardOnly"

[madpiet]

adOpenForwardOnly should be something like adOpenStatic. You're not trying to move through the recordset at all. You want it to be static so you can get a count. But really, no, you don't want to use a recordset for this at all. Ever. Maybe write a function in SQL or write a stored procedure that filters a bunch of data and then passes the recordcount in an output parameter.

[Gicu]

@ JAPA1972
Can you please try this:

MyCode sub
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient 'Vlad -add this line before rst.open
rst.Open "SELECT TRIP_DATE FROM VIEW_TRIP_DATES", GetADOConn, adOpenForwardOnly
Debug.Print rst.RecordCount 'returns -1
rst.MoveNext
Debug.Print rst.RecordCount 'this also returns -1
end sub

Cheers,

[June7]

If all you want is a count, do as madpiet suggested in post 9, or use DCount() domain aggregate function.

[NoellaG]

Best practice when using SQL server as backend: pull as less as possible data over the net. So the solution #11 given by madpiet seems the best to me.
Second best: link the view as a table to your access front end. No need to open the connection anymore.
And if you open a new connection to SQL server with VBA, don't forget to close it! A few years ago I had to kick a developer out of my database and remove all rights because of the bad way the program was written (opening a new connection with a new SPID every minute without closing it crashed the server).