Why move the data
An Access file has a size limit of 2 GB, copes poorly with many simultaneous users or a slow network connection, and can become corrupted when a connection drops. Backups, access control and logging are limited.
A database server removes those limits. It handles many users, offers proper security and backups, and can be reached by other applications and reporting tools. In the cloud, for example with Amazon RDS on AWS, much of the maintenance and backup work is taken care of.
Upsizing and linked tables
The classic approach is upsizing: the tables and data go to the server, and the Access front end keeps working with linked tables through ODBC. Access used to include an Upsizing Wizard for this; since Access 2013 Microsoft offers the SQL Server Migration Assistant (SSMA) for Access instead. For PostgreSQL, the move is done with migration scripts and the PostgreSQL ODBC driver.
This puts the data on a solid foundation quickly. Keep in mind that an Access front end over linked tables can be slower than before when forms and queries fetch more data than needed, especially over a VPN or to a database in the cloud. Linked tables also need a primary key to be updatable.
What to watch
Some Access features have no direct equivalent on a database server and need a decision per case:
References to forms in queries, such as a filter on a field of an open form, also need attention when queries move to the server.
- Lookup fields in tables: drop-down lists at table level that become real relationships with a separate table
- Multivalued fields and attachments (in .accdb): these have to be split into separate tables or moved to file storage
- OLE Object fields: embedded documents and images that have to be extracted and stored in a usable format
- VBA in queries: functions such as Nz() or custom VBA functions do not exist on the server, so Access processes those queries locally or they have to be rewritten
- Data types and values: Yes/No fields, dates outside the supported range, AutoNumber fields and text that does not match its field type
From database move to modernisation
Moving the data is often the first step in a broader modernisation. Once the data is on a server, a web application can be built on top of it part by part, while the Access front end stays in use for the remaining functions.
The free scan shows how many lookup fields, attachments, OLE fields and queries with VBA functions your database contains, and which tables are linked to other files. That tells you up front where the work will be.
Frequently asked questions
SQL Server or PostgreSQL?
Both are mature choices. SQL Server fits well when your organisation already works with Microsoft tools; PostgreSQL is open source. The choice also depends on hosting and on the knowledge in your team.
Can we keep using Access as the front end?
Yes, through linked tables. That is a common intermediate step. For use from home, on tablets or over slow connections, a web application is usually the better long-term solution.
Is the Upsizing Wizard still available?
No. It was removed in Access 2013. For SQL Server, Microsoft's SQL Server Migration Assistant (SSMA) for Access is its successor.
Is data in the cloud safe?
With the right setup, yes: encryption, restricted network access, personal accounts and automatic backups. That is often safer than an Access file on a network drive.
Use the free scan to see which tables, lookup fields, attachments and queries need attention before you move. The analysis runs in your browser and your file is never uploaded. We are glad to discuss the result in English or Dutch.
Start the free scan