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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
1
Gustav BrockCIOCommented:
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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Am I fooling myself thinking this will work ?>>

 Yes.

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

Jim.
1
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Jim.
1
PatHartmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.