[Update] Post has been updated to correct the maximum size of the database to 10 Gigs

When should you use SQL Server with Access? (Hint: Almost always)

Look, I’ve heard the phrase “When all you have is a hammer everything looks like a nail” and given that our specialty is Access with SQL Server at IT Impact, you may suspect that is the case here. Using SQL Server with Access is such a wonderful combination, it would be almost criminal neglect to not suggest it as a solution.

SQL Server Express was the best thing to happen to Access.

I’m not exaggerating when I compare upgrading an Access database to SQL Server is like going from a bike to a car, even if it’s a version with severe limitations such as Express. Issues such as lost data, corrupted data, and slowness are solved by using it.

Here’s a great example of the issue: You’re using MS Access with both a Front End and Back End file, (where the data is in the Back End file), and you’ve got half a dozen users on the network using the same database concurrently. Unfortunately, one user has an issue and crashes the database on their PC. Odds are you may lose data, get corrupted data or not be affected at all,

Another example: Your database grows more popular at your company. At first, it was only a few users, but as your database became mission-critical more and more users are wanting to use it, making the system slow and unresponsive.

Both situations would benefit by upgrading the data to SQL Server. I’ve had upwards of 100 users on an MS Access with SQL Server solution with no problem.

When do you only use Access?

There are great reasons why you may be using Access without SQL Server, not the least of which is your IT department may not allow it, (more on that in a future post). Another is the low amount of users. (1 to 3 users are my standard to using Access, more than that and I recommend SQL Server).

More reasons to upgrade

• Your data can be encrypted in transit to/from the PC.
• SQL Server can store data securely: credit card, Social Security Numbers, and HIPPA information.
• Access has a 2 GB size limit, SQL Server Express has 10 GB limit.
• You can integrate Active Directory Groups into SQL Server to create rich security schemas.

Sometimes upgrading to SQL Server can make matters worse: your Microsoft Access database can become slower, or worse, you can’t edit data at all:

• If you’re tables become read-only it’s because they are missing a primary index. Click here to learn how to fix it.
• If your queries are too slow then convert them to views.
• Avoid using hybrid queries, where you’re using both local tables and SQL Server tables in the same query.
• Follow these tips for designing your tables in SQL Server for maximum compatibility with Access

For further help, contact us today.