[AccessD] Backend database corruption

Hi Janet,

I use this mechanism to speed data updates - I think this is a fairly
well-known technique among experienced developers.

Call this procedure when your FE opens and pass in the word "Connect" to get
started.

Because this is a static database variable, the connection between the FE
and BE stays open.
__________________________________

Public Sub PipelineToMDBBE(stgConnectDisconnect As String)

    '-- Purpose: This will open/close a handle to the BE.mdb data file. _
     Do not explicitly close this database variable. _
     It is here to maintain a connection between FE and BE for better
performance.

    Static dbsBE As DAO.Database
    Dim stgBEFullPath As String

    '-- Connect
    If stgConnectDisconnect = "Connect" Then

        stgBEFullPath = "FullPathToBEFile"
        Set dbsBE = DBEngine(0).OpenDatabase(stgBEFullPath)

    End If

    '-- Disconnect
    If stgConnectDisconnect = "Disconnect" Then
        On Error Resume Next
        dbsBE.Close
        Set dbsBE = Nothing
        On Error GoTo 0
    End If

End Sub
____________________________

Also, I would suggest spacing out the updates.  If all the PC's are updating
the BE tables at the same time, that could be an issue.  Perhaps time the
FE's so that they update a minute apart.

And - this might be a good application to use a SQL Server BE.  You can use
SQL Express (free).  You'll use ODBC table links instead of Access table
links - everything else is the same.

Good Luck!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Janet Erbach
Sent: Thursday, February 19, 2015 14:02 PM
To: Database Advisors
Subject: [AccessD] Backend database corruption

Hello!

It's been years since I've addressed this group, so please be patient with
me while I get back into the swing of this.

I've been an Access developer for the last 15 years or so.  Until recently I
created straightforward apps used on a small group of hardwired networked
computers that had 5 or 6 users in the app at the same time.

Last year I took a job with a large manufacturing plant, and just deployed a
very complex app that I co-wrote with one of the access-fluent production
supervisors.  It is supposed to run non-stop on 20+ machines, all with WIFI
connections.  It writes machine production data to a set of front-end
tables;  every 15 minutes the app checks to see if there is network
connectivity - if there is, the front-end table data is posted to the
back-end tables on the network, the front-end tables are emptied, and the
loop begins again.

The app worked pretty well when it was running on one or two machines.  Now
that it's up on 20 machines, the back end is corrupting multiple times
during the day - which, of course, brings the whole show to a halt.  The
error log seems to indicate that loss of a network connection during the
back-end write operation proceeds the corruption.

I have two questions.  Will hard wiring the network connection to these
machines go a long way towards stopping the corruption?  Is there anything
else that could be contributing to this that I need to be aware of?

Thank you for your help.

Janet Erbach
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com