MS Access is great for desktop databases with five to ten users. Anything more and it can become unstable. Stability is also affected by the volume of data held in tables. Corruption can occur resulting in the database having to be repaired or in some cases lost altogether. You may wish to compare MS Access SQL Server.
SQL Server is another Microsoft product and offers better security. It also has the ability to handle massive volumes of data, hundreds of users and has all round better stability.
SQL Server is not a development tool. It cannot be used to create data entry screens.
Fortunately there is a way of getting the best of both worlds here. We can use MS Access for creating the nice screens and then use SQL Server to hold our data.
I will show you how.
In MS Access 2003 we need to create an Access Project (ADP).
Go to the ‘File’ menu and select ‘New’.
You will see a list of options.
Select ‘Project using new data’.
Now select a location to save and give the project a name.
The SQL Server connection wizard will be shown.
Select your SQL Server from the dropdown box.
If your SQL Server is stored on your local hard drive then enter (local).
If you want to login using your Windows credentials then tick the box ‘Use Trusted Connection’.
You can also give the database a name. I like to use the same name as my Access project.
Excellent so far!
Now if you switch to SQL Server you will see your database.
Note: You may need to refresh the server list before it shows.
Now switch back to Access and create a new table.
Notice the data types look different. Well they are because Access is using the SQL Server engine.
Create some new fields and save the table.
Again switch back to SQL Server and refresh the table list of the database.
You will see the table you just created in Access.
We now have a situation of MS Access SQL Server.
If you switch back to Access and add data to the table you will find that also shows up in your SQL Server table.
In Access you can go ahead and create forms in the normal way. You can also create the equivalent of queries – views or stored procedures.
SQL Server is a robust database, but is not as easy as Access to use. It does however offer far better stability than Access. Thankfully Access users can now connect to SQL Server and offer scalable solutions.
With the change of MS Access SQL Server, you get the robustness of SQL Server with the rapid design development of Access.