Get Mirosoft Access data

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  December 6, 2013 at 1:19 pm

  #302114

  Is there any way of getting Mirosoft Access data in c:\order.mdb from SQL without import Access table?

  For example, I want to complete a store procedure to get data from one table in Access

  Select * from Order (table Order is in c:\order.mdb)

• Igor Micev

  SSC-Dedicated

  Points: 33110

  More actions

  December 6, 2013 at 3:27 pm

  #1671686

  You can add and use Access as linked server but only if your Office installation is a 64bit as SQL Server is.

  EXEC sp_addlinkedserver

  @server = 'AccessTest'

  ,@provider = 'Microsoft.ACE.OLEDB.12.0'

  ,@datasrc = 'C:\Users\IgorMi\Desktop\Database11.mdb'

  ,@srvproduct='Access'

  GO

  USE [master]

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

  GO

  Igor Micev,My blog: www.igormicev.com