Troubleshoot Access to SQL Server Migration

Access to SQL Server Migration Troubleshooting

You can use Amphis Customer CRM Software with the free version of Microsoft SQL Server called SQL Server Express. SQL Server Express can support many users in true client-server fashion which gives superior performance and scalability improvements over using a Microsoft Access Database.

We recommend that you consult an IT Professional when installing and configuring SQL Server as it is not as easy as using an Access database. If you run into problems, here are some troubleshooting suggestions.

Troubleshooting Upgrade to Microsoft SQL Server Express

After you have upgraded your Access database to Sql Server, we recommend using SQL Server Management Studio Express to view the contents of the tables in the SQL Server database. The Customer table, for example, should have the same number of rows as the Customer table in your Access database (customer.mdb). If the Customer table is empty but there are customer records in your Access database, the upgrade to SQL Server has failed.

Connection Configuration

Check in the config.mdb Access database that you have entered a SQL Server connection string and set the useSQLServer flag to true. Check that the database name used in the connection string exactly matches the SQL Server database you have created. For example if you created a SQL Server database called customerSql then the connection string needs to include the name customerSql not customer.

SQL Server Configuration to allow Remote Connections

You will need to enable Remote Connections on your SQL Server, Turn On the SQL Server Browser Service and Configure the Firewall to allow SQL Server Network Traffic. Also you'll need to Enable the TCP-IP Protocol. See Enable SQL Server Remote Connections

Microsoft SQL Server Connection Strings

There are various connection string combinations you can use depending on how your SQL Server installation is configured. SQL Server can be installed as a Named Instance or a Default Instance. Authentication can be with Trusted Users (Windows Authentication) or SQL Server can be configured to require a UserName and Password to be provided (SQL Authentication). There are various protocols you can use to make the connection and different software providers that may be available to use depending on the software that is installed on your client PC.

Example SQL Server Connection Strings

Here is an example connection string connecting to the default instance on MyServer using Windows Authentication and connecting to the customer database.

Provider=SQLOLEDB;Data Source=MyServer;Integrated Security=SSPI; Initial Catalog=customer

Here is an example connection string connecting to a named instance on server Home, using Windows Authentication and connecting to the customer database.

Provider=SQLOLEDB;Data Source=home\sqlexpress; Initial Catalog=customer;Integrated Security=SSPI

Test Microsoft SQL Server Connection String

If there are customer records in your Sql Server database, check if you can connect to the Sql Server database successfully. Download our free tool for checking database connections - just extract and run the .exe file to try the connection string you are using.

Test SQL Server Connection

Just type in the connection string and click Test Connection. You should see a Connected OK message. If you don't, the client PC cannot connect to Sql Server for some reason and an error message may be displayed with some useful information about why the connection cannot be made.

Microsoft SQL Server Connection Problems

There are lots of reasons why you may not be able to connect to your SQL Server database. There could be network or firewall issues connecting to Sql Server or it could be that the connection string is incorrect. SQL Server may not be configured to allow remote connections (see above). You may have installed Sql Server as a Named instance but your connection string is trying to connecting to the default instance. Microsoft Sql Server is an advanced Database Management System (DBMS) and we recommend consulting an IT Professional if you cannot successfully connect to your Microsoft Sql Server database and you don't understand why.

Microsoft SQL Server Language Setting

Please note that if you live in a country that uses British date formats eg United Kingdom or Australia, you will need to change the Default Language for the Logins to British English.

SQL Server British English Dates

To check this, go to Security->Logins and select the Login you are using. Then check the properties to see if the Default Language is British English.

SQL Server British English

If your dates are in British format dd/mm/yy but the SQL Server Login you are using is configured to be the default of English then your SQL queries will fail because the dates will be invalid as it will treat them as mm/dd/yy.