Simple database with quite a few users - Access or SQL Server?

[mahiyu]

Hi



I am looking to build a fairly simple (at least initially) database and I'm wondering whether it's best to use Access or SQL Server for the actual database.

The company that I work for has lots of Excel spreadsheets stored in various places for logging various types of incoming work. What I would like to do is consolidate them into a single database - apart from only having one place to go to to update logs, it should give us the ability to extract MI regarding who is doing what types of work, what's currently oustanding etc (which isn't practical with the spreadsheets we are using at the moment.) My intention is to have a couple of Access front-ends - one for general users and one for management with reports for MI.

It should be a fairly straightforward database initially, and would probably have about 15 concurrent users initially (with a few others who might be dipping in to have a look at the progress of particular transactions, run reports etc.) This number is likely to grow over time.

We are using a terminal services server running Windows Server 2012R2 (64-bit) which is accessed over RDP - Access 2013 (32-bit) and the full version of SQL Server 2012 are installed. The back-end database and front-end would be physically located on the same server.

I'm sure that Access would be adequate for the job (and I have basic Access knowledge already), but would there be any benefit in having the back-end in SQL Server? Would it make a difference for such a basic database? I can't see that we're likely to hit Access's 2GB database limit anytime soon.

Apologies if this seems to be a basic question - even such a simple database will take me a while to design and build, so I want to make sure I use the right tool from outset!

Many thanks.

[JoeM]

As long as you have split the database and are doing regular backups of the back-end, I see no issue with using Access.
SQL is more powerful and a bit more stable, (and I believe it handles record locking a bit more gracefully) so all things equal, it might be more preferable. But if you are much more comfortable with Access, I say go for it. I usually keep it in Access if it is fairly small and basic.

Just make sure that everyone has their own copy of the front-end to avoid issues.

[mahiyu]

Thanks for the reply - Access sounds like the way to go. Will there be any problems in having quite a few users on the database at once, and am I right in saying that it's fairly straightforward to migrate the back-end to SQL Server at a later date if we need to?

Is it critical to give everyone their own copy of the front-end? Since we are on a remote server I was planning on having one copy that everyone could use (plus a "management" version with additional reports.) This would save having to make sure every user had the latest version if I made any changes later on - though it would be possible to give a separate copy if need be. We already have a system in use (which was developed by an external developer) which uses a single Access front-end for multiple users, albeit connecting to a SQL Server rather than Access back-end database, and as it was developed by a professional it might be structured better than my creation!

Many thanks.

[JoeM]

Access sounds like the way to go. Will there be any problems in having quite a few users on the database at once,

How many are we talking here? If it is a split database, and everyone has their own front-end, that will minimize any issues.

am I right in saying that it's fairly straightforward to migrate the back-end to SQL Server at a later date if we need to?

Yes, as long as you don't use features which are not compatible with SQL. The only one that I know of is the Multi-Valued Field option in Tables that newer versions of Access offers. Don't use it!!! It is not compatible with any other database programs.

Is it critical to give everyone their own copy of the front-end?

I would say yes. If people have different versions of Access, it is an issue. If it becomes corrupt, it is an issue. If it gets locked and you cannot release it, it can be an issue. If you want to upgrade it and someone has it locked open, it can be an issue.

There is actually a really easy solution to this which makes it really easy to update and to make sure everyone is using the most current version.
1. Create the front-end version and put it on the network, but don't tell anyone where it is.
2. Create a batch file (or use your favorite scripting program, like vbscript or AutoIT3) that:
- copies the front-end off of the network down to a folder on the C drive everyone has (i.e. Programs, or Temp, or if your work has some other folders all users have),
- then opens that copy of the front-end
3. Distribute the batch/script file you created above to users and have them use that to run the database

So every time they run it, they are getting a fresh copy of the front-end. And any time you need to update the front-end, you simply replace the secret copy you have on the network that the batch file copies. You just have to make sure that you overwrite it with the exact same name every time.