>> ADO recordset moveprevious

[Jonathan]

Hi, using Access 2003 and sql2005. I would like to have a recordset that I
can navigate forwards and back, and get the record count using the following
code (snippet)...

conn.ConnectionString = "DSN=MATohbData"
conn.Open

Set cmd.ActiveConnection = conn
cmd.CommandTimeout = 300
cmd.CommandText = "spl_PostnatalCombined"
cmd.CommandType = adCmdStoredProc

With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
End With
Set rs = cmd.Execute

rs.MoveFirst
rs.MoveNext
rs.MoveLast

rs.MovePrevious

Debug.Print rs.RecordCount
Debug.Print rs.MaxRecords

....
rs.movefirst and rs.movenext are ok. rs.movelast and rs.moveprevious trigger
an error.

rs.recordcount = 0
rs.maxrecords = -1

The recordset should contain 7 records. I have tried other curser types
without success.

Any ideas or suggestions appreciated

Many thanks,
Jonathan

 

[Dirk Goldgar]

Hi, using Access 2003 and sql2005. I would like to have a recordset that I
can navigate forwards and back, and get the record count using the
following
code (snippet)...

conn.ConnectionString = "DSN=MATohbData"
conn.Open

Set cmd.ActiveConnection = conn
cmd.CommandTimeout = 300
cmd.CommandText = "spl_PostnatalCombined"
cmd.CommandType = adCmdStoredProc

With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
End With
Set rs = cmd.Execute

rs.MoveFirst
rs.MoveNext
rs.MoveLast

rs.MovePrevious

Debug.Print rs.RecordCount
Debug.Print rs.MaxRecords

...
rs.movefirst and rs.movenext are ok. rs.movelast and rs.moveprevious
trigger
an error.

rs.recordcount = 0
rs.maxrecords = -1

The recordset should contain 7 records. I have tried other curser types
without success.

Any ideas or suggestions appreciated

I work mostly with DAO, and know just enough ADO to get along. But from
what I can see, it doesn't matter that you specified a CursorType for the
recordset before assigning it from cmd.Execute, because that original
recordset is going to be replaced by the recordset returned by cmd.Execute.
And that one, I think, will default to a forward-only cursor.

I don't see any way, using a Command object, to override the cursor type.
But you could do without the Command object, and just use the Recordset
object:

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open "spl_PostnatalCombined", , , , adCmdStoredProc
End With

Does that work for you?

 

[Jonathan]

I work mostly with DAO, and know just enough ADO to get along. But from
what I can see, it doesn't matter that you specified a CursorType for the
recordset before assigning it from cmd.Execute, because that original
recordset is going to be replaced by the recordset returned by cmd.Execute.
And that one, I think, will default to a forward-only cursor.

I don't see any way, using a Command object, to override the cursor type.
But you could do without the Command object, and just use the Recordset
object:

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open "spl_PostnatalCombined", , , , adCmdStoredProc
End With

Does that work for you?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk, unfortunitely your suggestion still does not support movinglast or
movingprevious.

Many thanks, Jonathan

 

[Dirk Goldgar]

Hi Dirk, unfortunitely your suggestion still does not support movinglast
or
movingprevious.

Hmm, it should. Please verify that the stored procedure,
"spl_PostnatalCombined", actually returns records. Does it have parameters
that would need values supplied?

MoveLast and MovePrevious will both fail, of course, if the recordset is
empty. But in that case, so should MoveFirst.

 

[Jonathan]

Hmm, it should. Please verify that the stored procedure,
"spl_PostnatalCombined", actually returns records. Does it have parameters
that would need values supplied?

MoveLast and MovePrevious will both fail, of course, if the recordset is
empty. But in that case, so should MoveFirst.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Running the stored procedure directly in the sql database and using a
pass-through query in Access both return records.

Jonathan

 

[Dirk Goldgar]

Running the stored procedure directly in the sql database and using a
pass-through query in Access both return records.

Curiouser and curiouser. What happens if you use ".CursorLocation =
adUseServer" instead?

If that doesn't work, would you mind posting the full procedure, at least up
to the point where it fails? So far we've only seen what seem like they
ought to be the relevant snippets, but maybe there's something I'm
overlooking.

 

[Jonathan]

Curiouser and curiouser. What happens if you use ".CursorLocation =
adUseServer" instead?

If that doesn't work, would you mind posting the full procedure, at least up
to the point where it fails? So far we've only seen what seem like they
ought to be the relevant snippets, but maybe there's something I'm
overlooking.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk, changing the curser location does not change the outcome.

***** code start *****

Private Function GetObjects() As ADODB.Recordset
On Error GoTo Err_GetObjects
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection

conn.ConnectionString = "DSN=AUDData"
conn.Open

With rs
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "exec spl_Patients_Get"
.Open
End With

rs.MoveFirst
rs.MoveNext

'Next two lines trigger errors.
rs.MoveLast
rs.MovePrevious

'Using debug.print to confirm records returned.
Do Until rs.EOF
Debug.Print rs!PID
rs.MoveNext
Loop

Set GetObjects = rs

Exit_GetObjects:
On Error Resume Next
Set rs = Nothing
Exit Function
Err_GetObjects:
MsgBox Err.Description & " Error No: " & Err.Number, , "GetObjects"
Resume Exit_GetObjects
Resume
End Function
**** code end *****

 

[Dirk Goldgar]

Hi Dirk, changing the curser location does not change the outcome.

***** code start *****

Private Function GetObjects() As ADODB.Recordset
On Error GoTo Err_GetObjects
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection

conn.ConnectionString = "DSN=AUDData"
conn.Open

With rs
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "exec spl_Patients_Get"
.Open
End With

rs.MoveFirst
rs.MoveNext

'Next two lines trigger errors.
rs.MoveLast
rs.MovePrevious

'Using debug.print to confirm records returned.
Do Until rs.EOF
Debug.Print rs!PID
rs.MoveNext
Loop

Set GetObjects = rs

Exit_GetObjects:
On Error Resume Next
Set rs = Nothing
Exit Function
Err_GetObjects:
MsgBox Err.Description & " Error No: " & Err.Number, , "GetObjects"
Resume Exit_GetObjects
Resume
End Function
**** code end *****

I don't see anything wrong there, I'm afraid. It sounds like you're getting
a forward-only recordset (except that I thought .MoveFirst would also raise
an error in that case). Could you set a breakpoint after you've opened the
recordset and check the value of its CursorType property? And you might
also use the Immediate window to check what the recordset supports:

?rs.Supports(adMovePrevious)

?rs.Supports(adBookmark)

Are these stored procedures doing something special, besides just selecting
records? Something that would return a restricted cursor?

 

[Jonathan]

I don't see anything wrong there, I'm afraid. It sounds like you're getting
a forward-only recordset (except that I thought .MoveFirst would also raise
an error in that case). Could you set a breakpoint after you've opened the
recordset and check the value of its CursorType property? And you might
also use the Immediate window to check what the recordset supports:

?rs.Supports(adMovePrevious)

?rs.Supports(adBookmark)

Are these stored procedures doing something special, besides just selecting
records? Something that would return a restricted cursor?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Well ?rs.Supports(adMovePrevious) returns true before executing the
movelast/moveprevious and still returns true after triggering an error that
rowset does not support fetching backward.

Jonathan