Import 2007 access file (.accdb) to SQL Server

Download below system driver and install it.
http://www.microsoft.com/en-us/download/details.aspx?id=23734
After installing you will see another Data Source option: “Microsoft Office 12.0 Access Database Engine.”

Select this option to import from an ACCDB file. Then click the Properties button to open the Data Link Properties window:

On the Data Link Properties page, provide the location of database in to the Data Source field, and enter the User name and password if applicable. Click on the Test Connection and make sure it succeeds.

Click OK to close the Data Link Properties sheet, and continue to the next page of the Wizard, to choose the Destination.

SQL Date Format Functions|Date Conversions| 2 digit month or day values

DATE FORMATS
Format #	Query (current date: 12/30/2006)	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30
TIME FORMATS
8 or 108	select convert(varchar, getdate(), 8)	00:38:54
9 or 109	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
14 or 114	select convert(varchar, getdate(), 14)	00:38:54:840

How to get 2 digit day or month format?
Solution: SELECT RIGHT('0' + RTRIM(MONTH(GETDATE())), 2);

Unable to import float data type values from excel to sql server

Issue:
Unable to import float data type values from excel to sql server though the text in the column contains string values.

Description:
The issue with JET Engine is that it guesses the datatype of the column based on the majority of the type of values in that column.
After a long research i could come across a work around. Basically there is no direct solution as it seems that this is a bug with JET engine.
Use OpenRowSet command with IMEX=1 in connection string, to import the data to sql server as it is in the excel.

Process below:

First check if "Ad Hoc Distributed Queries" setting is enabled in sql server.
Run sp_configure in query window and check the list of the configuration. If there are 14 list items and there is no Ad Hoc Distributed Queries then it should be enabled.

To enable "Ad Hoc Distributed Queries" follow below steps:
Run
sp_configure 'show advanced options', 1
reconfigure
Then run
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Now run below query to get the result set from excel
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Test.xls;IMEX=1;', 'SELECT * FROM [Sheet1$]')
Most important is that you should give IMEX=1 in the connection string since this is the one setting that is going to help us solve the issue. This command tells JET not to guess the datatype of the columns in the excel.

Now that you get proper data, you can insert the data into the required table using INSERT INTO ......... SELECT ...FROM format of sql