Legacy MS Access application needs to support multiple users

Question

I'm currently on a 4-person team tasked with the development and maintenance of a legacy MS Access application.

The application is quite large, with hundreds of forms, reports, queries, and tables.

Currently we have the front end split into about 7 mde components, each of which is essentially an application in its own right, joined by a common front end which is essentially just a menu GUI.

We use linked tables to connect this front end to an MS Access back end, using OpenDatabase(C:\access.mdb) calls in the code itself. This application has been around for a while, and thus uses DAO to connect to an Access 97 backend.

This means that every user of the application has their own local copy of the database for making changes. We have a carefully change-controlled environment that ensures that only one person can be working on the data at one time, they have to validate all their changes before passing the master database to the next person.

This change control environment is stifling to put it mildly, and soon we will have a need for more data changes to be made in a timeframe that makes single user access unviable.

So, we need to move to multi-user access, but by multi-user I mean only about 4 people. This people are probably not physically located at the same office, so some form of remote db connection is needed.

The whole application is likely to be re-worked in a year or two's time, moving both the front and back end away from MS Access. However, we need multi-user access ASAP.

So, what is the quickest path to multiuser bliss?

Suggestions we are considering include:

• Setting up a VPN so that MS Access believes it is accessing a regular network drive. This looks like it will be slow and I'm unsure if a VPN is reliable enough, but it is just a temporary solution we're after anyway.
• Converting the mdb backend into something intended for multiuser remote usage, like SQL Server. We just don't know how to do this quickly and easily (we rely on field validation rules for example) We would also presumably have to convert back to MS Access format as other applications accept the same .mdb files as data input.
• Just about anything that can be carried out by 1 or 2 people in a couple of months.

EDIT: Responding to the below comments.

The data that is processed by the application is highly safety critical data. It changes rarely, and must be validated to show there are no logical errors before being exported. In reality, the data is under heavier restrictions than the application itself!

The data is interrelated in non-trivial ways. As such, a change to a record in one table may invalidate a record in another table due to complicated business logic. As such, at the moment, one copy of the mdb data file is designated the master database. Only one person has the master at any one time. If you want to make changes to it, you have to obtain that database from the person who currently has it. This is usually not a problem as data changes rarely enough that there's enough time for this to occur.

However, a large change is coming up which we haven't been given enough time to work this way. We have to have multiple people working on the data at once. Now I'm aware that you can share the mdb file on a network drive and have multiple people in the same office work on that file with little or no risk, but we need people from different companies to work on the data at the same time. as I understand it, setting up a VPN to share the data across is a bad plan.

I believe we have to change the backend away from MS Access and move to something like SQL server. But how easy is it to convert a schema in this fashion? How are MS Access table validation rules represented in SQL Server?

Answer 1

IMO you'll definitely need to convert the database to a multi-user server regardless of what other solutions you consider. There's an upsizing wizard that should prove useful (http://support.microsoft.com/kb/237980), you'll probably encounter items that Access allows and SQL server doesn't but for the most part should be painless. You can point your local access copies to use this new data-source (through ODBC for example) and I believe it should all work pretty much in the same way. Haven't done this in years, no idea what those field validation rules (wouldn't they still be on the form?) would turn out into. You can download a trial copy of SQL server and do this in under an hour to get a feeling of how much effort it would require.

Answer 2

As a general rule acccess right of the box is accesses multi user as a file share. What this means is that you could take the backend database (mdb file) and put it on a shared folder on a server. This would allow a few people in your office to run the application at the same time. However that means we’re talking about a typical office LAN. When you start talking about remote connections and VPN's and wide area networks (WANS), Then using access as a file share is not stable.

So if this is only three or four people in a typical office network environment, then depending on the application, it’s very likely that you can simply put the backend on a shared folder on the server and continue to have all the front ends deployed on each computer, and they are linked to that one database backend (mdb) file on the shared folder. MS access quite works well this way.

However for talking about some kind of VPN, or WAN, then one possible solution is to move your back end mdb file to SQL server, and continue to use all the forms,reports etc. from your current application (Most of your application will run as before when you do this).

Another really great technology to consider is thin client, or so called terminal services. Terminal services just a fancy edition of a remote desktop system. TS allows people over even fairly limited bandwidth to run and use the application from remote locations.

I have a fairly good article here that explains all your possibilities of this type of connectivity:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

However if you’re talking about three to four users on a typical office LAN, that it’s very possible your application will run with very little modifications as is, and you simply move the backend database file to a shared folder on a server somewhere. I can’t stress however that this only works if all the people are on the same little office LAN, And not some kind of remote connection or a WAN/VPN. So, in the case of WAN/VPN, use terminal services, or consider moving the backend to SQL server, and continue to use the application front end as is.

---

edit - more info: Ok, so with more info here we can move forward. As mentioned, ms-access is multi-user right out of the box. You need people from different locations to work on this data. So, this means that your application has to be setup for multi-user ability regardless of this different location issue. Once you have the application setup for multi-users then you THEN TACKLE the issue of allowing people to use the software from different locations.

This is no different than if we have something for managing the company Christmas party. If we have a design such that after the Christmas party we delete the whole file to start over for next year’s Christmas party, then we can STILL allow multiple uses into this application. However the design is such that you can’t have multiple Christmas parties active at the same time because of your designs. So in this case it is not the fact that the application is multi user. In this type of scenario, one might actually add a new table called the Christmas party year table. Then one can relate all tables in the application to this master table as a child tables. That way you can have multiple Christmas parties active at the same time for this design. Then when you start the application, users are prompted with some type of list to choose what Christmas party you want to work on.

So don’t confuse the two above separate issues. It doesn’t make sense to ask how terminal services allows the application to be multi user, it does no such thing. What TS does is allow you to take an application that is already multi user and allow people from remote locations to use that application. So TS is a system that allows people to run and use the application from remote locations anywhere on the internet. Your designs will still dictate if your application to allow us to more than one Christmas party to be active at the same time however.

So you don’t make MS access multiuser, MS access is multiuser right out of the box And you don’t have to do anything, except adopt some technologies that let users in different locations use the application. So, that is what TS does, and also SQL server can do for you.

If your design is only allows one project, then we can allow multiple users from different locations in the world to use an application, but they only be allowed to have one active project because of the design limitations of the application.

So all of your table updating logic etc can work as before. YOu simply have to ask is does the applicaion now allow one user to quit the applcaion, and another user enter into it and do their work? Pretend that there's only one standalone computer in the office. Can diffent employees durign the day sit down and use that one computer and that one application with the one backend for each of their separate projects?

So using SQL server, or terminal services doesn’t make your application more (or less) multi user then it is now. These technologies can certainly increase the number of users that can use the application the same time.

So MS access is multi-user now. However what SQL server does, or TS does is allow much more flexibility in terms of how the users remotely connected to this application.

Answer 3

As usual you've got an excellent answer from Albert Kallal.

If you want to consider upsizing to SQL Server there is a tool from the SQL Server group. SQL Server Migration Assistant for Access (SSMA Access) http://www.microsoft.com/sql/solutions/migration/access/default.mspx that is better than the Access Upsizing Wizard.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm

As you can see from the comments to your posting the term change control as your organization uses is non traditional and rather interesting. Although I can see how someone years ago trying to figure out a solution to remote offices changing data would come up with this solution. I can also see though how it would be stifling.