MS access file is getting corupt

I have a Windows AD environment and use DFS to sync three locations.   I want to use a MS Access file and have three computers at one site to do updates to the file.  The rest of the users will have a runtime reader to read only the file.   I've done some testing and the reader seams to lock the file as well.  One location reported a completely  mixed up file.   Am I fooling myself thinking this will work ?
Tim DawsonIT ManagerAsked:
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
I do know what Windows AD or DFS are.
It's an excellent tool, but you can't use it this way, as (by default) any change on any site will be replicated to the other sites within seconds. Thus, the backend file will be changed "behind the scenes" as seen from one of the applications. This will also be true for a reader only.

The way we use it, is to replicate between two sites where - at any time - only one site has active users. That works extremely well.

You should move the backend to SQL Server, or set up Remote Desktop for the remote users.
PatHartmanConnect With a Mentor Commented:
I don't know what Windows AD or DFS are but I do know about Jet/ACE.  It doesn't seem that you are actually using Access at all since you don't seem to have an Access application.  You are just using "Access" as a data store.  Can you clarify please?  People frequently say they are using "Access" because they don't know any better.  Access provides a front end for Jet/ACE similar to what SSMS does for SQL Server.  Access also provides a front end to develop a GUI used by users consisting of forms and reports. The Access application can use any RDBMS that supports ODBC.  It is not reliant on Jet/ACE as the data store.  Access ONLY requires Jet/ACE to store its own application objects - forms, reports, code, macros.  An Access application CAN include local tables but NEVER if it is shared.  ACCESS itself does NOT STORE DATA.  Access is a rapid application development tool. Jet is the .mdb database engine and ACE is the .accdb database engine.

What is a "runtime reader"?  Access offers a runtime engine but it is only used to run Access applications.  You can't actually use it to read a raw database.  It only works if you have developed an Application interface using forms and reports.

The language you are using is not at all what an Access developer would use.  Can you clarify please?
Do you actually have an Access application with forms and reports?
Is the data separated into a separate .mdb or .accdb file?  The app and data should NEVER be in the same table since that would cause multiple people to have to open the application file in a shared mode and that leads to corruption.
Do the users have their own personal copy of the FE (front end - the actual app objects - forms, reports, etc) that has tables linked to a shared BE (back end).  The BE is located in a shared folder on the network.

Two other potential problems that lead to corruption.
1. client PCs have different versions of the Windows OS.  This interferes with the sharing process.
2. is the LAN wired?  WiFi LANs have too much latency and that causes corruption in the Access database because of the dropped connections.  Access cannot smoothly recover from a network blip.  If you have a wireless network, NEVER use Jet/ACE.  You can use Access as the FE but the only way to get stability is to use SQL Server as the BE.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Am I fooling myself thinking this will work ?>>


<< The rest of the users will have a runtime reader to read only the file. >>

 Even if a user only reads a MDB, they are still writing to it.  The process of logging into the database and connecting to it requires write operations.

 As gustav said, either switch to SQL Server for the data store, or have users outside of the main site use an RDP server on the main site to get to the app as a JET/ACE DB cannot be used over a WAN.

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tim DawsonIT ManagerAuthor Commented:
Thanks guys.  That was my fear.  I like the RDP solution, the branches use that anyway for other apps.
We use the full Microsoft Access program to produce the form which is what we share.
Do you think Google drive would do any better ?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Do you think Google drive would do any better ?>>

  No.    A JET/ACE DB must be shared on a Windows OS (or a NAS as long as it can handle the locking), and it must be done over a local LAN.  

 Google Drive, Drop Box, or anything along those lines is a non-starter.

I have a number of clients who use Citrix.  It works extremely well. RDP is also an option as someone already mentioned.  Using Citrix or RDP, if you host the server yourself and it is attached to your LAN will allow people to run the app directly when in the office and via Citrix or RDP when in another location.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.