June 18, 2002 at 9:11 pm
I am trying to link to a secured Microsoft Access database using SQL Server 2000 SP2 (Linked Server). I already have one link to an unsecured Microsoft Access database. From reading the BOL, I know you need to reference the Access .mdw in the Windows Registry of the SQL Server server. Here is the clip from the BOL:
"The Access database is secured and the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\SystemDB registry key is not pointing to the correct Access workgroup file. Secured Access databases have a corresponding workgroup file, including the full path, which should be indicated by the above registry key."
I know this to be the case. However - is there a way to deal with my problem? I really need a second registry entry for the second (secured) .mdb. Have people dealt with this issue in other ways? In the end, I have many MS Access based systems. I would like to be able to create SQL Server views of various tables in these sytems to allow easier access.
I have also tried with the ODBC Linked Server but ran into similar issues.
Thanks to all for their ideas!
Jeff
June 19, 2002 at 4:34 am
Interesting. Your two mdb's use separate workgroup files?
Andy
June 19, 2002 at 12:45 pm
Yes, they use seperate workgroup files. They are from completely different systems. I'm surprised this isn't more of an issue. I would think (personally) that folks would want to link to various .mdbs using differnt .mdws. However, I have never really relied on the .mdw myself. Maybe most DO use just on .mdw.
Thanks again,
Jeff
June 19, 2002 at 4:43 pm
Unfortunately this is a known issue and MS does not plan a change. There reasoning was that this was not intended to be done this way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 5:41 pm
Thanks for the information. Any recommended workarounds?
June 19, 2002 at 5:49 pm
It has been a while but I believe you can set it up so the guest has rights to access the data (but not alter) then the mdw is not needed. You have to login into the admin account of the MDW and make this change then save the database. That would be easiest, not sure of any other feasible work arounds but a really bad one would be setup an MSDE server on another box with a link to the access file and a link from the prod server to this one, but not sure that would even work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 5:54 pm
Thanks! As usual - you are a great help. I think you have had a hand in answering ALL my questions I have posted to date.
So you think a "Guest" account should eliminate the need for the .mdw login? That is actually what I am using. However that makes me think that it may actually be an issue with my domain privliges.
Thanks again.
Jeff
June 19, 2002 at 6:23 pm
Let us know what happens.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 6:24 pm
How about setting up a couple copies of Personal Edition, setting up a linked server to the mdb's from there, create views to it, then link to those from the main server? That would solve the one reg key problem. At the risk of a little complexity!
Andy
June 20, 2002 at 8:07 am
Could you link to all required Access databases from within a single main secured Access file. Have the registry key point to the main Access file and then setup your linked server through that?
I'm not sure if this will work..., just an idea to try out.
DanW
June 20, 2002 at 3:31 pm
Dan - actually I tried that. I couldn't link/import into a new database if the old database was secured.
Thanks for the thought,
Jeff
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply