Import external data into SQL server: from MS Access file

2. MS access file
2.1 Use OPENROWSET
SELECT * FROM OPENROWSET(
‘Microsoft.Jet.OLEDB.4.0’,
‘C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB’;’Admin’;”,
    [Sample – Chips]) AS a
Since OPENROWSET does not support variable in parameters, if the file name is dynamically determined in run time, use dynamic query like this:
DECLARE @FileName nvarchar(255)
DECLARE @Table nvarchar(50)
SET @FileName=’C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB’
SET @Table='[Sample – Chips]’
DECLARE @SQL nvarchar(2000)
SET @SQL=’SELECT * FROM OPENROWSET(
”Microsoft.Jet.OLEDB.4.0”,
”’+@FileName+”’;”Admin”;””,’+@Table+’) AS a’
PRINT @SQL
EXEC(@SQL)
 
 
2.2 Use linked server:
EXEC sp_addlinkedserver ‘MSAccessTest’,
   ‘Access 97’,
   ‘Microsoft.Jet.OLEDB.4.0’,
   ‘C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB’
GO
SELECT * FROM AccessTest…[Sample – Chips]
SELECT * FROM OPENQUERY(MSAccessTest, ‘SELECT * FROM [Sample – Chips]’)
 
sp_addlinkedserver supports variables in the parameter datasource, e.g.:
DECLARE @FileName nvarchar(255)
SET @FileName=’C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB’
EXEC sp_addlinkedserver ‘MSAccessTest’,
   ‘Access 97’,
   ‘Microsoft.Jet.OLEDB.4.0’,
   @FileName
GO
SELECT * FROM MSAccessTest…[Sample – Chips]
 
1.3 Use OPENDATASOURCE:
SELECT * FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
  ‘Data Source="C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB";
    User ID=Admin;Password=’)…[Sample – Chips]
 
For dynamic file names:
DECLARE @FileName nvarchar(255)
DECLARE @SQL nvarchar(2000)
DECLARE @Table nvarchar(50)
SET @FileName=’C:\Program Files\Microsoft Office\Visio11\1033\DBSAMPLE.MDB’
SET @Table='[Sample – Chips]’
SET @SQL=N’
SELECT * FROM OpenDataSource( ”Microsoft.Jet.OLEDB.4.0”,
  ”Data Source="
‘+@FileName+’";
    User ID=Admin;Password=”)…’+@Table
PRINT @SQL
EXEC(@SQL)
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

5 Responses to Import external data into SQL server: from MS Access file

  1. Youjin says:

    oh cool! so three different method can be used for importing data from access file. how would you know which one to use among those? OPENROWSET, Use linked server or openDataSource?? ^_^ thx

  2. Youjin says:

    OHHH disregard the question above! u have already explaned about this in the article below!

  3. dov says:

    getting error on SQL Server 2010 64bit
    Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered.

  4. Hello colleagues, how is all, aand what you would lke to say about this post,
    in my view its truly awesome for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s