Using SQL Server Express for Your Microsoft Access Projects

Use SQL Server as the back end database for your Microsoft Access projects

Summary:

Some of the reasons why you should use SQL Server (or SQL Server Express) to store data for your Microsoft Access projects. Plus, code to simplify connecting to SQL Server and attaching tables without manual linking.

The Access DB is miserable but awesome!

Can we be honest. It’s slow, clumsy to move around, inherently insecure on a network, prone to corruption, etc. etc. etc.

Some devotees will argue with this – though I’m not sure why. That’s fine, everyone is welcome to their opinion, it’s just those people are wrong.

Okay, now that I’ve alienated a bunch of people and gotten them to tune out, let’s continue.

Problems with mdb and accdb files

  • Security
  • Performance
  • Flexibility
  • Internet or WAN connections
  • Managing/Making Changes

I’ve sort of listed them above. I don’t want to belabor the point, but the idea of opening a share that gives access directly to a file – one that users can delete accidentally – is enough. Add to that that making that data available across the Internet is problematic, at best, is a deal clincher.

Administering and changing an Access DB is also a hassle. I have to knock everyone off the database and open it on my computer. With SQL, I can connect, very efficiently, to a SQL database with the SQL Management

We are in 2018 people! That Internet thing, it’s probably going to catch on eventually.

And finally, the grotesque inefficiency of native Access databases, with even a few simultaneous users, game over.

But, at times, I’ll prototype something, single user – just for me – using a native ACCDB database. I know I’m going to move anything “real” to SQL Server but for a rapid prototype or manipulating some data to move it elsewhere, it works great! And for that reason, Access databases are awesome! But, beyond that, not awesome!

Use SQL Server for Your Access Projects

One of the problems a LOT of those using Access struggle with is the process of connecting their Access front-end to a SQL back-end. They manually create links and when they are in development or rolling out changes, they have to manually re-link and refresh those table connections.

To that end, I have code here (a downloadable Access file below) that contains an amalgamation of code from the Internet and my own.

The Access DB includes two local tables. One local table contains the SQL Connection string. (zlocalSysInfo) The other contains the SQL table names and the local table names (usually the same) you want to use in your code. (zlocalTables)

I’ve also included a form that lets you run the connection code to link to the SQL tables.

You could put this routine in a start-up routine, having it connect and refresh connections every time the application starts. That way, any changes you’ve made to the existing SQL tables and any new tables you’ve created would be available.

Microsoft Access File Download

MS Access SQL Connection Example

Posted in Microsoft Access, SQL Server and tagged , , .

2 Comments

  1. Hi Matt

    Thank you for your concise explanation of what makes SQL great. My own experience includes other SQL servers so if you don’t have Microsoft SQL ( and I know Express is ‘free’) look for a free alternative. The linking from Access may be trickier but all the other advantages you list remain valid.

    • Hi Tony,

      Thanks for weighing in. I’ve done the same with MySQL – though I use it sparingly. But with the driver installed, you can swap out the connection string and be up and running in very little time.

      My bias, or the focus of this blog, is simply driven by the clients I tend to work with and their use of SQL Server.

      I’m often surprised by the number of Access developers who are afraid to wade into the SQL Server world. Once they do, however, they rarely look back.

Leave a Reply

Your email address will not be published. Required fields are marked *