Migrating from Microsoft Access to Microsoft SQL Server

There several steps to migrating an application from one database product to another database, such as from Microsoft Access to Microsoft SQL Server.

Step 1:  Migrate your application’s database schema and data from one database to another.  This is something that you will need to do on your own and possibly look for help on the Internet on how best to accomplish this.  There are third-party tools available that can help with the database migration process.

Step 2:  Make a backup copy of your application before proceeding with the migration process.  This is important in case anything goes wrong or you need to revert to your original application.

Step 3:  Change the application to point to your new database engine.  This is done by changing the database connection strings in your migrated application’s Web.config file to reference the new database server.  The easiest way is to let Iron Speed Designer create the new connection strings for you and then copy them into your migrated application’s Web.config file.

Using Iron Speed Designer, create a new “shell” application using the new database.  This creates the appropriate database connection strings in the new application’s Web.config file.  Then, copy them into your migrated application.  For Microsoft SQL Server, the database connection strings will look like:

add key="DatabaseNorthwind1"

     value=" Provider=SQLOLEDB;

          Data Source=localhost;

          Database=Northwind;

          Trusted_Connection=no;

          User Id=sa;

          Password=sasa"

Step 4:  Open the application in Iron Speed Designer and scan your new database for changes (Databases, Scan All Tables for Changes).  This identifies to Iron Speed Designer any subtle changes in database schema structure between your old database’s schema and your new database’s schema, including field type changes and foreign key changes.

Step 5:  Build your application using the “Rebuild All” command (Build, Rebuild All).  This updates your application’s code to use the new database schema.

Keep in mind though that database products differ slightly so a feature in one product may not work exactly as the other product.  For example, Microsoft Access has Lookup Tables and these are not supported in Microsoft SQL Server.  So your application may stop working or may not work correctly if it uses database specific features. Similarly, the data types of one database may not work in another database product - so things may not be exactly the same.  This is an issue for the database products and not really an Iron Speed Designer issue.

See Also

Microsoft SQL Server Database Support