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...