VB.NET: Upgrading from MS-Access to SQL-Server (Part 1/3)
Introduction
Information presented will provide guidance to move from Microsoft Access database to Microsoft SQL-Server for VB.NET developers working in Microsoft Visual Studio. Focus will be on working with using OleDb and SqlClient data providers using connection and command objects to perform common data operations. Techniques shown can also be utilized with DataAdapter components but not with TableAdapter methods to work with databases.
While SQL-Sever is a much more powerful system than Access, there are many cases where it makes more sense to use Access instead of SQL Server. There are times where a more modest database might be all that is required. In these cases, Access will often fit the bill perfectly. SQL Server would be overkill.
One thing that makes Access attractive to individuals and small business is that a relatively non-technical person can whip up a database in no time using Access. Success is dependent on either blind luck or having decent knowledge of how to put containers together in a database for business needs.
An assumption might be made for those interested in how to move from MS-Access to SQL-Server which is there is a pending or future consideration for scalability, MS-Access is currently bloated and requires constant repair or there are issues with many users working with a database that does not have the ability to keep things in sync.
Series
Requirements
- Microsoft SQL-Server Express edition, version 2012 or higher
- Microsoft SSMS (SQL-Server Management Studio)
- Microsoft Visual Studio 2017 or higher
The decision to not only do a simply upgrade but to also if possible be open to new ways to code along with making an effort to learn about database performance and how to tune your database or obtain professional guidance to tune your database.
Implement a backup and restore plan as SQL-Server excels with various options to perform backup and restore operations. For those developers who require the ability to perform backup and restore operations in code there is SMO (Server Management Objects) which has a Backup and Restore classes to assist with this.
Data Preparation
For a successful transition from MS-Access to SQL-Server these steps presented are critical.
Step 1 is to decide which database type is going to be used, standard or enterprise edition which resides on a network or cloud server, a Express or LocalDb based solution. Then there may be the case to combine one of the server based solutions with a LocalDb if users work offsite and will need to sync to a server-based database when a connection to the office is available.
Step 2 is to create a backup of the MS-Access database which will be ported to SQL-Server.
Step 3 is to evaluate current field data types in MS-Access tables against SQL-Server field data types using the following table which indicates if a field type in MS-Access can be mapped to SQL-Server. Once these field types have been identified there may be an opportunity to fine tune, for example, for a date field selecting one of the many offered for SQL-Server, this should be avoided and left to after moving data to SQL-Server so the transition goes smoothly.
Step 4 review the database schema relationships. If the MS-Access database can be normalized this is the time to consider this. For instance, a customer table stored details like address information where a company may have more than one address, in this case consider adding a table to store address information using a foreign key to relate back to the customer table. Look for opportunities to take any repeating data into child tables or reference tables. Once completed write queries in the database rather than in a Visual Studio project to test all changes to ensure data is returned as expected, inserts, updates and delete operations function correctly.
Step 5 Views and queries which rely on other quires. Review all queries in your MS-Access database, if they rely on other queries these queries will not port to SQL-Server, a strategy is needed to rewrite these queries to fit with SQL-Server.
Step 6 any linked tables need to be brought into the MS-Access database and have fields reviewed as if they were originally in the MS-Access database from the start.
Step 7 review any code modules and/or macros for functionality that needs to be setup in the new SQL-Server database.
Another important aspect of the port operation is not only to review the database relations but also review business rules. For example, a customer table has a company name field, if required consider setting the required property to true and Allow zero length property to suit business rules. Later, in code these rules can be enforced using a validator (there will be examples in the accompanying source code) classes which run prior to an insert or update operation.
Review fields which may benefit from setting default values such as a date field for modified date and/or create date. These don’t port over using SSMS import operation but instead are done after the import process has completed.
Expect the import process to have data errors, that is okay, when they happen SSMS import process will indicate what the issue is so they can be handled.
Importing using SSMS Import wizard
Open SSMS, connect to your server (or Express server). Once connected to your server select the Object Explorer, right click on Databases and select, task followed by “Import data”. A window appears, click the “Next” button. The next window prompts for the data source which will be your MS-Access database. In the Data source combo box select Microsoft Access (Microsoft Access database engine). Next click the “Browse” button and select your database. If this is a password protected database enter user name and password.
Click the “Next” button to select the destination which should be populated to the current database. Click the “Next” button which has two options, select “Copy data from one or more tables or views”, click the “Next” button.
A window appears prompting from tables to import. If there are many tables start with references tables and or related table and resist importing all at once as this is the time were errors will happen and it’s best to deal with the least as possible. There is an “Edit Mappings” button which should not be needed if the preparation steps were adhered too.
Click the “Next” button for the review window. Note the column for the bottom grid, “On Error”, leave as is and only need to change if this import operation fails. The two main options are fail or ignore which you must make the decision which one to select base on your knowledge of the data.
Click “Next”, “Next” and “Finish” to run the import. This is a twelve-step process, if all is successful there will be green checks next to each validation and explanation marks on the copy processes. If successful repeat the process for the remaining table, several at a time or go for all of them. If there are failures there will need to be manual changing of data, relations or structure changes to the MS-Access database to permit the import to the SQL-Server database.
Note the above process can be performed in Visual Studio using the following link to install the tools needed.
Once all tables have been imported, inspect the data in each table, note that data may appear slightly different then when in MS-Access e.g. dates for instance which is not a concern as dates and other fields can be formatted in code rather than how it may have been done in a table design property of a field.
Note another method to migrate MS-Access to SQL-Server is using SQL Server Migration Assistant which will do other databases also. Using SSMS import data should be the first choice followed by the migration wizard as the SSMS import process provides an overall better experience.
Code preparation
First step is to create a complete backup of any projects will have code modified to move from MS-Access to SQL-Server using a GitHub repository, TFS or creating a copy of the Visual Studio physical folder of the current code into another folder.
Review any components and controls which interact with data, make sure they work with SQL-Server.
If current code lives in one or more forms which directly accessing, inserts, removes and filters data from a database consider moving this code to a class. What belongs in the class is one method per operation and for each operation there is a local connection and command object at the very least. Connection strings can be in the same class, in the application configuration file or a custom configuration file.
Avoid creating a single connection object as a public or private property of the class as this method creates extra code which is not required when using a connection object in a method which is creating with a using statement as demonstrated in the accompanying source code.
Implement exception handling on all data operations, there are many directions this can take, in the accompanying source code one simple and effective method is demonstrated using a NuGet package which also provides an easy to use method to create connection string for SQL-Server, MS-Access and Oracle.
If a query requires values such as in an INSERT, UPDATE, DELETE or SELECT WHERE, do not use string concatenation, instead add parameters to the command object. This means an effort should be made when writing the code to access SQL-Server data from a former MS-Access project which has been using string concatenation don’t simply attempt to use a slightly altered version of the query but instead begin using parameters. The common used reason to use parameters is to stop SQL-Injection while there is another important reason which is to properly prepare data to be used in queries. Example, “SELECT FirstName FROM Customers WHERE LastName = ‘O'Brien’” where the statement was created using string concatenation, upon execution an exception will be thrown as there needs to be another apostrophe e.g. “SELECT FirstName FROM Customers WHERE LastName = ‘O''Brien’”. When using parameters, the lone apostrophe is doubled up to provide an escape character thus no exception is thrown.
Unlike MS-Access parameters which are work by ordinal position SQL-Server parameters are named-parameters meaning they may be added to the parameter collection in any order. It is commonplace to use a ? mark as a parameter marker with MS-Access as the thought is a named parameter doesn’t matter since MS-Access is ordinal based parameters but by using names it’s easier to look at a query and parameters to ensure everything is in it’s place along with meaningful names for values being assigned to these parameters.
When an operation which interacts with a database will insert or update one than one table use a transaction on the connection object which commits upon successful completion of all operations and on failure will rollback all changes in the operation.
In general, when there is proper preparation the code aspect moving from MS-Access to SQL-Server should be easy. To see a comparison for reading data for both databases see DataOperationsAccess and DataOperationsSqlServer classes from the accompanying source code. Moving to other code samples in the accompanying source code, for instance inserting records (will be will covered later) the two databases schemas have changed to provide a seamless transfer of data and to fine tune the relationships.
Containers
Common practice in many cases leads a developer to use DataSet/DataTable or just a DataTable for containing data that is read and manipulated while there are times when these containers provide more functionality then needed for some operations. For instance, populating a ComboBox for a list of states using a DataTable is overkill will a List(Of T) would be a better choice as the data is read only. The class would have a display property and a key (a primary key within a reference table) and override ToString to return the property for the display property so all that is required to setup a ComboBox is to set the DataSource, no need to set the DisplayMember and ValueMember, instead cast the SelectedItem to the class and access the key and display property (there are examples in the accompanying source code).
For most code samples to follow DataTable and classes will form the base for most code samples. Later on exploration will be done with only classes and Entity Framework.
Updating data in controls
It’s commonplace for developers to pass information collected by user controls such as TextBox to update or insert data to a database then thinks to see changes in a DataGridView (for example) the grid needs to be loaded again to reflect changes, in the examples to follow techniques will be shown that never require any sort of refresh or reloading of data to see changes from user controls. If current projects use reloading to see new records or updates discard this idea and consider the techniques which will be shown as this article progresses.
Validating user input
The most used methods to validate data is to check if controls such as TextBox have a value, ComboBox and ListBox controls have a selection and when a grid is utilized various events tend to be used to validate input. Validator classes will be used to demonstrate performing validation on user input which not only validate data is present but also provide the ability to ensure required fields are set, minimum and maximum lengths are enforced so that there are no violations at the field level which if not used can a) truncate or misrepresent data b) cause a runtime exception. These methods also provide the capability to set custom error messages to present to the person using an application.
Helpers
In some cases there may need for manual intervention outside the import process using SQL statements or to obtain statistics for current data. In the included source code this is done in scripts or in code, when in code these methods will be in SqlServerUtilities project.
Setup
For each project in the repository listed below there will be a script which needs to run for creating the SQL-Server database, tables and data for use in seeing how a specific technique works. Before running a script the server name and path to the server may need to change. These scripts have been generated using SSMS 2012 with SQL-Server 2012 server base, not Express edition yet will work with SQL-Server Express edition.
Summary
In this article the groundwork has been laid down to provide starting steps to move from MS-Access to SQL-Server with VB.NET for transitioning to a different data provider that when done by following the steps provide for most upgrades should be simple. For complex upgrades these steps will still assist yet require more work on the MS-Access database dependent on how many tables are used. Part 2 will focus on how to read and insert data using SQL-Server database.
See also
SQL-Server database login for Windows Forms (VB.NET)
Source code
https://github.com/karenpayneoregon/MoveFromAccessToSqlServerWithVisualBasicNet
This repository will contain source code for each section/article which follows this initial introduction. As parts are added to this series each aspect will be explained in detail.