Split databases and ADO Cursors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony - 16 May 2006 18:05 GMT

I've read that OLE DB is the best connection technology when working on
a slow/unstable network because it is thread safe. I've also read that
only ADO supports OLE DB, so I'm limiting myself to JET OLE DB and ADO.

I'm having trouble understanding which cursors to use when developing
for a split/file server environment (a local mdb linked to a remote mdb
on a network share).

Does a split architecture imply that I should always use a client-side
cursor?

What happens when I use each cursor in this architecture? Where does
the processing occur? How does it effect network traffic?

Recommendations for good references related to split architectures,
ADO, and VBA?

About our environment:

We've got some 300 - 500 mb Access files currently being shared on the
network. Corruption happens daily on at least one of the files.

I'm trying to improve our situation by pushing for split solutions. We
do not own the network and cannot setup a DB server or a web server for
better multi-user performance.

Reply to this Message

Tony Toews - 17 May 2006 05:23 GMT

>I've read that OLE DB is the best connection technology when working on
>a slow/unstable network because it is thread safe.

Got any URLs on that statement? I'm not even sure what OLE DB is.

>We've got some 300 - 500 mb Access files currently being shared on the
>network.

That's a pile. Are they split?

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

> Corruption happens daily on at least one of the files.

For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

>I'm trying to improve our situation by pushing for split solutions. We
>do not own the network and cannot setup a DB server or a web server for
>better multi-user performance.

Hmmm, not sure why you'd want a web server for Access apps.

Tony

Signature

Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Reply to this Message

Tony - 17 May 2006 16:42 GMT

None of the files are split. For clarity, we've got around 25 shared
MDBs, some of which are 300 - 500 mb.

I think my ADO Cursor question stems from not really understanding the
tiers of a split Access database architecture. There's a file server
where the BE resides, but is there a server that can be associated with
adUseServer, the default CursorLocation? Where's the service? Isn't
everything happening on my workstation, the client?

A definition I found online at:
http://www.w3schools.com/ado/prop_cursorlocation.asp

"The CursorLocation property sets or returns a long value that
indicates the location of the cursor service. It can be set to one of
the CursorLocationEnum values. Default value is AdUseServer.

A cursor is used to:
control record navigation
control the visibility of changes in the database
control the updatability of data"

This just doesn't speak to me about where the processing is happening
in a split situation. What are the tiers? Why do I care? Because so
many things in ADO seem to depend on using the right combination of
CursorLocation, CursorType, and LockType. It is so confusing that I'm
beginning to understand why experts swear by DAO.

Seems to me that if OLEDB connections can be accomplished in a split
database by redefining table links (to ensure that they are not ODBC),
then everthing else can be done in the FE using table links and DAO.
ADO never comes into play. Right? Does the question of CursorLocation
then go away, or does DAO have its own demons?

The OLD DB recommendation came from a note on page 89 of O'Reilly's
"Fixing Access Annoyances" by Phil Mitchell and Evan Callahan.

"If you're using ADO libraries, connect via the OLEDB driver rather
than using Open Database Connectivity (ODBC). ODBC is not thread safe
-- it isn't stable in a multi-user environment and can cause data
corruption."

Thanks! -Tony M.

Reply to this Message

Tony Toews - 17 May 2006 17:39 GMT

>None of the files are split. For clarity, we've got around 25 shared
>MDBs, some of which are 300 - 500 mb.

Ouch. No wonder you're getting corruptions.

>I think my ADO Cursor question stems from not really understanding the
>tiers of a split Access database architecture. There's a file server
>where the BE resides, but is there a server that can be associated with
>adUseServer, the default CursorLocation?

I can't answer your question as I've never used cursors. Never had a
need for them so I've never bothered to investigate them. My use of
recordsets is relatively simple. And I've written some very complex
apps.

>Seems to me that if OLEDB connections can be accomplished in a split
>database by redefining table links (to ensure that they are not ODBC),

Well, you just use the Linked Table Manager to link to the tables in
the BE. No idea if OLEDB comes into play as I'm not familiar with it.
But it sure doesn't use ODBC in that scenario.

>then everthing else can be done in the FE using table links and DAO.
>ADO never comes into play. Right?

Correct. But you can also use ADO with simplistic settings instead of
DAO.

>Does the question of CursorLocation
>then go away, or does DAO have its own demons?

DAO might have its own demons but I'm so used to it that I can't
recall any of them. <smile>

>The OLD DB recommendation came from a note on page 89 of O'Reilly's
>"Fixing Access Annoyances" by Phil Mitchell and Evan Callahan.
[quoted text clipped - 3 lines]
>-- it isn't stable in a multi-user environment and can cause data
>corruption."

I've responded to this in the other thread.

Tony

Signature

Reply to this Message

Tony Toews - 17 May 2006 17:40 GMT

>None of the files are split. For clarity, we've got around 25 shared
>MDBs, some of which are 300 - 500 mb.

So are your databases currently using DAO or ADO?

The first thing I'd do to minimize corruptions is to split the
databases and give each user their own front end.

Tony

Signature

Reply to this Message

Tony - 17 May 2006 21:13 GMT

I'm new here. My predecessors used DAO. My first day here I learned of
corruptions, went to the book store. Hence, the OLE DB discussions.
Probably a red herring, but I think I understand things better now. My
background is ASP. Used ADO quite a bit from there. DAO is new to me.

Yeah, I'm pushing for the split. If it were one or two MDBs, it would
be easier. These things are linked together like a web. FE deployment
is a big concern. Hard to shake things up.

-Tony M.

Reply to this Message

Tony Toews - 17 May 2006 21:48 GMT

>I'm new here. My predecessors used DAO. My first day here I learned of
>corruptions, went to the book store. Hence, the OLE DB discussions.
>Probably a red herring, but I think I understand things better now. My
>background is ASP. Used ADO quite a bit from there. DAO is new to me.

For the basics, there are two differences between ADO and DAO. One is
the variable dimming and connection string stuff for executing
queries.

The other is that you don't need the .addnew or .update when adding
new records via a recordset in code. I can't remember which now.

>Yeah, I'm pushing for the split. If it were one or two MDBs, it would
>be easier. These things are linked together like a web. FE deployment
>is a big concern. Hard to shake things up.

Uh oh, that could be very interesting. And should a server name get
changed and they are using UNC all kinds of hard coded links between
databases could start puking big time.

Mind you splitting shouldn't be of concern when things are "linked."
The data would still reside in the same MDBs.

Tony

Signature

Reply to this Message

Tony - 17 May 2006 16:50 GMT

Reply to this Message

Larry Linson - 18 May 2006 01:13 GMT

> I've read that OLE DB is the best
> connection technology when working on
> a slow/unstable network because it is
> thread safe. I've also read that only ADO
> supports OLE DB, so I'm limiting myself
> to JET OLE DB and ADO.

What kind of front-end application do you intend to create? If it is
Access, I strongly suggest you use Access MDB, the Jet DB Engine, and DAO.
DAO is the native language of the Jet database engine, is more complete for
Jet than the combination of ADO and ADOX, and is faster than ADO with Jet.

The admittedly-little work that I have done with Access ADP, ADO, and OLEDB
with MS SQL Server did not convince me there is any advantage in that
configuration over using Access MDB, DAO, Jet DB Engine, ODBC, and MS SQL
Server. But I believe that knowledgeable Microsoft insiders (on the Access
development team) now recommend MDB, DAO, Jet, ODBC, and MS SQL Server over
ADPs.

I think someone has sold you a bill of goods, regarding "thread-safe,"
because it has nothing to do with slow/unstable networks. Thread-safe means
that there are features for protecting against interference between multiple
threads of instruction execution in a single application. That is why I
asked elsewhere if you intended to create your front-end in C++ because
Access VBA does not have the ability to create multiple threads of execution
(though you likely could do it by calling library functions in one of the
languages that do, it would be problematical).

What they probably intended to convey was that because a file-server (Jet
for example) does all the work on the user's machine, the back-end database
is subject to "corruption" if the network connection is dropped... leaving
internal pointers set to some intermediate value in the back-end database. A
true server DB (Microsoft or Sybase SQL Server, Informix, and others) does
the work in the server and if the connection is severed, will still complete
what it is doing so the database will not be in an unstable state due to
partially completed operations.

Larry Linson
Microsoft Access MVP

Reply to this Message