Network activity when saving local front end


A

AdmSteck

I have a split database with the backend tables on the server and a copy of
the front end on each user's machine. Lately I have been doing some major
upgrades to the front end database on my local copy. The question I have is
why does Access generate a good amount of network activity when saving
updates to a form in my local front end? It takes a considerable amount of
time for this activity to complete and Access is frozen while it waits. Is
there something I can do differently to speed up my save time and decrease
dev time? Thanks.
 
Ad

Advertisements

K

Ken Sheridan

It sounds like Access itself is installed on the server, not locally.
Ideally Access should be installed on each local machine, but even if
that's not possible you should at least install it on your development
machine. The development machine doesn't even need to be on the
network provided you have a copy of the back end locally. For
development purposes the data in the back end doesn't need to be up to
date; in fact using a copy of the back end is advantageous in that you
can test your interface amendments against it without any risk of
damaging the active data.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

If Access is installed locally it doesn't make a lot of sense as I
can't see why it would involve any network traffic at all. What
indicates that its network activity rather than merely local activity?

If it is generating a large amount of network traffic the only
possibility which I can think of is that, despite being installed
locally, a shared installation of Access is being called when you open
your front end. If you are opening it via a shortcut check that it
does not specify a path to a shared installation of MSAccess.exe.

Ken Sheridan
Stafford, England
 
A

Albert D. Kallal

why does Access generate a good amount of network activity when saving
updates to a form in my local front end?

Access has to check all of controls on the form against the actual fields in
the back end table. If you add a field to a table, and then base a form on
that table, you'll notice in code you can do the following:

msgbox "name of company is " & me.Company

If you look at the above we have a field called company. ***Even*** if that
field is NOT placed on the form the above code will continue to operate
correctly. If you remove company from the underlying table then the above
code will (should) NOT compile. In other words the above syntax code shows
that there is significant runtime checking of your code in that form against
fields in the back end table.

(hint: if you're actually program of Cle changing the data source of a form
in code, then you're much better off to use the following syntax that will
not break :

msgbox "name of company is " & me!Company

the Otis the use of the bang "!" in the above, even if the form is unbound,
or the field does not exist in the form yet, the above will continue to
compile.

To make a long story short, the above is one of many reasons that gives an
example of the type of data checking and traffic that occurs when access
checks your form design against the back end table when you save.

Another feature/things to watch out for is if your default printer is a
network printer. Once again MS access calculates and determines layout
information based on the **current** printer that you're using when you're
designing forms and reports. Unfortunately we can print both reports and
forms, so even when you're designing forms a whole whack of printer
information is chattered back and forth between ms-access and your installed
printer. If your default printer is not a local printer then network
activity will occur. For this reason during development if I don't have a
local printer attached to my computer then I simply set my default printer
to a local "pdf" printer I have installed. This will eliminate the
additional network clatter that occurs for printer layout information.
there something I can do differently to speed up my save time and decrease
dev time? Thanks.

Yes, there's two simple solutions to your problem:

1) Simply make a copy of your back end database and place it on your local
computer. Your then free to develop without this delay problem (however this
approach can often "hide" when you do something stupid in your program
design and make the network run slow - you only find out about this when you
re-link back to the backend when you're ready for production use of your
application). By the way, you do deploy a separate front end copy to each
computer right?

Anway, right before you deploy your application, you simply re-link to the
back end on the server.

2) try the following persistent connection trick:

simply open any "linked" table in your front end (I'm talking about a table
that is linked to the back end). Then simply minimize this table and now
start workign/designing any form. When you save it you notice the delay has
gone away!! (try this!!!). There's a really great lesson to be learned here,
and by forcing the connection to STAY OPEN to the back end at ALL times you
significantly increase the performance and responsiveness of your
application.

This persistent trick also works for your end users when you're running the
application. Often you'll see ten times the performance. Thus you REALLY
REALLY REALLY want in your production application **always** force a
persistent connection to be opened at startup time and have it STAY OPEN
during the whole time. Simply open some global recordset variable to a table
or ensure that some form is always opened at all times that based on a table
to the back end.

The reason why the above trick eliminates the significant long delay is that
a significant amount of time occurs to build setup and make the connection
to the back end. When you're saving a form that connection is reopened and
closed many times and it takes longer than a slow boat to China for your
form to save. If you have a table opened and minimized as per above, then
while the form is opening and closing the connection, in fact you forced MS
access to keep the connection open at all times and thus you see the really
amazing increase in performance.

Try the above...
 
A

Albert D. Kallal

Ken Sheridan said:
If Access is installed locally it doesn't make a lot of sense as I
can't see why it would involve any network traffic at all. What
indicates that its network activity rather than merely local activity?

See my response in this thread..there is significant number of reasons why
network activity occurs...
 
Ad

Advertisements

K

Ken Sheridan

I'm sure that what you say is true. I'd always assumed the table in
the back end would only be accessed when the form was opened in form
view, not when the form definition was saved. I'm happy to stand
corrected, however.

I do wonder why this should be the case, though, as it seems to do
nothing useful. The ControlSource property of a bound control can be
changed from ExistingColumn to NonexistantColumn and the form saved
without any error being raised. The linked tabledef object can be
deleted and the form definition similarly amended without any
problem. The table can even be deleted from the back end folder and
the form definition can happily be amended. In each case its only
when the form is opened in form view that a problem arises.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top