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