Connect to Access Db from SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Connect to Access Db from SQL

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-18 : 06:33:08

Hi ,
I need to connect to Ms-access database which is present in another machine(Server) and use the tables in it for querying.. also make use of soem joins....
I have seen openrowset function but, i guess it works only if access database is present in the local machine.
My local server which does not have any Ms-Acess installed should be able to connect to another machine which has Access DB in it..

Is this Possible.. Can anyone suggest a solution.

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-18 : 08:35:52

Please let me know if the question is not poper... i really need a solution...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 14:55:54

quote:
Originally posted by swathigardas

Hi ,
I need to connect to Ms-access database which is present in another machine(Server) and use the tables in it for querying.. also make use of soem joins....
I have seen openrowset function but, i guess it works only if access database is present in the local machine.
My local server which does not have any Ms-Acess installed should be able to connect to another machine which has Access DB in it..

Is this Possible.. Can anyone suggest a solution.

cant you add access db as a linked server to your sql server db and use OPENQUERY or four part naming to get data?

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-20 : 02:35:54

quote:
Originally posted by visakh16

quote:
Originally posted by swathigardas

Hi ,
I need to connect to Ms-access database which is present in another machine(Server) and use the tables in it for querying.. also make use of soem joins....
I have seen openrowset function but, i guess it works only if access database is present in the local machine.
My local server which does not have any Ms-Acess installed should be able to connect to another machine which has Access DB in it..

Is this Possible.. Can anyone suggest a solution.

cant you add access db as a linked server to your sql server db and use OPENQUERY or four part naming to get data?

Thanks Vishakh for the answer. i would try it out

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-21 : 06:17:16

i have found this SP - Sp_addlinedserver to created a linked server.
i dont know what should be the value given to @datasrc as the mdb file exists in some other system. can u please suggest me what should be the value given.
The system name is hydpc075
and the path where mdb file exists in that system is
'C:\Documents and Settings\swathi.g\Desktop\Miscellanoeus\CardV3.mdb'

EXEC sp_addlinkedserver
@server ='exe',
@srvproduct ='Access 2003',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc =?

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-21 : 06:55:09

is there any other way other than making the access file as shared

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-21 : 07:59:15

this is the code i used for creating linked server by making the mdb file as shared.

EXEC sp_addlinkedserver
@server ='exe',
@srvproduct ='Access 2003',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc ='\\hydpc075\access\CardV3.mdb';

.. and used open query like this
SELECT *
FROM OPENQUERY(exe,
'SELECT AlarmDate,AlarmTime FROM AlarmData');

But still i get an error -

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "exe" returned message "The Microsoft Jet database engine cannot open the file '\\hydpc075\access\CardV3.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "exe".

Please help me out in this

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-22 : 02:45:08

can anyone please suggest me a solution.

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-23 : 02:30:08

The open query() function works when i am logged in usin gSql Server authentication. But when i login with windows authentication. i get the above mentioned error.. Is there any way To solve this problem for windows authentication.

anilkumarkatta
Starting Member

7 Posts

Posted - 2009-04-23 : 03:47:42

hi swathi,

Please let me know how you are trying to connect to access. java, .net or anyother..?

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-23 : 04:35:53

i am not connecting through java neither .net. i am using linked server option in sql and created a linked server with access database from whivh i am trying to query data, It workd fine when i am logged in using sql server authentication. but fails when using windows authentication.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 04:40:47

You have to impersonate a user for SQL Server to see Access data.
See procedure sp_addlinkedsrvlogin

E 12°55'05.63"
N 56°04'39.26"

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-04-23 : 05:16:15

I have used this statement
sp_addlinkedsrvlogin 'exe','false','Vertexcs\Swathi.g','Admin','13'.

This was executed sucessfully without any error.

But when i try to query using openquery function i still get the same error.

SELECT *
FROM OPENQUERY(exe,
'SELECT AlarmDate,AlarmTime FROM AlarmData');

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "exe" returned message "The Microsoft Jet database engine cannot open the file '\\hydpc075\access\CardV3.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "exe".

i guess i have gone wrong in using sp_addlinkedsrvlogin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:18:51

Or the access file is in use?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:22:00

Have you included the MODE option in your connection string?

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts