ado recordset, problem scrolling backward (1 Viewer)

[jesse]

Hi,

I'm nearly finished with my first acces project (very grateful for all the help I received here by the way).

I have split my database into a user interface (only forms and modules) and the real database (only tables) to allow for multiple users and easier maintenance. It works great but for one thing: I can not use the recordset.find method anymore. When I do it gives the following error: "rowset does not support scrolling backward". I have used this method quite often in my code so this is a huge setback.

I hope someone has a solution for me. I split hte database using a .udl file for the connection with readwrite|sharedenynone. I'm using access 2010 (but the error also appears in 2007). My connection code is below.

Thanks,
Jesse


unction ExecCommand(dbSQLstring As String) As ADODB.Recordset


Dim cmnd As ADODB.Command
Dim rec_set As ADODB.Recordset
Dim cnn as ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "File Name=" & database_path & "\connection.udl"

Set cmnd = New ADODB.Command
cmnd.ActiveConnection = cnn.ConnectionString
cmnd.CommandText = dbSQLstring

Set rec_set = New ADODB.Recordset
rec_set.CursorType = adOpenDynamic

Set rec_set = cmnd.Execute

Set ExecCommand = rec_set

cnn.Close
Set cnn = Nothing

End Function

 

[Banana]

I'm guessing that this is an connection to Access database file?

If so, then Jet does not support dynamic cursor. I'd bet that when you do this:

rec_set.CursorType = adOpenDynamic

it will get ignored and replaced with a different cursor type and thus isn't what you think what it is.

Next, note that this:

Set rec_set = cmd.Execute

will tend to coerce the recordset as a server side, forward only recordset. You actually would do this:

cmd.Execute
rec_set.Open cmd

To perserve the setting you have specified for the recordset. (Again, the settings has to be valid and if it's not, ADO will silently coerce the setting into something else so you need to check your code and make sure you are actually get the recordset you are expecting.)

HTH.

 

[jesse]

It works!

I'm very happy with this. Thanks a bunch

kind regards,
jesse

 

[Banana]

Swell. You of course did away with the dynamic declaration as well, yes? I'd think it would be replaced with Keyset which is close enough, anyway.

 

[jesse]

No, I just replaced

"Set rec_set = cmnd.Execute"

with

"cmd.Execute
rec_set.Open cmd"

like you said. Do you think it is better to do it a different way? Why do you think so?

 

[Banana]

If you put down a breakpoint after this line:

rec_set.CursorType = adOpenDynamic

and query in the immediate windows:

?rec_set.CursorType

I'm sure that the answer won't be 2 (aka adOpenDyanmic) but likely 1 (adOpenKeyset) or 3 (adOpenStatic). This is simply because dynamic cursor is not supported by the Jet OLEDB - it's in the documentation. ADO's behavior is to silently replace invalid values with something. The reason I point this out is because it's very possible to run ADO code without any errors and get unexpected behavior precisely because invalid values was given.

 

[jesse]

ah, ok, I understand. Thanks for pointing this out. I learned something new. And, come to think about it, I really don't need the opendynamic cursortype. The openkeyset (or, if need be, even the openstatic) will do just fine for everything I intend to do.