Access Split Database Data Handling

[DanZaMan]

Just a query about how clever access is, I'm using access 97.

When using a "split" database with a separate front-end and with the
data file on a separate server on the network how does access handle
the data; Does it transfer all the data across the network and process
it locally on the front end or does it process it on the server the
data file is held on?

If I put my queries in the access data file on the server would this
result in the queries being run on the server?

[ManningFan]

Access is Client-Side, SQL Server is Server-Side. Access will bring
all the information to your local computer.

DanZaMan wrote:

Just a query about how clever access is, I'm using access 97.

When using a "split" database with a separate front-end and with the
data file on a separate server on the network how does access handle
the data; Does it transfer all the data across the network and process
it locally on the front end or does it process it on the server the
data file is held on?

If I put my queries in the access data file on the server would this
result in the queries being run on the server?

[Lyle Fairfield]

"DanZaMan" <da**********@gmail.comwrote in
news:11**********************@s34g2000cwa.googlegr oups.com:

Just a query about how clever access is, I'm using access 97.

When using a "split" database with a separate front-end and with the
data file on a separate server on the network how does access handle
the data; Does it transfer all the data across the network and process
it locally on the front end or does it process it on the server the
data file is held on?

If I put my queries in the access data file on the server would this
result in the queries being run on the server?

I suppose you could test these:

For the data transfer you could time opening a recordset of all the
records in a large table.
Then you could time opening a recordset where ID = 1 or whatever
condition and compare the two times.
If ID is not indexed you could try indexing at and seeing if that has an
effect on speed.

Here's my prediction but I don't use JET backends anymore and I haven't
used JET 3.5 (Acces 97) since the last century (so it's strictly a
prediction and I could be confused with JET 4.0):
If there's an index on ID Access/JET will being that index across the
wire. From the index it will identify the pointers to the records that
should be retrieved. It will then bring those records only across the
wire.

----

For the query on the server you could take a look at the server's Task
Manager while the Front End on the Client machine ran one of those
queries. My guess is that for the Backend DB to run the query, JET or
Access will have to open as a process on the Server (some program/process
will have to do the work?). You should see one of them pop up.
But my prediction is that you won't.

Of course you could just accept the responses you get here. Sometimes
they're right. Sometimes they're not.

--
lyle fairfield

[Rick Brandt]

ManningFan wrote:

Access is Client-Side, SQL Server is Server-Side. Access will bring
all the information to your local computer.

That should say "Access will bring all the *required* information to your
computer."

It will read from the file on the server exactly the same bytes as it would
read if the file were on a local disk. The server is merely acting as a
remote hard drive.

Some people think that all data from all tables used in the query is brought
over and only then is the query processed on the local PC. That is
certainly NOT what happens.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Larry Linson]

"Lyle Fairfield" <ly***********@aim.comwrote

... and I haven't used JET 3.5 (Acces 97) since the
last century

Hmm. I thought you hadn't used it since BPAT (Before Positive Access Time),
that is, prior to Dec. 30, 1899.

(so it's strictly a prediction and I could be confused
with JET 4.0): If there's an index on ID Access/JET
will being that index across the wire. From the index
it will identify the pointers to the records that should
be retrieved. It will then bring those records only
across the wire.

If memory serves, this is an accurate description of Jet as far back as
16-bit Jet and right up through Jet 4.0 with all 8 (or however many there
are now) SPs.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:v3Uoh.5051$%Q4.1631@trnddc06:

If memory serves, this is an accurate description of Jet as far
back as 16-bit Jet and right up through Jet 4.0 with all 8 (or
however many there are now) SPs.

Nine.

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

[Albert D. Kallal]

Just to help this,

Think of a Excel sheet you place on that server, but that server does NOT
have excel installed.

When you run and load excel on your computer, does the server magically now
start running excel?

the same applies to ms-access. The processing, and code, and execution
occurs on YOUR machine. In fact, your "back end" mdb file is placed on that
server, but you never did have to install ms-access on that server.....(so,
how is it that server going to run ms-access when ms-access never was
installed????).

Of course, ms-access is smart, and DOES NOT always read in the whole file
from the disk drive. So, if you have 100,000 records, and retrieve ONE
record, then ms-access can use index to determine "WHERE" on the disk drive
that ONE record is..and then simply requests the disk drive to ONLY load
that part of the file.

The fact of the file being on drive "c:"....or some network share DOES NOT
change how ms-access reads ONLY that one part of the file....

So, if ms-access has to read "all" of the file when the mdb is on drive c:,
then it will continues to do so if you put the mdb on a file share.

So, if ms-access has to read the whole file, and a network is between you
and the file...the obviously the whole file has to pass "through" that
network.

Since ms-access does it best to use index, and read as little data as
possible, then often the WHOLE table is NOT transferred.

However, all data, and all processing occurs on your computer, and that mdb
file is just plain Jane file sitting on he disk drive. The fact of that disk
drive be on your local c: drive, or a drive halfway across the world that
you opened...does NOT change the behaviours as to how ms-access runs.....

ms-access does not *always* read in the whole table, so therefore, the whole
table is not transfered from that file into your comptuers memeory. What
that file is located does NOT change this behavour....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[DanZaMan]

Brilliant, this has helped loads! It explains why SQL servers (Oracle,
SQL server, MySQL) are better for multi user apps.