Connect to Read-Only Access database as linked server SQL 2005

Hi all,
I am trying to add an Access database as a linked server in SQL Server
2005.
For a "regular" database, everything works fine. BUT (there's a
but...) the database I'm trying to access is a "read-only" database
(i.e.: folder in which db is located is read-only). When I open it
from Access, it warns me that the db cannot be modified, bla bla bla.
I can access the data, but cannot make modifications to the structure
of the database (add queries, change tables, etc.).
So... my question is... Did I forget any parameter in the
sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
method with this database I get the message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
returned message "The Microsoft Jet database engine cannot open the
file '\\CompletePathAndDatabaseName.mdb'
. It is already opened exclusively by another user, or you need
permission to view its data.".
However, like I said, the same db can be opened with access 2002 from
the same station, the same user, etc.
exec sp_addlinkedserver @.server='MyServer',
@.srvproduct='Access',
@.provider='Microsoft.Jet.OLEDB.4.0',
@.datasrc='\\CompletePathAndDatabaseName.mdb'
exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
@.useself='false',
@.rmtuser='Admin',
@.rmtpassword=''
Regards,Hi,
I have replicated your problem and it works perfectly for me. Are you
sure that you don't have some issues with the Access lock file. If you
shut down all applications/clients/servers accessing this Access file,
does the .ldb file still show up?
Jonathan
fabien.turcotte@.fidomobile.ca wrote:
> Hi all,
> I am trying to add an Access database as a linked server in SQL Server
> 2005.
> For a "regular" database, everything works fine. BUT (there's a
> but...) the database I'm trying to access is a "read-only" database
> (i.e.: folder in which db is located is read-only). When I open it
> from Access, it warns me that the db cannot be modified, bla bla bla.
> I can access the data, but cannot make modifications to the structure
> of the database (add queries, change tables, etc.).
> So... my question is... Did I forget any parameter in the
> sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
> method with this database I get the message
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
> returned message "The Microsoft Jet database engine cannot open the
> file '\\CompletePathAndDatabaseName.mdb'
> . It is already opened exclusively by another user, or you need
> permission to view its data.".
> However, like I said, the same db can be opened with access 2002 from
> the same station, the same user, etc.
>
> exec sp_addlinkedserver @.server='MyServer',
> @.srvproduct='Access',
> @.provider='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='\\CompletePathAndDatabaseName.mdb'
> exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
> @.useself='false',
> @.rmtuser='Admin',
> @.rmtpassword=''
>
> Regards,
>sqlsql