Know How To Convert MS Access Database to SQL Server

With time, multiple database application acquires grow, it will become more complicated and need to support many users. At some moment, a user able for importing access database into SQL Server Database. MS SQL Server Database helps to optimize the security, scalability, availability, performance, reliability and recoverability.

Moreover, we will discuss the access upsizing wizard process which is used regularly to upsize the small and large Access databases. In the following session, we are considering several steps which help to upsize the process.

Convert MS Access Database to SQL Server

There are following steps to transfer Microsoft Access Database to SQL Server those are mentioned below:

Create a SQL Database and Multiple Log Devices

Consider, if a user may create a database twice the size of Access file, then the estimation is incorrect due to the presence of Memo fields. Memo field, which is used to upsize the amount of space because this upsizing Wizard helps to create a timestamps column for every field. Thus, we have created a database and log devices five times bigger than the MDF file.

Create New Data to Upsizing

A user has to create database and an ODBC connection for existing SQL Server database. The ODBC data source is obtained to store the information while creating the database.

Gather the table to Upsize the Database

In this step, a user must select Access Database Documenter that help to gather the entire information to upsize the SQL server database. Make sure a user has to install the documenter from MS Office setup.

Follow some points to gather data on table relationships, Select Tools, Analyse, Documenter from database menu bar. After choosing the current database option, select Relationships and press OK to display the entire detail of all relationship in the database.

Make MDB files More Manageable

If the name of table or field names is not valid for SQL server identifiers then, upsizing Wizard tries to force the table to become more manageable. Due to some situations, the Wizard creates an aliasing query, that takes the name of the original table and translate each SQL server field names to their original names.

Delete Recursive Relationships

Recursive relationships are the medium between two fields within the same table that users have applied. If a user tries to upsize this table, then, the upsizing Wizard doesn’t have any problem to create the structure of a table. However, Wizard will encounter various difficulties while loading the data because it must load all supervisors before it load the other table data. Thus, a user must remove all recursive relationships.

Determine the Order of Table

When a user has to load the data, make sure first to load the table along with no foreign keys. After that, load all the tables which depend on first as well as second level tables and so on. Table dependency order is most important because if a user wants to load over-sized data then, loading becomes fail.

Check Entire Default Values

It is necessary to check all default values, especially if a user has number fields along with default values o in the table that a user don’t want to allow o values. For example, if a user mentions the Identity field of SQL server parent table within child table, the default value comes as o, even if a user allows only null values in a parent table. This issue happens, access shows the default value before saving the list of records. Therefore, a user might view o in Access interface and delete it for setting the field to null. However, When a user wants to attach SQL server tables, MS Access can not display the default values. Hence, it aspects like a user entered a null, but when a user wants to store the record, SQL server attempts to insert the o value and the updated records become fail.

Move Data Tables into Code File

A user must perform this step only if users data and code are present in separate databases. While running the upsizing Wizard, it must attach all SQL Server tables to the database from which the Wizard runs. If a user might leave the tables in a separate database and try to run the Wizard from the table database, when a user wants to link the code file to new SQL server tables, the Wizard represents the names of all newly linked SQL Server as tables dbo_TableName. Apart from this, a user can rename each table manually. A user can ignore this hassle by importing all table from .mdb file into code file before running the upsizing Wizard. Make sure to include the data, relationships, and structure.

Running the Upsizing Wizard

While running the upsizing Wizard, make sure a user must upsize each table at once to assure that the table relationship acts together. In case, if Table X has a relationship with particular Table Y and a user can upsize Table X but not Table Y then, the Wizard breaks the relationship between both tables.

Let the upsizing Wizard helps to specify the attributes of the table to upsize, including the data options and modification. The following points clearly helps to run the upsizing Wizard:

• Using Declarative Referential Integrity (DRI), not triggers for several relationships. If a user doesn't have more experience with SQL Server, then, DRI is much predictable and easier as well for using as compared to the triggers. A user might face problems with referential integrity if a user choose triggers, insert and delete operations must behave uncertainly. Nevertheless, if a user uses DRI, a user can write triggers for cascading deletes.
• Make sure, Wizard don’t need to decide where putting the timestamps if a user doesn't have a powerful preference. The upside to timestamps helps to track the sequence of changes in users database if a user must have the time and expertise to figure out how they do their work. The downside to timestamps is dual.
• First, timestamps add storage data space to a user database.
• Second, they try to make it more difficult to bcp data from MS Access tables to SQL Server database tables. If a user wants Wizard to place the timestamps, a user has essentially done more advanced bcp formatting thus SQL Server easily knows how to represent the various fields from a text file to the database fields. Moreover, advanced bcp formatting is a time-consuming process.
• Create only the structure of table no need to upsize any desired data. Especially, it is necessary to tell the Wizard to create the table structure if a user has a computer system with referential integrity. If a user lets the wizard upsize the data, it may be upsized the child tables before the parent tables, it may cause to become fail while loading the data for those tables.
• Create the link with new SQL Server tables and save the relevant Password information with linked tables. Otherwise, when a user tries to open an Access file, a user will get a prompt for a password to re-establish a user connection along with SQL Server.

After this, click on Next to get final Wizard screen. This screen represents an option to create the log report. After selecting, a user must get this report and choose Finish to run the upsizing Wizard.

Check the Log

After running the access upsizing Wizards, indicate the log immediately because the Wizard might remove this log. Also check the log for aliasing queries and other unnecessary errors. If a user receive the errors, then must decide either to work around them or redo the data upsizing procedure. If a user has decided on later, a user must remove the newly created database and then, revert to .mdb file, Fix the problem on Access side and re -test the upsizing Wizard.

Load the Access Database into SQL Server

Now, load the Access data within SQL Server through several tables. If a user has a database without any Auto number fields, then the data should migrate from Access database to SQL server with few issues. Simply, a user can allow appending several queries for each table. If a user holds large tables, then, a user must need to run smaller queries for every table.

Need to Write Triggers in SQL

For relationships with cascading events, a user must remove the DRI foreign keys and write all triggers. A user can write triggers for recursive relationships and also for table-level validation rules. Triggers help to assure the data integrity.

Run New SQL Server System

Multiple users have to test the SQL database server system several times. Add data to each table and make sure the Desktop acts predictably (must break each rule).

Conclusion

Here we are ending with the steps recommended to convert MS access database to SQL Server without any consequences.