February 23, 2007 at 8:45 am
Hi Folks -
I am trying to create a linked server to an Access 2007 database, but, I can't seem to find the right combination of settings.
Has anyone successfully done this? What setting did you use?
Regards,
Joy
February 23, 2007 at 9:38 am
Does the database exist in SQL 2005 or do you want to import the Access database into a sql database?
February 23, 2007 at 9:57 am
Actually it is an Access 2007 database. I do not want to import the tables. Rather, I would like to create a linked server.
This would allow me to query the Access tables from SQL Server.
February 23, 2007 at 10:23 am
TRY THIS:
Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.
For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'c:\mydata\Nwind.mdb'February 23, 2007 at 10:32 am
That won't work... the file extension is no longer .mdb - in Access 2007 it is now .accb - also the provider type has changed. You can no longer use 'Microsoft.Jet.OLEDB.4.0'
According to connectionstings.com it should be:
February 23, 2007 at 10:50 am
Try this:
In Management Studio you expand the "Server Objects" and then expand "Linked Servers".
Right-Click "Linked Servers" to add a new linked server.
February 23, 2007 at 11:08 am
I have tried that... what I need to know is if anyone has made it work and what setting they used.
February 20, 2008 at 5:17 am
Hi, did u find out how to create a linked server to access 2007 accdb file? If so, pls mail me the syntax. Thanks.
February 20, 2008 at 5:37 am
Hello,
Yes I did figure out how to create a linked server to an Access 2007 database in SQL Server 2005.
First, be sure to install SP2 of SQL Server 2005 (this is important).
Then in SQL Server Management Studio select Server Objects > Linked Servers, right click and select New Linked Server.
Use the following settings:
- In the field 'Linked Server:' enter a name for the linked server.
- Server type: Othet data source
- Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
- Product name: Access
- Data source: (path and name of file ie: C:\Documents and Settings\myname\My Documents\AccessDB\Northwind2007.accdb)
- Provider string: (leave empty)
Then select okay. You should now see the linked server name in the list. Expand down to see the tables if you wish.
Regards,
Joy
February 20, 2008 at 6:01 am
i'm using sql server 2005 express which i recently downloaded, so i guess it already has sp2 or whatever..(i havnt separately downloaded sp2)
However, I did everything else that you mentioned but i still got the error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "generix1" 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 "generix1".
Is this only because of the sp2 thing, or can there be any other reason?
February 20, 2008 at 6:18 am
I don't use SQL Xpress -it's too limited...
February 21, 2008 at 8:06 am
Applying SQL Server SP2 is crucial to being able to created a Linked Server to Access 2007, regardless of the Edition of SQL Server you are using (Express, Workgroup, Standard, Developer, or Enterprise). Applying SQL Server Service Pack 2 makes SQL Server "aware" of Office 2007 products and/or applications.
Run this query against any database within your instance:
select @@version
... results in the first part of the string should read the number as "Microsoft SQL Server 2005 - 9.00.3042.00" or higher will have Service Pack 2 (or greater) applied.
My system returned:
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Hope This Helps,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 21, 2008 at 9:50 pm
i tried the select command mentioned by you. my version is -
9.00.3042
However, i am still unable to connect to Access 2007 accdb. The error message is -
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "generix1" 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 "generix1".
Pls help!
February 22, 2008 at 6:48 am
You may have a permissions issue when attempting to connect to the Access 2007 database. You will need to verify that the SQL Server service account tied to your SQL Server instance has rights to the Access 2007 database. For testing purposes, you may want to copy the Access database to the SQL Server machine, to rule out permissions. (I am assuming that your SQL Server Service account is a local Administrator on the SQL Server machine.)
I found a similar issue, but dealing with an Excel 2007 file in this thread:
Keep us posted on success or failure,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 22, 2008 at 6:58 am
Damon -
Off topic a bit. Is it possible to create a linked server in the express version?
Joy
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply