The business has outgrown its Access database

How an Access database gets here

It started as a way to keep track of jobs, customers or stock, built by someone in the business who knew what was needed. It worked, so more was added: quoting, then invoicing, then a dozen reports. Ten or fifteen years later the business runs on it.

Access was made for a few people sharing a file on a local network, and the business has grown past that. Six people have it open at once and the file corrupts. Someone working from home finds that every screen crawls over the VPN. An engineer on site wants to update a job from a phone and cannot. And the colleague who built it is now the only person who can fix it, on top of their real job.

What to check first

Before choosing a route, find out three things.

First, whether the database is split. A split database keeps the tables in one file on the server and gives each user their own copy of the forms and reports. If everyone opens the same single file, splitting it is a cheap way to reduce corruption.

Second, what is in real use. Access databases collect forms and reports that nobody has opened in years. The live part is usually much smaller than the whole.

Third, where the rules are. Calculations hidden in VBA behind a form, or in a chain of queries, are the part that takes care to move.

Your options

Move the data to SQL Server and keep the Access screens. The tables go into SQL Server and the existing forms and reports are linked to them. Corruption of the shared data file and the 2 GB limit go away, and backups become dependable. You still have a desktop application that needs Access on every PC, and nothing for remote or mobile users. It is a good interim step, and the next stage can use the same database.

Replace it with a web application. The screens are rebuilt to run in a browser against SQL Server, with logins, permissions and a record of changes. Staff can use it from anywhere. It costs more than the interim step and takes longer. Our Access to web app package does this at a fixed price.

Buy a packaged product. If the database does something standard, such as customer management, job management or stock control, a product you subscribe to will often cost less than anything built for you. The test is how much of what makes your process different you would have to give up.

Tidy it and carry on. For a small team in one office, splitting the database, setting up backups and writing down how it works may be all that is needed.

Migrating and cleaning the data

Whichever route you take, the data is the valuable part. Access is forgiving about what it lets people type, so years of use leave duplicates, customers spelled several ways and records that point at things which no longer exist. A stricter database will reject some of this, so it has to be found and corrected as part of the move. We write the migration as a script and run it repeatedly, so every rehearsal shows what is left to clean.

Running old and new side by side

For an agreed period the Access database stays the system of record while the same work is put through the replacement. The results are compared: the same quote, the same invoice total, the same stock figure. Once every difference is explained, Access is made read-only and kept for reference.

What we would do first

We would open the database, list what is in it and ask the people who use it what they rely on. That gives a scope that can be priced. If you need to keep Access going in the meantime, see our page on Microsoft Access.