For over a year, I had been running three Access/Jet FE/BE setups, identical
but separate, in three district offices over the district LANs, and all was
well. Response was for the most part snappy, and the errors that cropped up
were mostly due to coding issues that I was aware of, but just hadn't gotten
to fixing. The file servers were three spare PCs.
I was delighted to find two weeks ago that all of the outdated PCs were to
be replaced with brand spanking new ones, including the PCs used as servers,
as spot replacements had shown to vastly improve the performance of the app,
and errors were most often reported from the users of the oldest and slowest
PCs (Dell Optiplex 110GX w/128 MB).
To my horror, the IT people informed me that local PCs would no longer be
permitted to share folders. The BE mdb files would have to be moved to a
server on a WAN many miles away.
After making some minor adjustments, the app was back in operation - but
now, despite the new PCs, performance on any save or fetch operation has
degraded from near instant, to a 1-10 second wait, depending on network
traffic (doing work after hours, performance is only slightly slower than
before). Errors are more frequent, about as often as were happening before
with those using the slowest PCs - but now rather than just a few users,
all users are affected. Initial startup for the app is the most affected -
it has gone from [slow to snappy depending on PC] to abyssmal. Most of this
can be fixed, I believe, by being more judicious in the timing of updates to
FE tables from the BE - over the LAN, I had updated often, even though the
large table of cities/zip codes might receive only one new city/zip code per
day. It was simply not an issue over the LAN.
Of course, the solution is changing the BE to SQL Server, however, this all
caught me by surprise, and it may be three months or more, in the best case,
before the app can be fully converted. In the meantime, I need to decrease
errors caused by timed-out connections, and minimize traffic over the WAN in
any way that I can.
Generally, the setup is this: most operations are done with one offender at
a time. An offender is represented by several tables, which are bound to
the respective FE forms. When an offender is selected to work on, existing
records for the old offender are cleared from the FE, and records from all
tables concerning the new offender are copied using ADO from the BE to the
identically structured tables in the FE, the local tables being the ones
bound to the forms. Changes to each form/table for that offender are saved
only on pressing the Save button or switching to a new form.
The following is a snippet of the load function for an offender (actually
about 20 tables).
Dim cnxn As ADODB.Connection
Set cnxn = CurrentProject.Connection
SetOffenderID id
ClearTables
With cnxn
.Execute "INSERT INTO offender " & _
"SELECT * FROM offender IN \\server\odis.mdb " & _
"WHERE offender_id=" & id, , adCmdText
.Execute "INSERT INTO photo " & _
"SELECT * FROM photo IN \\server\odis.mdb " & _
"WHERE photo_off_id=" & id, , adCmdText
.Execute "INSERT INTO address " & _
"SELECT * FROM address IN \\server\odis.mdb " & _
"WHERE deleted = False AND add_off_id=" & id, ,
adCmdText
End With
I don't know what advice I'm looking for - certainly few would voluntarily
put themselves in my predicament, so few would/should have experience with
Jet-over-WAN, but perhaps something creative? I don't want to do too many
changes on the short term solution, as I prefer to spend the bulk of my time
on the long term solution of switching to SQL Server, but I'm open to
suggestions ...
--
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
