Today, I remembered a little factoid about Access database versions. You can’t link older front end Access files to newer back end Access files.

My mission was to take a set of Access applications I had upgraded for one department (which is being used in parallel with another department) and get everyone on the new set of backend files so we could get out of the parallel usage of the new and old applications for this one department.

So the new applcation uses both front end accdb files and backend accdb files while the old system uses front end mdb files and backend mdb files.

The other departments also use the old backend mdb files to get access to family relationships and part number lists. These other departments are still using front end mdb files.

What are my options here?

1. I am going to be updating the backends to SQL Server over the next few months. If I did that now, both the mdb and accdb front ends could be linked to those tables. Another advantage with this method is that I am planning on doing that anyway and I’m going to have to do it eventually regardless. A disadvantage is that I don’t know how the different sources will end up playing with each other. Sometimes, this can create huge inefficiences that then have to be analyzed and fixed to end up reaping the speed rewards that can be achieved by moving to SQL Server. But just leaving everything as is in the code and queries MIGHT result in slower performance which I’d have to work on.
2. Another option is to upgrade the current front ends to the accdb format. This is probably an easier option and would likely not cause issues like SQL server because the change is more compatible and continues using the DAO engine entirely. It is unlikely that there would be any changes in speed with this method. The main issue will be that I will have to convert all databases to the accdb format. This may be as simple as saving them in Access using the new format, but could also require me to create a new accdb database and import all the objects into it. This may or may not capture everything from the old database. Any shortcuts the users currently have will also need to be updated and if the databases have data tables that other mdb front ends are connecting to, I could end up with a cascading upgrade problem.

In all, I will likely move toward option 1 in this case. In the long run I need to do it anyway and this will make this path the most efficient one for the customer.