Access To SQL Server

While Access is great for small databases it is not so good when you have many users.
Say 50 or more. It may be worth thinking about moving Access To SQL Server.

Some companies may use MS Office an create small databases, but they may also have
the need for a more robust, stable database that can handle hundreds of users.
In this case they may use what is known as SQL Server. Like MS Access it is a
relational database.

Imagine a database system that does not include forms. Well, that gives you an idea of
what SQL Server is. You cannot use it to design fancy screens as it does not include a
form generator. This is a consideration when moving from Access To SQL Server.

The main SQL Server screen.


Comparison with Access…


SQL Server allows you to create database servers and store hundreds of databases on
it. It uses a language called SQL (pronounced sequel) which essentially pulls data from
the databases based on your criteria. SQL stands for structured query language and is
also used in Access, although not as much as SQL Server.


Stored procedures are sets of SQL commands that have been compiled and stored
within SQL Server. The Access equivalent are queries.

SQL Server has a query designer which looks very much like the one in Access. It is
used to create what are known as ‘Views’.


SQL Server also has much stronger security than Access. Permissions can be set on
tables, stored procedures and on the database in general.


SQL Server tables look similar to their Access equivalent.


In SQL Server you can schedule jobs – for example you may wish to have sales data
loaded automatically into a table each morning.

Unlike Access, SQL Server can store a lot more data in it’s tables. The banking industry
are known to use SQL Server and you can imagine the volume of data they must be
dealing with. No way could a desktop database like Access cope with such demands.

Data demand presents a good reason to move Access To SQL Server.

Access is more of a rapid development tool, great at putting together databases quickly.
It is also good for prototyping and getting an idea of what a larger SQL Server system
could look like. Access can connect to a SQL Server database and manipulate data in
it’s tables. It is common to develop screens in Access and then connect to the tables in
SQL Server.

There is a big gulf in price between SQL Server and Access, although there is a cut
down free version also available now. Think about whether you are ready to move Access To SQL Server.

It is always a case of thinking “What is the right tool for this job?”……if you are not
dealing with many users and don’t require heavy volumes of data or tight security, then a
desktop database like Access is ideal.