Step 2

In keeping with my previous post about attempting the work and then blogging about it afterwards, this post details what happened when I tried to point the existing app to the new installation of SQL Express...

In the VB6 world, my app had a single class that handled data access. A true single point of contact for the database. Everything was contained in it and to move to SQL would have been simple: Change the one class to communicate with SQL and off you go.

When I started the .NET port I tried to continue in the same way but quickly realised that it just didn’t work that way anymore. I didn’t understand the subtleties of .NET and so ended up using DataSets and TableAdapters in the form designer for each form that needed it. The project had a DataSet designer that graphically defined the links between the tables and so on.

Moving away from the Access database to SQL Server was therefore going to be tedious as there were numerous places that would need to be changed and then checked. But not to be put off I gave it a go!

First off, my Development Environment is contained in a Windows 7 Virtual PC, whereas I installed SQL Express on the main OS so I can use it for both Development and Production. So, fire up the VPC, load up Visual Studio 2010 and open the current project.

To ensure that I knew that during debugging there could be no way that data was being read from Access I renamed the Access database so that all data had to come from somewhere else (SQL hopefully).

Debugging the app threw an expected error “Could not find file 'C:\Users\All Users\KCN\Office Manager\OfficeManager.accdb'.” Sweet.

Next, the connection string that was held in the settings file was changed from “Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Users\All Users\KCN\Office Manager\OfficeManager.accdb"” to “Provider=SQLOLEDB;Data Source=MainPC\SQLExpress;Initial Catalog=OfficeManager;User ID=sa;Password=<hidden>;”

Debugging the app showed it didn’t work though! As the development environment was on a Virtual PC it was unable to connect to SQL Express on the host. Using this page I had to enable remote connections to SQL Express, enable TCP/IP, set the port to 1433 and then play with Windows Firewall to open ports 1433 and 1434 in both TCP/IP and UDP.

Finally a connection!

Opening the app and reading data worked fine, although the performance was PAINFULLY slow.

Testing a data write threw an immediate error of "Incorrect syntax near '`'." Hmmm. It quickly became obvious that it was never going to work as the SQL commands embedded in the DataSetDesigner were for Access and not SQL so things like "INSERT INTO `AssetTypes` (`AssetType`) VALUES (?)" are obviously going to fail.

I can see that there is no easy way to move the current app to SQL. I’m going to have to re-create the data schema in the application from scratch and then check every point of data access.

The question is this; is it worth all the additional effort?

Probably not. I think we'll chalk this one up as a failure and move on. I'll leave the current app as it is ...