Add MSAccess 2007 to SQL2008 server via Linked Server and access all MS Access table information from SQL

Post date: Apr 14, 2011 12:05:52 PM

1. First add MSAccess to SQL2008, So Open SQL2008->Expand Server Object from Server in the Object Explore

-> Right click server object->New->Linked Server

or right click Linked Server-> New Linked Server wait till dialog box appear.

2. Once it shown, Fill the required details.

or execute following script

/****** Object:  LinkedServer [GREATINDIA]    Script Date: 04/14/2011 17:34:24 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'GREATINDIA', @srvproduct=N'GreateIndia', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\MSAccess\Tryal.accdb'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GREATINDIA',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,

@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'use remote collation', @optvalue=N'true'

GO

3. Use following query to get data from MS Access table

SELECT [ID]

      ,[Id1]

      ,[Name]

      ,[Type]

  FROM [GREATINDIA]...[MSObjects]

GO

4. The SQL Query used to get MS-Access table columns information is

Syntax:

sp_columns_ex [ @table_server = ] 'table_server'       [ , [ @table_name = ] 'table_name' ]       [ , [ @table_schema = ] 'table_schema' ]       [ , [ @table_catalog = ] 'table_catalog' ]       [ , [ @column_name = ] 'column' ]       [ , [ @ODBCVer = ] 'ODBCVer' ]

[@table_server =] 'table_server'

Is the name of the linked server for which to return column information. table_server is sysname, with no default.

[@table_name =] 'table_name'

Is the name of the table for which to return column information. table_name is sysname, with a default of NULL.

[@table_schema =] 'table_schema'

Is the schema name of the table for which to return column information. table_schema is sysname, with a default of NULL.

[@table_catalog =] 'table_catalog'

Is the catalog name of the table for which to return column information. table_catalog is sysname, with a default of NULL.

[@column_name =] 'column'

Is the name of the database column for which to provide information. column is sysname, with a default of NULL.

[@ODBCVer =] 'ODBCVer'

    exec sp_columns_ex 'GREATINDIA','EMPLOYEE',null,null,null

-Reference Microsoft