Have you got a Microsoft Access database that is too slow?
Or where the data keeps on getting corrupt and regularly needs “Compact and Repair”?
Or that you need stronger security on?
Or where you need audit trails and change logs?
Or that needs to be shared with a Business Intelligence tool that only understands SQL Server?
Does your organisation have a policy to get rid of Microsoft Access databases?
These issues can be solved by migrating the underlying data to Microsoft SQL Server !!
But by keeping the program screens in Microsoft Access!
Microsoft number 1 and number 2 databases are Microsoft SQL Server and Microsoft Access. Despite some serious differences, Microsoft has always provided tools to migrate from one to the other. So it has always been possible to keep the front end program more or less the same, while changing the back end data to SQL Server storage.
It is highly likely that you will be able to use the free version of Microsoft SQL Server – Express Edition. This will need to be installed on a Windows computer or server in your organisation, along with the Administration Tools.
To be honest, unless your database is extremely simple, you are going to need professional help to complete this job. The following tasks 1..5 need to be completed. It’s straightforward enough, but the last step is where most of the work is – (“Test all screens, queries, and reports. Make minor changes to VBA code and queries as required.”) But don’t fret – the cost of the migration is a whole lot less than reprogramming the database (your other option), and if the developer has experience migrating MS Access back end data to SQL Server, then they should be able to give you a fixed price quote.
Steps that the developer will need to perform:
- Make sure your Access program programs is Split into a Front End (Program) and a Back End (data)
- Install SQL Server on a windows computer in your organisation
- Do a trial migration of the Back End Data to SQL Server
Make sure you have connectivity via ODBC.
Create a DSN to your database
- Link the Front End program to the Back End SQL Server tables, using the same names as before
- Test all screens, queries, and reports. Make minor changes to VBA code and queries as required.
There are a number of other setups that you might like to consider. These things are too detailed to put on this page. Please post a question, email or phone if they are relevant to you:
- Optimise certain queries
- Move certain queries into SQL server views
- Either impose strict security on the Access front end (Yes this is possible!! – Fill out the enquiry form here to ask me how), or consider linking to sensitive tables in Pass Through Mode only, so that they cannot be browsed from the database window.
- Set up a backup schedule for your database
- Set up stored procedures
- Put security on certain views
Do you have a Microsoft Access database from which you need additional reports?
This month only, a new report for just $299
Get a Free Fixed Price Quote on Porting your Microsoft Access Database to SQL Server