Difference between client server and shared access

[James]

Can someone explain the fundamental difference between creating a "multi
user" version of an Access DB and creating a client-server Access DB?

ie why can't all the users on my network just click on database.mdb and
share the same database? why would I want to create a separate client/server
architecture?

[DeanL]

The simple answer is network bandwidth.

If you create a database and place it on a server then everything has
to "come down the pipe" so to speak. Forms, reports and all the
bandwidth intensive stuff (and that's even before you start sending
data down the wire. If you split your database into a tables database
and place that on the server and then have everything else in a GUI
client that links to the tables then all you're brining through the
network is the data. This should be considerably faster (talking from
personal experience having done both).

Hope this helps.

[Larry Linson]

"James" <pr*****@replyToGroup.com> wrote in message
news:Ma*******************@newsfe6-win.ntli.net...

Can someone explain the fundamental difference between creating a "multi
user" version of an Access DB and creating a client-server Access DB?
In the Access world, "multiuser" means a Database done in Access, using the
supplied Jet database engine, with multiple users. Access can also be used
as a "client" to server databases such as Microsoft or Sybase SQL Server,
Informix, DB2, and Oracle by using an appropriate Open DataBase Connection)
ODBC driver with an Access .MDB and Jet, or it can be a client as an Access
"Project" .ADP file with Microsoft SQL Server only via the OLEdb driver.
ie why can't all the users on my network just
click on database.mdb and share the same
database? why would I want to create a separate
client/server architecture?

Firstly, DeanL was correct as far as he wrote, but left out a very important
part: if you have multiple users concurrently connected to the same "front
end" or monolithic database, you significantly increase the chances of
database corruption. Such corruption is _usually_ correctable by the Compact
and Repair function, but not always.

Secondly, there is a hard limit of 255 concurrent users in an Access
multiuser environment, but a practical limit on users doing updates of
something less than 100. With either the Access-Jet-ODBC-serverDB or
AccessProject-OLEdb-SQLServer arrangement, there is essentially no limit on
the number of concurrent users, no matter whether they are editing or just
reading.

Larry Linson
Microsoft Access MVP

[Dave G]

Mind if I slip in an ask an associated question?

My setup is back end data in a .mdb on a server and access front ends
on each workstation, about 20 by the way. Suppose I change to mySQL
back end and 'talk to' the back end using ODBC, don't make any changes
on the FE but simply re-link the tables. Have I gained anything?

Or do I only gain by actually changing my front end in some way to make
best use of an sql back end ?

Thanks
Dave

[BillCo]

My tu'pence:
1. Running code over a server connection is significantly slower than
running a desktop application
2. Local DB frontend means it's possible to have local tables for
holding user settings and
3. local temp tables for efficiantly manipulating large amounts of data
4. Signifigant decrease in the chances of user conflicts / data
corruptions
5. Stuff breaks from time to time - toolbars corrupt etc... far easier
to replace one user's FE from a template than if everyone's database is
suddenly broken
6. Multiple data sources / data source switching - one local front end
can pull together many other databases at once or swap into a data
archive without effecting other users
7. Customisation - different depts have different needs and
permissions, which can be served by different FE's into the same
backend data.
8. I could keep going all day... but it just makes far more sense to
have a local front end where there are more than a couple of users, or
speed is a requirement

[Keith Wilby]

"James" <pr*****@replyToGroup.com> wrote in message
news:Ma*******************@newsfe6-win.ntli.net...

Can someone explain the fundamental difference between creating a "multi
user" version of an Access DB and creating a client-server Access DB?

ie why can't all the users on my network just click on database.mdb and
share the same database? why would I want to create a separate
client/server architecture?

To add to all the other comments, another important point is that, once your
application is split you can work on your FE safe in the knowledge that you
can deploy updated versions to your users without it affecting their data
(unless of course you need to make a table change).

Keith.
www.keithwilby.com

[David W. Fenton]

"Dave G @ K2" <da**@k2computers.co.uk> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

Mind if I slip in an ask an associated question?

My setup is back end data in a .mdb on a server and access front
ends on each workstation, about 20 by the way. Suppose I change to
mySQL back end and 'talk to' the back end using ODBC, don't make
any changes on the FE but simply re-link the tables. Have I gained
anything?

Or do I only gain by actually changing my front end in some way to
make best use of an sql back end ?

The question is simply not answerable.

If your Access-only application was written to retrieve data very
efficiently, you will likely gain nothing. If it was inefficient, it
might gain you some performance.

But it depends entirely on the specifics of your application.

I also don't think I'd ever contemplate MySQL as a back end for an
application other than a web page, even with InnoDB tables (with the
native MySQL ISAM, it's not appropriate to any significant database,
as there's no engine-level referential integrity). If you want a
professional server database that is open source and free, I'd go
with PostgreSQL. Quite a few people in this newsgroup work with it
from Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Dave G]

Thanks for that. The reason for my asking is that I have a new
requirement whereby a remote Linux based web server will need to make
entries into my Access database. One option was to convert the BE to
mySQL so that the web server can talk to the database directly. Based
on your response I will take a good look at PostgreSQL. I too have
heard some good things about it but have never tried it

Thanks
Dave

[David W. Fenton]

"Dave G @ K2" <da**@k2computers.co.uk> wrote in
news:11*********************@g43g2000cwa.googlegro ups.com:

Thanks for that. The reason for my asking is that I have a new
requirement whereby a remote Linux based web server will need to
make entries into my Access database. One option was to convert
the BE to mySQL so that the web server can talk to the database
directly. Based on your response I will take a good look at
PostgreSQL. I too have heard some good things about it but have
never tried it

Well, depending on what server-side language is running on Linux,
perhaps you can use an ODBC driver to write directly to the Access
database, without needing to change db engines at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/