SQL Server - Link between SQL Server 2005 and MS Access

Asked By Shobha Kumawat on 06-Oct-08 09:43 AM

Hi all,

My application has database in SQL Server 2005. I have an independent database in MS Access and want to query data (extract data) from MS access data base through Sql server query.

Any suggestions please

 Thanks in advance

Link between SQL Server 2005 and MS Access - Amrita Gaur replied to Shobha Kumawat on 06-Oct-08 09:50 AM

Hi Shobha,

You can use this query to import data from MS Acces to SQL server 2005.But it only work when your Access database is not password protected

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','E:\Userstest.mdb';

'admin';'',Table1)

 

Thanks,

Amrita

check this for connectivity - C_A P replied to Shobha Kumawat on 06-Oct-08 09:55 AM

Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task|Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)

Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.

You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.

Linked server

You can add a Access database to a SQL Server database. Following steps are -

(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.

– OR –
Issue this statement in QA of SQL Server-

EXEC sp_addlinkedserver @server = ‘DBName’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’

You have added it as linked server now. Then, use full qualified name to issue your statements.

its not working with password protected database - Shobha Kumawat replied to Amrita Gaur on 06-Oct-08 09:57 AM

amrita,

your query is not working when ms access database is password protected.

do you have any such query

solution - Perry replied to Shobha Kumawat on 06-Oct-08 10:29 AM

Hi,

Both OPENROWSET and OPENDATASOURCE should be used only for accessing external data for ad hoc situations, when it is not possible to configure a permanent linked server. These functions do not provide all of the functionality available from a linked server, such as management of login mappings, ability to query the linked server's meta data, and the ability to configure various connection settings such as time-out values.

The arguments of OPENROWSET and OPENDATASOURCE do not support variables. They have to be specified as string-literal. If variables need to be passed in as arguments to these functions, a query string containing these variables can be constructed dynamically and executed using the EXEC statement.

Try below: it works

decalere @Server varchar(50)
decalere @User varchar(50)
decalere @Pwd varchar(50)

set @Server = 'yourservername'
set @User = 'UserName'
set @Pwd = 'Password'

USE MyDB
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB',@Server;@User;@Pwd,
'SELECT * FROM MyDB.dbo.MyTable ') AS a
GO

Also refer below links you will definately get your ans

http://support.microsoft.com/?kbid=246255 and http://www.databasejournal.com/features/mssql/article.php/3584751

Regards,

Megha