Search web

Site owners

  • Boobalan Munusamy

Statk Overflow

Recent site activity

MyFreeCopyright.com Registered & Protected
indiae.in
we are in
Software Zones‎ > ‎Database Community‎ > ‎SQL Server‎ > ‎

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

posted Apr 14, 2011, 5:05 AM by Boobalan Munusamy   [ updated Apr 14, 2011, 5:40 AM ]
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





Comments