Working with Microsoft Access databases

One should always use the right tool for the right job. If you want to store large volumes of data and have many users then think if Microsoft Access databases are the best way to go.

Microsoft Access databases tend to be better suited to small systems with around ten or less users. For anything more substantial you are better looking at something more robust such as SQL Server.

When working with Microsoft Access databases which have a small number of users you should aim to put one copy of the database on the network. This database should contain only the tables.

Each user should have a copy of the database on their PC with everything in it except the tables. Each of the users will have a connection to the database sitting on the network. This will speed things up a little and also can help to prevent corruption or system crashes.

Microsoft Access databases have always suffered from stability problems, but over the years with each new release there has been an improvement.

Another option is to have Microsoft Access databases connect to SQL Server. The database table will come from SQL Server and the forms, queries etc will all be in MS Access.

Not only does SQL Server offer better stability, it can also handle larger volumes of data and has better security.

Whatever solution you choose to implement it is worth planning out your system and taking into account expected data volume. Also consider how expandable the system is and whether the number of users is likely to grow by much in the future.

Tags: , , , ,