Load tables to the local drive

  • Thread starter Thread starter Malvina
  • Start date Start date
M

Malvina

I would like to create a mechanism, where on opening the
database from the server, all my tables would be loaded to
the user's local drive. When the user closes the db all
the updates have to go back to server. The reason for me
doing that is, because the traffic on the server is so
bad, and the process of navigating within the db is VERY
SLOW.
I can visualize that it's possible, but I need to figure
out how and where to start. May be some simple code, or
example.

Thank you for the reply in advance,
Malvina
 
Malvina -
For your sake, and those who come after you, think very carefully before
implementing a solution like this. I work with an application that does
exactly what you are suggesting, and the solution was implemented for
the exact same reasons you are describing. The solution is the cause of
almost all of the bugs in the application and has made maintenance and
upkeep absolute hell.

Find out why the communication is slow. Maybe you have exceeded the
limits of what Access can do. How many users do you have? Are you
connecting to an SQL server for the server DB? Is the server DB Oracle
using Jet and ODBC? Remember that Access (Jet) is a file/server
solution, the computation of queries is (mostly) done on the local
machine, this means that all the data needed to perform a join or query
needs to be sent across the network. If your database is sufficiently
large, there could be a huge amount of data being transferred just to
query out 1 or 2 records. If you have a SQL or Oracle backend, you may
be able to re-write your queries or use stored procedures or views to
minimize network traffic.

Another consideration is concurrency. What happens if two people make
changes to the same record? For example, if user A makes a change, but
does not upload the change to the server, then user B makes a change,
that is now based on outdated data. User A uploads his changes, then a
minute later user B uploads her changes, overwriting user A's updates.
You will very quickly find yourself unable to trust the data.

If you have more questions about why your application is slow please
post a bit more about the structure and design. We might be able to come
up with ways of speeding it up without the complications of multiple
versions of the database.

HTH
Ben
 
I found out that the main (client info form) form takes
the most time to be loaded - opened. I open simple forms
without no problems, and not time consuming at all. My
clients (users) choose to have most of the info in one
location. My client form has about 8 tabs, and of course
lots of subforms. That being said, the rs data is being
read from the server. For those users, who are on the
same server as the db, there is no problem to navigate
within the db. The other users, who are on a different
server then the DB, experience low time navigation.

I strongly feel that it's because of the network
communication.

May be any other suggestions?
Thank you for your reply in advance.
Malvina
 
I found out that the main (client info form) form takes
the most time to be loaded - opened. I open simple forms
without no problems, and not time consuming at all. My
clients (users) choose to have most of the info in one
location. My client form has about 8 tabs, and of course
lots of subforms. That being said, the rs data is being
read from the server. For those users, who are on the
same server as the db, there is no problem to navigate
within the db. The other users, who are on a different
server then the DB, experience low time navigation.
Click to expand...

Work on this Form - you're right, it's the problem!

One useful technique for a "heavyweight" form like this is to have all
the Subforms' Source Objects blank in the form design. In the Change
event of the tab control, set the Source Object of each subform on the
page being left to blank, and those of the subforms on the new page to
the proper form name. This will populate the subforms only when
they're needed.

And, of course, splitting the form up into several forms with
navigation links is worth considering!

For some more detailed performance suggestions see Tony's excellent
http://www.granite.ab.ca/access/performancefaq.htm
 
Back
Top