SQL Server Database conversion
for Project Administrator

What is it?

For some years we have been asked to provide Project Administrator with a more heavy duty database back end. You can now convert your Access back end to run on SQL Server 2000 to 2008. The more robust design of SQL Server makes it more suitable for high volume users. It is also suitable for organisations that restrict use of Access as a database in order to control growth of data islands across the enterprise.

What does it replace?

The SQL Server database replaces the tables in the standard version of PA. The screens, reports and code are still in an Access file however the file uses data from a SQL Server back end.

Can I convert my existing data?

The simple answer is yes. It will however require the skills of a Data Base Administrator or someone skilled in SQL Server. There may be some manipulation of data required to correct corrupt or missing data. SQL Server is less tolerant than Access when it comes to data integrity.

Can I install it myself?

Unless you have extensive knowledge of SQL Server, we would not recommend you try and convert the data. It not only requires knowledge of SQL Server. It also requires administrator access and understanding of your security environment.

Can I use SQL Server Express?

SQL Server Express is the free download of the software. You can use the Access Upsizing Wizard to convert your data to SQL Server.

How do I go about it?

We suggest your first step is to talk to an SQL Server database expert. You will probably need them to assist with the conversion. Once you (or the DBA) have access to SQL Server, you use the Database Upsize Wizard which is under the Tools menu in Access 2002/2003, and under the Database Tools tab in 2007 and 2010. Select SQL Server for these versions.

Some databases have problems converting from Access to SQL Server. Will PA?

In spite of the availability of a wizard to guide you through the process, many Access databases do not convert properly. In fact there have been books going to 600 and 800 pages written about upsizing Access databases.

We have done all the work for you. The application has been modified to allow conversion. We have built PA tables so that conversion problems have all been overcome. If you get errors in the conversion - which is quite possible - they will almost certainly relate to data errors rather than structural errors.

Do we recommend upsizing?

Not generally. If you are doing it for performance issues, there are experts who claim there will be a minor degradation in performance by converting to SQL Server. Some organisations however do not allow Access databases so you may have no choice.

Access is maligned by many people. The main reason is that it is a relatively easy program to learn (or at least the basics) and consequently there are lots of amateurish programs out there. They lack the structure and rigor of a properly written piece of software. Their security may be poor, their performance slow. There is no reason a properly written Access program cannot perform as well as any other programming language.

What support can Project Perfect provide?

We will answer any questions you may have but most of the decisions or potential problems will relate to your own environment. We cannot assist with questions relating to security or networking issues. This is why we strongly recommend the involvement of a DBA. Your DBA is most welcome to contact us and clarify any issues prior to conversion.

Return to the top