G
GeorgieGirl
Will someone please explain the difference between a Multi-User Access
database and a Client/Server Access database?
Thanks
database and a Client/Server Access database?
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pieter Wijnen
MultiUser = more than one user in the db at a time
Client/server=Application logic (forms/reports etc) in one place (client -
Access, vb a.o) Data in another place (Access, MSSQL, Oracle)
Usually you'd want both MultiUser & Client/Server
HTH
Pieter
Client/server=Application logic (forms/reports etc) in one place (client -
Access, vb a.o) Data in another place (Access, MSSQL, Oracle)
Usually you'd want both MultiUser & Client/Server
HTH
Pieter
A
Albert D.Kallal
The difference between client server and what we call a 'file share' is that
in client server, you have a 'SYSTEM' running on the other end that can
accept commands.
A file share:
In a file share, you simply have a file sitting in a shared folder. That
file could be a word file, excel file, or a picture file, or a ms-access
file. In this case, the server has no knowledge about the kind of file here,
and ALL THE SERVER CAN DO is dish out the file. The sever has NO special
knowledge about what is going on. In fact, there is not any difference
between loading a word file on your local hard disk, or loading that file
from the server. The IMPORTANT difference of cause is that a network is now
between you and your file that you open. However, the ONLY new thing here is
a network. The rest is still just a standard file that is opened and read by
a program running on the client pc. Note that to open the word file, or the
Excel file, or the ms-access file on the server, NO SOFTWARE NEEDS TO BE
INSTALLED on the server. So, we don't have anything special sitting on the
server, but ONLY a simply folder that is shared, and you can come along and
open a file. It is important to note that ms-access is quite smart, and if
you have a file with 200,000 records sitting on your local drive c:, and you
request ONE record from the mdb file, ms-access DOES NOT load all 200,000
records, but uses a index, and loads the ONE record. Now, if you move the
mdb file to the file shared folder on the server, the SAME THING happens (in
other words, the whole file is not transfer across the network. Only the
data that ms-access needs to find and load the record is transferred here).
Of course, if the mdb file is sitting on drive c: and a index can not be use
dot find the one record, then ALL records are loaded into memory as they are
searched. And, if you move the file to a file share, then again, all records
will be transferred access the network. So, it is important to note that
NOTHING changes the behavior of ms-access when you have the file sitting on
your local drive, or sitting on a file share server. The ONLY new thing
introduced with the network is in fact that you open the file ACROSS a
network. So, the network does not change the fact if a record needs to be
read into the pc's memory. However, if a record is needed in your computers
memory, then it MUST travel across the network to get to your pc.
Client/Server.
In client server, instead of you opening a actual file on the server side,
you install a server based engine (typically sql server). Then, the client
simply requests a record. The database server takes that request, looks for
the record, and then returns the record to the client). What happens when we
request one record out of 200,000 records? Well, the client sends the
request to the database server. It is this server engine that then uses the
index to find the one record, and return the record back to the client. Note
at this point, both the file share, and the client /server will perform
about the same here. However, lets take the 2nd example where the search
cannot use a index. What happens is the client sends the request to the
server engine. The server engine then has to read ALL of the records into
memory, and find the ONE record. However, note that while this search
occurs, NO NETWORK traffic occurs. No question that the disk drive on the
server goes crazy and spins wildly to find that one record, but these
records are NOT transferred across the network. When the one record is
found, it is then transfer across the network to the client that was just
waiting for a response.
So, the main difference here is that processing can occur on the server side
when you use client/server. When you use a file share, the server side is
100% stupid, and all it is doing is sharing a file. So, if ms-access can
use a index, then not much network traffic will occur in a file share (not
much difference then the client/server). However, if you can't use index,
you can clearly see that all records will get transferred to the client
until the match is found in a file share.
When you start updating lots of records, then sql server really start to
show an advantage. I can go:
update tblCustomers set City = 'New York' where City = 'NY'
Lets assume that out of the 200,000 records, 300 will match the above. In a
file share (with a index), 300 records will be loaded from the mdb file into
memory. Each record will then be updated, and then written back to the file
(note how I on purpose did not mention the network - if a network is
involved, then 300 records go to the client, are updated, and then travel
back tot he file, which in our example is sitting on the server).
Now, lets take the client/server example:
update tblCustomers set City = 'New York' where City = 'NY'
What happens is the above command is sent to the data engine. The data
engine will load the 300 records, update them, and write then back to the
file. However, NO RECORDS need to be transferred to the client on the other
side...it just waits for a response that says...ok..done your request.
So, in some cases, file/share and client/server will run close. But, in some
example, clearly the client/server has a BIG advantage. As you get more and
more users, then the chances of records needing to transfer across the
network will increase. Since sql 'often' will not transfer records accross
the
network, then of course sql server scales to many more users then a file
share.
Further, in a file share, you can damage a file by unplugging your computer
while writing to the data file. In a client/server, the clients NEVER open
the file, and thus corruption from clients is not really possible.
-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
in client server, you have a 'SYSTEM' running on the other end that can
accept commands.
A file share:
In a file share, you simply have a file sitting in a shared folder. That
file could be a word file, excel file, or a picture file, or a ms-access
file. In this case, the server has no knowledge about the kind of file here,
and ALL THE SERVER CAN DO is dish out the file. The sever has NO special
knowledge about what is going on. In fact, there is not any difference
between loading a word file on your local hard disk, or loading that file
from the server. The IMPORTANT difference of cause is that a network is now
between you and your file that you open. However, the ONLY new thing here is
a network. The rest is still just a standard file that is opened and read by
a program running on the client pc. Note that to open the word file, or the
Excel file, or the ms-access file on the server, NO SOFTWARE NEEDS TO BE
INSTALLED on the server. So, we don't have anything special sitting on the
server, but ONLY a simply folder that is shared, and you can come along and
open a file. It is important to note that ms-access is quite smart, and if
you have a file with 200,000 records sitting on your local drive c:, and you
request ONE record from the mdb file, ms-access DOES NOT load all 200,000
records, but uses a index, and loads the ONE record. Now, if you move the
mdb file to the file shared folder on the server, the SAME THING happens (in
other words, the whole file is not transfer across the network. Only the
data that ms-access needs to find and load the record is transferred here).
Of course, if the mdb file is sitting on drive c: and a index can not be use
dot find the one record, then ALL records are loaded into memory as they are
searched. And, if you move the file to a file share, then again, all records
will be transferred access the network. So, it is important to note that
NOTHING changes the behavior of ms-access when you have the file sitting on
your local drive, or sitting on a file share server. The ONLY new thing
introduced with the network is in fact that you open the file ACROSS a
network. So, the network does not change the fact if a record needs to be
read into the pc's memory. However, if a record is needed in your computers
memory, then it MUST travel across the network to get to your pc.
Client/Server.
In client server, instead of you opening a actual file on the server side,
you install a server based engine (typically sql server). Then, the client
simply requests a record. The database server takes that request, looks for
the record, and then returns the record to the client). What happens when we
request one record out of 200,000 records? Well, the client sends the
request to the database server. It is this server engine that then uses the
index to find the one record, and return the record back to the client. Note
at this point, both the file share, and the client /server will perform
about the same here. However, lets take the 2nd example where the search
cannot use a index. What happens is the client sends the request to the
server engine. The server engine then has to read ALL of the records into
memory, and find the ONE record. However, note that while this search
occurs, NO NETWORK traffic occurs. No question that the disk drive on the
server goes crazy and spins wildly to find that one record, but these
records are NOT transferred across the network. When the one record is
found, it is then transfer across the network to the client that was just
waiting for a response.
So, the main difference here is that processing can occur on the server side
when you use client/server. When you use a file share, the server side is
100% stupid, and all it is doing is sharing a file. So, if ms-access can
use a index, then not much network traffic will occur in a file share (not
much difference then the client/server). However, if you can't use index,
you can clearly see that all records will get transferred to the client
until the match is found in a file share.
When you start updating lots of records, then sql server really start to
show an advantage. I can go:
update tblCustomers set City = 'New York' where City = 'NY'
Lets assume that out of the 200,000 records, 300 will match the above. In a
file share (with a index), 300 records will be loaded from the mdb file into
memory. Each record will then be updated, and then written back to the file
(note how I on purpose did not mention the network - if a network is
involved, then 300 records go to the client, are updated, and then travel
back tot he file, which in our example is sitting on the server).
Now, lets take the client/server example:
update tblCustomers set City = 'New York' where City = 'NY'
What happens is the above command is sent to the data engine. The data
engine will load the 300 records, update them, and write then back to the
file. However, NO RECORDS need to be transferred to the client on the other
side...it just waits for a response that says...ok..done your request.
So, in some cases, file/share and client/server will run close. But, in some
example, clearly the client/server has a BIG advantage. As you get more and
more users, then the chances of records needing to transfer across the
network will increase. Since sql 'often' will not transfer records accross
the
network, then of course sql server scales to many more users then a file
share.
Further, in a file share, you can damage a file by unplugging your computer
while writing to the data file. In a client/server, the clients NEVER open
the file, and thus corruption from clients is not really possible.
-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
G
GeorgieGirl
Thanks Pieter and Albert for your quick and thorough replies. I now
understand that I am "walking the middle ground." I have data stored on the
server, using MSAccess with indexed tables, but do not have any special sql
server installed. However, there are just 6 users and only 2 of them will be
accessing the system frequently. So it seems that performance should not be
a problem.
understand that I am "walking the middle ground." I have data stored on the
server, using MSAccess with indexed tables, but do not have any special sql
server installed. However, there are just 6 users and only 2 of them will be
accessing the system frequently. So it seems that performance should not be
a problem.