Tuesday, December 8, 2009

How to convert Microsoft Access Database into SQL SERVER Database.

In order to convert a Microsoft Access Database into SQL SERVER Database, we have the following two options :

Option A

The first option involves creating a database in the SQL SERVER yourself first i.e. create all the tables with the contained columns and their data types in the SQL SERVER yourself. After creating the database, we will populate the database with the data from the MS-Access file.
The option consists of the following steps :

Step 1. Create a link server between the SQL SERVER and MS Access Database file.
Step 2. Access or get data from the MS Access file and insert into the SQL SERVER Database.

The option A has the following code :

- Code to create a link server between the SQL SERVER and MS Access Database file is :-



                                                                         
EXEC sp_addlinkedserver                                                                
@server = '<Link Server Name>',                  
@provider = 'Microsoft.Jet.OLEDB.4.0',                
@srvproduct = 'OLE DB Provider for Jet',                     
@datasrc = '<FullPath of MS Access file>'
                                                                                                                                    



 
After creating a link server between the SQL SERVER and MS Access file, we can now access the data from the MS Access file using the link server and can directly insert the accessed data into the SQL SERVER tables.
Let us say, we have a table tbemployee in the SQL SERVER and tbemp in MS Access file. We want to populate the tbemployee table with the data from the tbemp table. 

- Code for the above is :-
 

insert into tbemployee select * from [Link Server Name]...[tbemp] 


Note :- Create a Linked Server once and then fetch the records using the same link server as you wish.
Note :- Three dots (...) is must and [tbemp] is a table from Access file.
 

Option B

The second option involves creating a link between the SQL SERVER and MS Access file and then creating tables in the SQL SERVER at the RUN-TIME after reading data from the MS Access file.
The option B has the following code :

- Code to create a link server between the SQL SERVER and MS Access Database file is :-

Please refer to the above piece of code to create a link server.
  
After creating a link server between the SQL SERVER and MS Access file, we will be executing SQL's two stored procedures to get information about the tables from the MS Access file, columns and their datatypes as well.

The stored procedure to get information about all the tables in the MS Access file :

exec sp_tables_ex 'Link Server Name'                      

The stored procedure to get information about all the tables, columns and their data types as well in the MS Access file :

exec sp_columns_ex 'Link Server Name'                   

After getting this information from the MS Access file, we can create tables with the specified columns and their data types at the run time after creating a query and executing it.

Note :- There are some data types which are available in MS Access but not in SQL SERVER like byte,short etc. Do make a check to these data types.

And thats all.

Wish you Happy Programming.




Pritpal Singh