LINKED SERVERS and MS ACCESS - 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 2000 Forums

SQL Server Administration (2000)

LINKED SERVERS and MS ACCESS

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-27 : 07:04:59

hello,
I am trying to link an access database via linked servers but I get the following error messages :

OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

my linked server properties look like :

Product name : Access
Data source: TEST
Provider string:
Location:
Catalog: Y:\Data.mdb

through enterprise manager I can expand the linked server and see the tables !?

any suggestions ?

cheers.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-27 : 07:42:19

It's probably the mapped drive letter Y. The SQL Server will (most probably) not have this drive letter mapped to anything. Change it to the UNC path and it should work.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-27 : 07:46:15

no, I'm still getting the same error messages.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 12:23:38

So you can see the table in Enterprise Manager but not in Query Analyzer? What is the command that you are running in QA?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-01-27 : 15:51:55

Is you Query Analyzer logged in the with the same account as EM? Also, how do you have the linked server set up? Is it using a hard-coded account. My guess is that you are using the "current logins security context" and have a different login in QA then EM.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-28 : 06:13:11

Tara, thats correct, the query I am running is :

SELECT * FROM OPENQUERY(TEST, 'SELECT * FROM table1')

Derrick, I am logged into both em and QA with the sa account.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-03 : 05:44:03

Tara, can you help me with this one ?
the error message I am now getting is :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources