access BE location best practice

Are there any ideal locations/best practices on where to store the backend database behind access DB databases?

Do you put them on a dedicated server, or is that overkill (thinking cost vs benefit).  Need to investigate the costs of a bare bones  windows based VM with access installed.

We have inherited an access based app, of which the backend of the database is only approx. 100 MB in size, so very trivial really. However, the backend DB is just located on a file server alongside about 1 million other documents. Permissions are restricted to only users of the application, however, but its still on the same drive as the other teams data.

We don't have local admin rights over the server so troubleshooting file locks and the like requires another team to investigate who prioritise such a task accordingly in line with other ICT incidents and it can take hours to resolve something that would take approx. 30 seconds!

Are there any other risks / issues / concerns you can foresee in storing the backend DB on a general file server? I am trying to build a case for moving it, but don't have too much in terms of issues on why to do so, or where too.
Who is Participating?
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.

John TsioumprisSoftware & Systems EngineerCommented:
For such a small database (100Mb) probably you can put it everywhere...personally some old BE i have them running on a small Android TV box running Armbian (samba) and it works just fine...if it had a 1Gb NIC it would be just perfect but even on 100 Mpbs is just fine...
If you are on production ...then ..personally i would invest on a refurbished server ...they can get really ...really low on price (i think the last one i got was an HP with dual processors ,8 gb RAM +1-2 SCSI and it was for $250 ...and all these in Greece..) up the OS...create a folder on the root of the filesystem ...e.g C:\DBs (Access likes short names without folder depth) ...share it...exclude it from Antivirus scanning and it should run great...

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
Gustav BrockCIOCommented:
Keep it where it is. Indeed if you don't want the trouble of maintaining a separate machine, including backup etc.
ste5anSenior DeveloperCommented:
Well, I guess you need to more research for requiring your requirements here.

There are multiple different aspects:

1) Some file servers and their infrastructure are optimized for delivering documents. An Access database is opened and read chunk by chunk when needed. Also such document traffic may have lesser network priority.

2) Recovery procedures in your company. How long does it take to do disaster recovery, when you need to use the fileserver associated procedures? Also your mentioned lock issues.

3) Compliance in general.

You need to assign that an overall cost (for down time etc.) and business use. Then you (your management) can decide the road to go:

- Stick with the current system.
- Get a separate file share on a different system with different IT processes.
- Use a SQL Server (Express is free) backend.
There is no need for a separate server.  Jet/ACE which are the database engines you are using if your BE is Access would be running on YOUR PC and not on the server so it is most important that the local PC have sufficient RAM and speed to handle the application.  

Restricting permissions on the folder holding the BE is important to prevent people from accidently (or deliberately) messing with the BE data.  Better still is to have the BE in a hidden subfolder so not even the authorized users can actually "see" the BE file on the server.  This will prevent anyone from being able to copy the BE from the server to their PC or thumb drive so they can crack it at will.

Because of the way Access works, users must have create/read/update/delete permission for the folder holding the BE file so if they were to navigate to the folder, they could conceivably delete the BE.  So hiding the folder minimizes this risk and you can also change the permissions on the BE file to prevent delete but this will mess up your compact procedure so I don't normally do it.
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

From novice to tech pro — start learning today.