Split Database Pros and Cons

On Wed, 24 Dec 2008 16:01:19 +0000, Donald Calloway wrote:

> I'm hoping someone can answer these questions for me.
>
> As I understand the split database arrangement, the back-end is all that
> gets transmitted via the network when data are created, modified or
> deleted, whereas the front-end is the non-changing aspect of the RDBMS
> that resides on each multi-user client PC: forms, queries, reports,
> macros and modules.
> The database fingerprint on the shared server would thus consist of only
> the data,
> and would be considerably smaller than the totality of the database that
> resides there now.
>
> Now for my first question: Aside from speeding up data transfer, what
> other advantages would I hope to realize in a multi-user environment?

The biggest is that NOT doing so makes it much more likely that the file/
data will become corrupted.

> Now my second question: In a split-database scenario with multi-user
> environment,
> if I need to make changes to the functionality of the database or some
> cosmetic
> improvements; for example, updating a form's appearance or modifying its
> functionality, or improving on the design of a query, macro, report,
> etc., would I not have to deploy the changes to all client PCs
> individually rather
> than the way it is handled now, i.e., taking the system down, making the
> changes
> and then bringing the system back on line?

Yep, and that goes in the advantages column. Why take the system down?
What if the change ends up taking a long time to complete? Distributing
changes to all users is easily solved in a number of ways that completely
automatic and involve no extra work (once set up) for the users or the
developer.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
 >> Stay informed about: Split Database Pros and Cons 

"Donald Calloway" wrote in


> Aside from speeding up data transfer, what
> other advantages would I hope to realize in a multi-user
> environment?

Well, the fact that it just doesn't work to share a non-split MDB
should pretty much tell you that it's a bad idea. It causes
corruptions and crashes.

What more convincing do you need?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 >> Stay informed about: Split Database Pros and Cons 

Although I am not using a Db over a network, I use the same front end with
different back ends (Different Clubs / organisations that I am involved
with). As a result both BE & FE are on a number of different Computers in
those organisations so it is similar to a network situation.

In my front end DB I have a table of modifications with details of the mods
and a TableNo. In the backend I have a table of TableNos. If the last
TableNo in the FE is differen from the last TableNo in the BE I throw out an
error message and stop the main menu showing. That ensures that both DBs are
"In Sync"

Phil


"Donald Calloway" wrote in message

> I'm hoping someone can answer these questions for me.
>
> As I understand the split database arrangement, the back-end is all
> that gets transmitted via the network when data are created, modified or
> deleted, whereas the front-end is the non-changing aspect of the RDBMS
> that
> resides on each multi-user client PC: forms, queries, reports, macros and
> modules.
> The database fingerprint on the shared server would thus consist of only
> the data,
> and would be considerably smaller than the totality of the database that
> resides there now.
>
> Now for my first question: Aside from speeding up data transfer, what
> other advantages would I hope to realize in a multi-user environment?
>
> Now my second question: In a split-database scenario with multi-user
> environment,
> if I need to make changes to the functionality of the database or some
> cosmetic
> improvements; for example, updating a form's appearance or modifying its
> functionality, or improving on the design of a query, macro, report, etc.,
> would I not have to deploy the changes to all client PCs individually
> rather
> than the way it is handled now, i.e., taking the system down, making the
> changes
> and then bringing the system back on line?
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 >> Stay informed about: Split Database Pros and Cons 

"Donald Calloway" wrote:

>As I understand the split database arrangement, the back-end is all
>that gets transmitted via the network when data are created, modified or
>deleted,

Correct, although not the entire BE if the tables are proprely indexed.

>whereas the front-end is the non-changing aspect of the RDBMS that
>resides on each multi-user client PC: forms, queries, reports, macros and
>modules.

Well, these do change as the user requirements change or they ask for new features.

>The database fingerprint on the shared server would thus consist of only
>the data,
>and would be considerably smaller than the totality of the database that
>resides there now.

Somewhat smaller although that varies from situation to situation.

>Now for my first question: Aside from speeding up data transfer, what
>other advantages would I hope to realize in a multi-user environment?

Actually performance will be worse unless you follow the tips.

Performance is worse after splitting - My personal experience
aka How to speed up complex forms and reports with many records each with subreports.
http://www.granite.ab.ca/access/splitapp/performance.htm

The three most common performance problems in Access 2000 or newer are:
- LDB locking which a persistent recordset connection or an always open bound form
corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]

For more information on these, less likely causes, other tips and links to MS KB
articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

>Now my second question: In a split-database scenario with multi-user
>environment,
>if I need to make changes to the functionality of the database or some
>cosmetic
>improvements; for example, updating a form's appearance or modifying its
>functionality, or improving on the design of a query, macro, report, etc.,
>would I not have to deploy the changes to all client PCs individually
>rather
>than the way it is handled now, i.e., taking the system down, making the
>changes
>and then bringing the system back on line?

Correct. As already suggested see the free Auto FE Updater. at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up
to date.

Tony

--
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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 >> Stay informed about: Split Database Pros and Cons 

Split databases seem to be less prone to corruption.

If you have auto-relinking code, then it is simpler to test and
develop with separate backends and front ends.

Most of my clients have a development, test and uat environment as
well as a production one.

99% of the changes are the front end, so it is a piece of cake to roll
out a new version.

Regards,
Tom Bizannes
Access and Sql Server Specialist
Sydney, Australia
 >> Stay informed about: Split Database Pros and Cons 

you can sit around and say 'less prone to corruption'

but less prone to corruption still means _VERY_PRONE_TO_CORRUPTION_

From Microsoft.com

----------------------------------------------------------------------------------------
Access, however, is not perfect. Performance degrades significantly as
the database size increases. The database is also prone to corruption.
Finally, starting with an Access database has tempted many developers
to do a dangerous thing. Sometimes a single-user application becomes
popular enough that there's a desire for it to be used by multiple
simultaneous users. The temptation is to just move the Access database
file to a network share, copy the application to multiple machines,
and let many users connect simultaneously. Access performance drops
off quickly with multiple users, and it's highly unlikely that an
application that was designed for a single user will work reliably
with concurrent users.

Http://msdn.microsoft.com/en-us/library/aa730870(VS.80).aspx
----------------------------------------------------------------------------------------


On Dec 27, 2:44 am, SmartbizAustralia wrote:
> Split databases seem to be less prone to corruption.
>
> If you have auto-relinking code, then it is simpler to test and
> develop with separate backends and front ends.
>
> Most of my clients have a development, test and uat environment as
> well as a production one.
>
> 99% of the changes are the front end, so it is a piece of cake to roll
> out a new version.
>
> Regards,
> Tom BizannesAccessand Sql Server Specialist
> Sydney, Australia
 >> Stay informed about: Split Database Pros and Cons 

" " wrote:

>From Microsoft.com

And that author clearly doesn't understand Access.

Tony
--
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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 >> Stay informed about: Split Database Pros and Cons 

Well, despite what you say, I've been using a non-split database in my
office at work for two years now with virtually no problems. I compact
and back it up every day.

On Wed, 24 Dec 2008 16:58:44 -0500, David W. Fenton
wrote:

> "Donald Calloway" wrote in
>
>
>> Aside from speeding up data transfer, what
>> other advantages would I hope to realize in a multi-user
>> environment?
>
> Well, the fact that it just doesn't work to share a non-split MDB
> should pretty much tell you that it's a bad idea. It causes
> corruptions and crashes.
>
> What more convincing do you need?
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 >> Stay informed about: Split Database Pros and Cons 

I have a question about database splitting. I have two databases
operating in a multi-user environment, with the database with the GUI
switchboard form linked to another database so it's data can be accessed
through the GUI. If I split the main database and link its Tables in the
FE to the Tables of the BE on the server, must I also split the currently
linked database and link its FE Tables to its BE Tables? I would envision
this would mean I would have two FE databases (linked as before) with each
linked to their respective BE databases. Is this correct?

On Wed, 24 Dec 2008 20:02:43 -0500, Tony Toews [MVP]
wrote:

> "Donald Calloway" wrote:
>
>> As I understand the split database arrangement, the back-end is all
>> that gets transmitted via the network when data are created, modified or
>> deleted,
>
> Correct, although not the entire BE if the tables are proprely indexed.
>
>> whereas the front-end is the non-changing aspect of the RDBMS that
>> resides on each multi-user client PC: forms, queries, reports, macros
>> and
>> modules.
>
> Well, these do change as the user requirements change or they ask for
> new features.
>
>> The database fingerprint on the shared server would thus consist of only
>> the data,
>> and would be considerably smaller than the totality of the database that
>> resides there now.
>
> Somewhat smaller although that varies from situation to situation.
>
>> Now for my first question: Aside from speeding up data transfer, what
>> other advantages would I hope to realize in a multi-user environment?
>
> Actually performance will be worse unless you follow the tips.
>
> Performance is worse after splitting - My personal experience
> aka How to speed up complex forms and reports with many records each
> with subreports.
> http://www.granite.ab.ca/access/splitapp/performance.htm
>
> The three most common performance problems in Access 2000 or newer are:
> - LDB locking which a persistent recordset connection or an always
> open bound form
> corrects (multiple users)
> - sub datasheet Name property set to [Auto] should be [None]
>
> For more information on these, less likely causes, other tips and links
> to MS KB
> articles visit my Access Performance FAQ page at
> http://www.granite.ab.ca/access/performancefaq.htm
>
>> Now my second question: In a split-database scenario with multi-user
>> environment,
>> if I need to make changes to the functionality of the database or some
>> cosmetic
>> improvements; for example, updating a form's appearance or modifying its
>> functionality, or improving on the design of a query, macro, report,
>> etc.,
>> would I not have to deploy the changes to all client PCs individually
>> rather
>> than the way it is handled now, i.e., taking the system down, making the
>> changes
>> and then bringing the system back on line?
>
> Correct. As already suggested see the free Auto FE Updater. at
> http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE
> on each PC up
> to date.
>
> Tony
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 >> Stay informed about: Split Database Pros and Cons 

My office has used an application, which I personally developed, in a
multi-user environment (with c. 25 simultaneous users at any one time) for
about two years now with little to no problems encountered. It is not
deployed as a FE/BE, rather the linked databases reside on the shared
server. I do follow a strict backup regimen just in case there is ever a
problem, and my database compresses itself daily when the last users
disconnects. The largest of about 10 tables in the main database alone
contains over 30,000 records, and the main database is linked to a second
database on the server so its data can be access through the main
database's GUI (switchboard form). I've not noticed much, if any,
degradation in performance in the two years the database has been
operational. I, therefore, concur with the MVP's comments, and totally
disagree with the author's comment regarding degradation of performance
and greatly increased possibility for database corruption.

On Sat, 27 Dec 2008 19:43:03 -0500, Tony Toews [MVP]
wrote:

> " " wrote:
>
>> From Microsoft.com
>
> And that author clearly doesn't understand Access.
>
> Tony



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 >> Stay informed about: Split Database Pros and Cons 

"Donald Calloway" wrote

> Well, despite what you say, I've been using a
> non-split database in my office at work for two
> years now with virtually no problems. I compact
> and back it up every day.

I don't know what you are doing in this database, but then if Microsoft knew
an exact configuration that would assuredly corrupt, they would be working
on correcting that. The fact is that any day, you may begin to experience
corruption because having multiple users logged in to the same monolithic,
or front-end, database does significantly increase the chances of
corruption.

Also, the fact is, that each user is retrieving each database object (query,
form, report, macro, or module) across the network, as well as data from the
tables. Whether you observe it or not, that is an increase in your network
traffic, and a potential performance problem if you are "really" making use
of the network. Because it is increased network traffic causing the
performance problem, it will affect all programs making use of the network.

Larry Linson
Microsoft Office Access MVP
 >> Stay informed about: Split Database Pros and Cons 

"Donald Calloway" wrote in message

> My office has used an application, which I personally developed, in a
> multi-user environment (with c. 25 simultaneous users at any one time) for
> about two years now with little to no problems encountered. It is not
> deployed as a FE/BE, rather the linked databases reside on the shared
> server. I do follow a strict backup regimen just in case there is ever a
> problem, and my database compresses itself daily when the last users
> disconnects. The largest of about 10 tables in the main database alone
> contains over 30,000 records, and the main database is linked to a second
> database on the server so its data can be access through the main
> database's GUI (switchboard form). I've not noticed much, if any,
> degradation in performance in the two years the database has been
> operational. I, therefore, concur with the MVP's comments, and totally
> disagree with the author's comment regarding degradation of performance
> and greatly increased possibility for database corruption.

Donald, you are perfectly entitled to your opinion. My opinion is that
results from two database applications is far from "statistically
significant".

Many of us here have opportunity to observe quite a few databases, of our
own, our clients, and people we are helping -- and the concensus here, among
experienced users, is that multiple users logged in to the same monolithic
or front-end database significantly increases the probability of corruption.
In fact, we have seen or had reported many projects where frequent
corruption ceased to be a problem when a database was split and each user
was given their own copy of the front-end.

In Access 2.0 days, I worked on some projects which used linked tables on an
Informix server, with the front-end on a LAN server, with multiple people
logged in to that (development) front end, making design changes to the
objects. In five years, there were few "bad occurrences" that could have
been blamed on multi-user logins, but there were a few that were unexplained
otherwise, too. That does not mean it was "perfectly OK" to do so, only
that we were lucky not to encounter problems -- and, in fact, in 32-bit
Access, at least beginning with Access 2000, Microsoft removed the
capability for multiple people to be making concurrent design changes by
requiring "exclusive access" for design changes.

I wish you much luck, and that it may be good luck, with your database
application. Just be aware, should that day come when you make some minor
changes and begin to experience frequent corruption, splitting your database
will likely be the first piece of advice you receive.

Larry Linson
Microsoft Office Access MVP
 >> Stay informed about: Split Database Pros and Cons 

Good to see that David hasn't lost any of his tact and diplomacy.
 >> Stay informed about: Split Database Pros and Cons