SQL Server 7 does not deal very well with secured Access mdb's. you
can set up a linked server to a secured Access database as long as you
create the appropriate registry entries. however, you can only query
databases from that one mdw. if you have multiple workgroup files, as
most people do, you're SOL. the other odd thing is that the registry
location you need to write to is not the same one used by the
workgroup administrator (wrkgadm.exe). i never tested using a secured
mdb with openrowset -- that may be the problem. try using a linked
server with the registry settings and see if that works. you'll be
able to tell right away by running the sp_tables_ex against the linked
server name. if you get an error, then the linked server or the
registry key was not configured correctly.
bottom line is, you're better off not using Jet security if you need
to query your Access databases from SQL Server. it's a giant PITA, and
has gotten no better in SQL Server 2000.
>Thanks for your view,
>but it does not apply here because I AM running SQL7 on *my own* NT WS
>machine. It is a development environment where I am trying to port an
>application from using Access97 database files (via DAO) to SQL7 using
>And since my Access97 files contain a lot of data for testing I need
>to move them over using a connection through OLEDB to the Access file.
>I am using Query Analyzer and the Acces97 file is on my PC as is the
>OLEDB system driver and the workgroup file.
>I am logged in to my machine on an admin account and I am using sa as
>the SQL7 login for these initial tests.
>So I should be fine but I am not.
>On Sun, 12 Nov 2000 09:50:31 -0500, "BP Margolin"
>>Not sure, but it sounds to me like you are overlooking that when you ask SQL
>>Server to read from an Access database, that the drive mapping must be from
>>the SQL Server box, not from your PC. Remember that the query is executing
>>on SQL Server ... it is not executing on your PC ... so the drive mapping
>>must be the drive mapping as seen from the SQL Server box.
>>General best practice in all such cases is to avoid drive letter mappings
>>entirely and stick to UNC's. Also make sure that the account that SQL Server
>>is running under has operating system permissions to the Access database ...
>>for example, if you want SQL Server to see a file on another box, you do NOT
>>want to be running SQL Server under the Local System Account.
>>Please reply only to the newsgroups.
>>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>>can be cut and pasted into Query Analyzer is appreciated.
>>> I have this problem:
>>> There are a number of Access97 mdb files I need to read from SQL7.
>>> (Need to move data into SQL7).
>>> The files are protected by a workgroup mdw file and user logins with
>>> varying permissions. I have created a SQL7 login which maps directly
>>> to one of these users (username, password), then I have tried using
>>> the syntax of Books On Line section "ole db provider for Jet" but as
>>> soon as I try to read these files I get an error saying that the
>>> workgroup file cannot be found or is opened exclusively by another
>>> Why is this so? The file sits next to the default system.mdw in
>>> c:\winnt\system32 in my PC (W2000 professional). And I am the only one
>>> using my PC right now...
>>> I have also tried the registry hack suggested in BOL with and without
>>> full path to the workgroup file, but no go.
>>> I have executed sp_addlinkedserver and sp_addlinkedsrvlogin also, but
>>> the same problem sufaces always. :-(
>>> What am I missing in this sentence:
>>> select a.*
>>> from openrowset(
>>> 'c:\path\test.mdb';'username';'password',tablename) as a
>>> If I test this syntax towards the employee.mdb that ships with
>>> MSOffice97 and use username=admin, password='' then it returns rows so
>>> the basic syntax seems OK.
>>> It is almost as if the OLEDB driver is not connected to my workgroup
>>> file (as says the error message). But how can I specify it better?
>>> Mybe someone can give the details of the complete procedure to do this
>PGP: My public key is available at the following locations: