adOpenStatic can update, dbOpenSnapshot can't?

[Banana]

Reading the documentation for MySQL backend, it was suggested that client-side adOpenStatic cursor be used. Because I was using DAO, I assumed that the equivilant was dbOpenSnapshot, but that's non-updateable. I just tried setting a form's recordset to ADODB using client-side OpenStatic cursor and found that I could update as I like.

Does anyone know if using dbOpenDynaset would be resolved to client-side cursor? The ODBC driver does supports Dynamic cursor, but the documentation say this is artifical and ineffective for the present.

Can anybody shine a little light on this? TIA.

 

[RoyVidar]

Also, according to http://support.microsoft.com/kb/281998/EN-US/, you can only bind a form to a recordset with client side cursor, when using ODBC sources, which means it seems client side/static is the only option, if the documentation is correct.

 

[Banana]

Thanks for the pointer. I was hoping for a bit more clarification on how DAO handles cursors. Scanning the internet, there was a blurb that DAO doesn't support server-side cursors, so therefore any cursors it use would always be client-side. But the whitepaper on Jet & ODBC Connectivity says that DAO likes to use keyset cursor, which is usually a server-side client.

So I'm a bit confused.

But after thinking about it, I think it may make sense to use ADO for data from the MySQL backend, as documentations seem to suggest that DAO is indeed faster but only if you are using Jet engine, but that's not the case here. I only hope that I won't find myself missing a functionality from DAO not available in ADO.

 

[Banana]

One more thing- if anybody can point me to a documentation detailing on what cursors actually does, that'd be great.

I have been unable to get a clear answers on how cursors communicate- For example, if I used a client-side cursor, does that mean the server sends the full recordset over the wire to the client or does that mean servers gives client the keys and client can then request row as it move around?

The documentations I have read tend to say that using client side cursors means the server gives the whole set to the client which afterward there is no further communication with the server. Did that mean the whole recordset will be sent over the wire using client side cursors? Woudln't that defeat the purpose of cursors, no?

Thanks


Edit: Found some more documentation about ADO cursors. Reading the Significane of Cursor Locations, it says that entire recordset has to be sent over the wire for a client-side cursor. I thought that had answered my answer, but then when I read about Using Cachesize, it sounds like one can limit how much rows will be sent into increments, rather than all at once. Did that mean client-side cursors is capable of pulling few rows as needed for initial opening and continue to pull for more?

Still looking for documents on DAO's cursors....

 

[RoyVidar]

I thought that CacheSize was meant to give you the possibility to have control over caching with server side cursors. I e, use it to determine how many records are read initially into the cache (determined by CacheSize). Then when you reach "outside", fetch the next N records. I think clientside cursor will fetch the whole recordset regardless of cachesize setting.

 

[Banana]

If that was for server-side cursor, I'd expect the documentation to say so. But the closest thing it says is it depends on what the provider you are using for the cursors...

 

[Banana]

All right-

I tried to use ADO instead and failed to see any improvement in intelligently managing the connection. It continues to retrieves the entire table over the wire even if I set the CacheSize property.

Do I really have to write a function or something to force the connection to bring only few rows at a time for a client-side cursored recordset? As RoyVidar pointed out, one cannot bind the form to recordset that uses server-side cursor. It'd be acceptable if I could at least ensure that Access will only pull few records at a time and ask for more as user navigates around?

Any suggestions?