Remove From My Forums

How to create linked server to Access 2007 accdb file?

Soru
1

Oylamak İçin Oturum Aç
How does one create a linked server to an Access 2007 (accdb) file? I am aware that to create one to a mdb file, the code would be -
EXEC sp_addlinkedserver @server = 'SEATTLE Mktg', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' GO

What is the corresponding code for Access 2007 accdb files?
20 Şubat 2008 Çarşamba 12:47

Yanıtlar

5

Oylamak İçin Oturum Aç
HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:

- Install the 2007 Office System Driver: Data Connectivity Components on your server.
here is the link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

- Make shure you have SQL SERVER 2005 Standard installed. IT DOESN'T WORK'S ON SQL EXPRESS. No mather what you do. It only works on the standard versions...

- Install SQL SERVER SP2

- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess

- Use this T-SQL template (replacing by your own values) to create a connecto to the Linked Server:

Code Snippet

-- ==============================================
-- Add Linked Server Access 2007 ACCDB template
-- BY JOSÉ ALVES @ CCG - UMINHO - PORTUGAL
-- ==============================================

EXEC sp_addlinkedserver
    @server = N'Your Linked Server Name',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @srvproduct = N'Access2007',
    @datasrc = N'C:\path\to\your\db.accdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'Your Linked Server Name',
    @useself = N'TRUE',
    @locallogin = NULL,
    @rmtuser = N'Your Linked Server Name',
    @rmtpassword = ''
GO

-- List the tables on the linked server
EXEC sp_tables_ex N'Your Linked Server Name'
GO

-- Select all the rows from table1
SELECT * FROM [Your Linked Server Name]...table1

I hope this helps you. To me it was a big headache but i figured out after many tries...

Greetings from Portugal
- Yanıt Olarak Öneren hoffmanc 11 Ocak 2009 Pazar 02:55
- Yanıt Olarak İşaretleyen Naomi N 18 Şubat 2011 Cuma 04:46
11 Haziran 2008 Çarşamba 14:01
1

Oylamak İçin Oturum Aç

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ACCESS', @locallogin = NULL , @useself = N'False'
GO

8 Mayıs 2008 Perşembe 05:56

Yanıtlayıcı

Tüm Yanıtlar

1

Oylamak İçin Oturum Aç

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ACCESS', @locallogin = NULL , @useself = N'False'
GO

8 Mayıs 2008 Perşembe 05:56

Yanıtlayıcı
0

Oylamak İçin Oturum Aç

I'm getting error 7399 when I try to browse the tables in SQL Server Management Studio (sql server 2008). I setup the linked server via the gui. Do I put "access" under product name?

p.s jet 4.0 oledb provider works fine in linked server to mdbs.

26 Mayıs 2008 Pazartesi 20:29
1

Oylamak İçin Oturum Aç

i've followed all those steps but it keeps giving me an error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EAFILE" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EAFILE".

I have SP2 installed... Any ideas?

29 Mayıs 2008 Perşembe 11:52
5

Oylamak İçin Oturum Aç
HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:

- Install the 2007 Office System Driver: Data Connectivity Components on your server.
here is the link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

- Make shure you have SQL SERVER 2005 Standard installed. IT DOESN'T WORK'S ON SQL EXPRESS. No mather what you do. It only works on the standard versions...

- Install SQL SERVER SP2

- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess

- Use this T-SQL template (replacing by your own values) to create a connecto to the Linked Server:

Code Snippet

-- ==============================================
-- Add Linked Server Access 2007 ACCDB template
-- BY JOSÉ ALVES @ CCG - UMINHO - PORTUGAL
-- ==============================================

EXEC sp_addlinkedserver
    @server = N'Your Linked Server Name',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @srvproduct = N'Access2007',
    @datasrc = N'C:\path\to\your\db.accdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'Your Linked Server Name',
    @useself = N'TRUE',
    @locallogin = NULL,
    @rmtuser = N'Your Linked Server Name',
    @rmtpassword = ''
GO

-- List the tables on the linked server
EXEC sp_tables_ex N'Your Linked Server Name'
GO

-- Select all the rows from table1
SELECT * FROM [Your Linked Server Name]...table1

I hope this helps you. To me it was a big headache but i figured out after many tries...

Greetings from Portugal
- Yanıt Olarak Öneren hoffmanc 11 Ocak 2009 Pazar 02:55
- Yanıt Olarak İşaretleyen Naomi N 18 Şubat 2011 Cuma 04:46
11 Haziran 2008 Çarşamba 14:01
0

Oylamak İçin Oturum Aç

@rmtuser = N'Your Linked Server Name',

can be replaced with

@rmtuser = @@servername,

Awesome job!

11 Ocak 2009 Pazar 02:55
0

Oylamak İçin Oturum Aç

Jose

Thank you for your wonderful post inrelation to a linked server to an access 2007 database. You mentioned that this was not possible with SQL express 2005. Does it work with SQL express 2008?

Thanks in advance for any help.

Neil

6 Mart 2009 Cuma 21:51
0

Oylamak İçin Oturum Aç

I'm getting the same 7399 error when using Microsoft Access 2007 as as Linked Server in SQL Express 2008.

Please tell me there's a way to get this to work in SQL Express??

Greatly appreciate any input that can be provided! Thanks you -

Ryan D. Hatch

29 Nisan 2009 Çarşamba 17:46
0

Oylamak İçin Oturum Aç

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ACCESS', @locallogin = NULL , @useself = N'False'
GO

thanx
it works

18 Eylül 2009 Cuma 05:19
0

Oylamak İçin Oturum Aç

This worked GREAT for me and saved me TONS of time! Thank you SO much!

I have one question.... When I open the linked server in the management studio, I don't see any tables listed. Keep in mind that in this scenario, I'm using Access 2007 as a pass-through agent to other databases that have proven difficult to access directly by SQL Server. So my access db contains a series of linked tables to other external databases (non-sql).

Using your example query above, I am clearly able to access all of the tables. But I'm wondering if there is some trick to get the tables to be listed in the management studio GUI to make enumerating/finding things a little easier.

I can't emphesize enough how much time you just saved me! I had previously been under the impression that in order for the linked external tables to be visible to SQL I would have to create access queries, which do seem more easily visible in the management studio UI's. I was setting down to create hundreds of those, but no more!

I look forward to your reply!

Thanks!
Toby Hosterman

15 Ekim 2009 Perşembe 22:05

Oylamak İçin Oturum Aç

Hello,

I have same issue, I wanted to create linked server to Access 2007.

Can this be possible on SQL Server 2005 Developer Edition with SP3 instance

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

I have tried all above suggestions but still i am getting below error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS".

Please help!!!

Thanks in advance
Sunny

3 Kasım 2009 Salı 22:47

Oylamak İçin Oturum Aç

The following script creates a linked server (to Access 2007 db) on SQL Server 2008.

In SSMS Object Explorer there is a tree view of tables and views in the Access database. Automatic scripts can be created for example to SELECT from tables/views by right clicking on the object.

Let us know if works for you.

-- SQL Server 2008 CREATE Linked Server script

EXEC master.dbo.sp_addlinkedserver @server = N'LINKED_ACCESS_2007', @srvproduct=N'ACCESS 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'F:\data\Access2007\Northwind2007.accdb'

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

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

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

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

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

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

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

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

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

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'LINKED_ACCESS_2007', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Kalman Toth SQL SERVER & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Düzenleyen Kalman Toth 10 Kasım 2017 Cuma 16:34

21 Kasım 2009 Cumartesi 16:48

0

Oylamak İçin Oturum Aç

I just got this working on SQL Server 2005 express edition.
Added a key (but I'm not sure if this is what made it work) ..

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0\DisallowAdhocAccess

value 0

4 Ocak 2010 Pazartesi 05:04
0

Oylamak İçin Oturum Aç
I came across Jose Alve's ("IID_IDBCreateCommand") error messages when trying to use SQLServer Express 2005 SP3 and dynamic queries using OPENDATASOURCE() SQLServer Express 2005 does not have a link to the ACE Driver in the registry where it looks for that connection (and security settings).

It Can be done for SQLServer Express 2005 without installing the data connectivity components (so long as office 2007 is installed)

I found the solution for that error here:
http://www.integralwebsolutions.co.za/Blog/EntryId/283/Importing-and-using-Excel-data-into-MS-SQL-database.aspx

Short Answer for fixing SQLServer Express 2005:
Enter the registry info below or run a reg file with the following info

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001

Restart SQL Server Services

Rod Nurk's solution above may help with SQL Server 2005 Express and SQL Server 2005 when using the Jet 4.0 Driver (at least initial releases). I saw a bug report that people had to add that key for the provider to allow ad hoc access even if it was opened up system wide by the surface are configuration tools. It may have been fixed in further Service Packs. Dunno. I was querying fine with Jet 4.0 with an excel file on SQLE 2005 SP3 and it did not have that registry key present.

Note: I also came across OPENDATASOURCE remaining with a handle open to the file when failing on the Microsoft.ACE.OLEDB.12.0 Provider and giving the IID_IDBCreateCommand error. If that happens you have to find a way to release the handle before anything can work. logout, reboot, restart sql server, etc. Until the file is released, If the provider is present and configured, the server will return the (null) provider error.
- Yanıt Olarak Öneren lespaa 10 Şubat 2010 Çarşamba 19:23
10 Şubat 2010 Çarşamba 19:22
0

Oylamak İçin Oturum Aç

I used the script below difying only linked server name and db path, but I'm still getting the 7339 error when trying to list the tables in SSMS. Any clues?

****** Here's the error

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TT_Access" reported an error. Access denied.

Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41

Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TT_Access".

**************Here's the script
-- SQL Server 2008 CREATE Linked Server script

EXEC master.dbo.sp_addlinkedserver @server = N'TT_Access', @srvproduct=N'ACCESS 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'H:\TT\TT.accdb'

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

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

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

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

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

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

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

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

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

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'TT_Access', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

17 Şubat 2011 Perşembe 16:34
0

Oylamak İçin Oturum Aç

Yup, AllowInProcess did it for me, tx

5 Ekim 2011 Çarşamba 15:30
0

Oylamak İçin Oturum Aç

thanks! thanks!

31 Temmuz 2013 Çarşamba 05:56
0

Oylamak İçin Oturum Aç

Obrigado José!

8 Ağustos 2019 Perşembe 11:16

En iyi yanıtlayıcılar

How to create linked server to Access 2007 accdb file?

Soru

Yanıtlar

Tüm Yanıtlar