May 29, 2009 at 9:43 am
Hello
I have been googling long and hard, and still have no solution. I have an Access 2000 MDB file that is protected with a database password. I am trying to setup a linked server so that I can query the contained tables from within a TSQL stored procedure. My code to setup the linked server is pasted at the bottom. It runs without error. The problem now is that if I try to select anything from it, I get this error:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System Database=system.mdw;" has not been registered.
I have checked my MDAC components, and the windows installer tells me I already have the current version. Following another KB article, I have also modified my registry to include the full path to the system.mdw file. Nothing seems to work.
Is it possible to query, within TSQL, against an Access 2000 database that has a database password? If so, how? I am running 32bit XP Pro with the latest service packs and windows updates. I am also running MS SQL Server 2005 Express, and the access database file is on the same computer.
Edit: Additionally, having read This Post I tried running this instead:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="E:\utility.mdb";UserID=Admin;Password=;Jet OLEDB:System Database=system.mdw;Jet OLEDB Database Password=secret')...image
in which case I see these errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
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 "(null)".
Any ideas?
Thanks!
--------------------
EXEC master.dbo.sp_addlinkedserver @server = N'UTILITY', @srvproduct=N'Access 97', @provider=N'Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System Database=system.mdw;', @datasrc=N'E:\UTILITY.mdb'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'UTILITY',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'UTILITY', @optname=N'use remote collation', @optvalue=N'true'
May 29, 2009 at 9:56 am
Firstly, Shouldn't the system database be "System Database=system.mdb" instead of "System Database=system.mdw"?
Secondly, you doesn't seem to set the remote login and password for the server login. remote login should be "Admin" and remote password should be password of the access database.
--Ramesh
May 29, 2009 at 11:51 am
Thank you for your reply.
No, I believe MDW is correct. That is the name of the file. system.mdw and I found it here:
C:\Documents and Settings\phemmer\Application Data\Microsoft\Access\system.mdw
Secondly, there is no username and password for the access database other than I guess Admin and [blank] by default. The Access database has a password on it, this is a database password, not a specific user account.
I am able to link to and select from the access database if I remove the database password manually from within MS Access application but that does not solve the problem as I won't be able to do that in production.
Edit: Additionally, if I change the line you are referring to:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'UTILITY',@useself=N'True',@locallogin=N'mysqlaccount',@rmtuser=N'Admin',@rmtpassword=N''
and try to select from the linked source, I get this:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System Database=system.mdw;" has not been registered.
May 30, 2009 at 2:12 am
I am sure that the remote login password must be the access database password, it is used the map the local sql login with that of the access database user (which is Admin).
Books Online has mentioned the following registry change, try and see if it changes anything.
Excerpt from Books Online:
To access a secured Access database, configure the registry by using Registry Editor to use the correct Workgroup Information file that is used by Access. Use Registry Editor to add the full path name of the Workgroup Information file that is used by Access to this registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
--Ramesh
May 31, 2009 at 10:30 am
I had found that registry change, and tried it, to no avail.
June 1, 2009 at 9:37 am
Hi, .mdw is a config file. You need to connect to your .mdb file. Here are some specs:
June 1, 2009 at 9:53 am
right, but the problem is there is a database password (I am not talking about a user account, name and password, rather, the access database password itself.) I can link to it and query it just fine if this password is manually removed from the MDB database file, but with the password in place, nothing seems to work.
June 2, 2009 at 2:41 am
Hi, also you could try to connect, first throw ODBC (here you have a tutorial about it:
http://www.simongibson.com/intranet/odbc/
), with your mdb. If you don't have errors then you could try this doc:
http://doc.ddart.net/mssql/sql70/oa-oz_5.htm
Look at the password parameter. I think you need to put it in your connection string
July 11, 2010 at 10:30 am
Did you ever get a solution for creating a linked server to an access 97 database with a "database password"? Trying to do the same thing... Having the same problems...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply