Sprinks said:
I gather then that I need to set the Record-Locking strategy for the back-end.
Yes.
What do you mean by a "persistent connection" to the back-end? Do you mean
that someone has the database open at all times?
It means that as soon as the front end opens, a connection is made to the
back end, and that connection remains open until the front end closes. This
can be done with a hidden, bound form that uses a table linked to the back
end, which opens at startup.
And how does such a
connection speed operations and by how much?
Every time the front end connects to the back end by opening a linked table
(with a query, bound form, bound report, VBA code, etc.), Jet has to use
database locking mechanisms to safeguard the data. Jet uses a locking
database file (LDB) for this. If the LDB file doesn't already exist, it is
created after Jet reads and deciphers the information from the database file
that it needs to write in the LDB file. This takes time. When the
connection to the back end is no longer needed, the LDB file is deleted.
This takes time. But before Jet comes to the conclusion that the connection
to the back end is no longer needed, it must first go through the motions to
determine that the connection is no longer needed. This takes time, too.
These steps can take as long as one or two coffee sips, or as long as a trip
to the break room for a fresh cup of coffee. It's fastest if both the front
end and back end are on your own hard drive, but if you find yourself in need
of excuses for coffee breaks, here's all you have to do:
1) Don't use a persistent connection.
2) Bury the back end file deep in the directory structure on a network
server, like 10 or more directories deep. The network security API's have to
be consulted at every directory level to check what permissions the user has
for accessing that directory and its subdirectories and files. If the back
end is in the root directory, these network security API's only have to be
consulted once.
3) Use more than 8 characters or a non-alphanumeric or non-underscore in the
name of each directory in the path of the back end file. Another Windows API
must be consulted to resolve the "LongFileNames" (non-DOS naming convention)
and passed to the network security API's for every directory in the path of
the back end. If you use the 8.3 DOS naming convention with only
alphanumeric characters and underscores, the LongFileNames Windows API won't
have to be consulted.
4) Use more than 8 characters or a non-alphanumeric or non-underscore in the
name of the back end file. The Windows API for "LongFileNames" must be
consulted yet again.
And if you want to have extra time to stop by the water cooler to catch up on
the latest office gossip on the way back from the break room with your fresh
cup of coffee, you can do the following:
1) Put the back end file on a domain server in another Windows domain on the
network so that the network API's have to be consulted to resolve permissions
for users on other network domains connected to that domain.
2) Change the auto-negotiate 100 Mbps network setting to half-duplex 10 Mbps.
Don't laugh. People really use all of these techniques and then complain,
"Access is too slow. We need to upgrade to SQL Server."
