Solved

ODBC connect to *.mdb on mapped network drive?

Posted on 1999-01-06
11
2,199 Views
Last Modified: 2012-05-04
I'm trying to display the results of a query in an HTML-page that accessible in the intranet. That's why I created *.idc and *.htx files to display the results.

I also set up the data source in ODBC (in the Control Panel).

"Everything" works fine as long as the database resides in c:\whatever. Of course, the database would "normally" be stored on the server. I mapped the directory to X: and changed the data source in ODBC.

However, I'm now getting the following error message:
===============
Error Performing Query

*   [State=S1000][Error=-1032][Microsoft][ODBC Microsoft
Access 97 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.
===============

What could be the problem? Directory was readable for everyone. I'm using Access 97 (as U C above :-), Win NT 4.0 and Peer Web Services 4.0

Any ideas appreciated!
0
Comment
Question by:Thesan
  • 3
  • 3
  • 2
  • +3
11 Comments
 

Expert Comment

by:T38Jet
ID: 1972732
I've always had to put the database on the same machine as the IDC file to make those things work. Even with the DSN setup to connect to a database on a mapped drive that everyone can get to. I don't think they like mapped drives.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1972733
I don't think your basic problem is related to the fact that your access database is on a mapped drive.  I use nt 4.0, and access 97 with odbc dsn's against access databases on mapped drives on NT 4.0 servers all the time, without problem.  If you notice in oyour DSN, or if you are using something like the .connect property in VB, the actual complete with path file name is included in the dsn for access.mdb's Tha'ts why you shouldn't see the 'unknown' indication if the dsn is correct.  You also need creation rights on the mapped drive, because you will create an *.ldb file (locking crap) when you try to access the mdb.  I'd check to see if you have the full set of rights to the shared folder.
0
 

Expert Comment

by:cmg05
ID: 1972734
I get the same problem when I run my local version of my page trying to access a data-base on the server.

If you are developing on a local server like me I think you will find when you post your WEB on the real server the problem will go away.

I have to develop my pages on my PC, with a dummy data base, on my local drive, and when I upload it I just change the server address in my connect statment.
0
 

Expert Comment

by:cmg05
ID: 1972735
The error is more to do with the fact that multiple users are trying to access the DB rather than security probs, you should note, that you should never have to accualy run your access database, this will cause more problems, if you need to work with the data from an access terminal, creat another replica Database, but delete the data table and create a new "linked" table which has the same name, but is linked to the real database.  Use this DB to access the data instead of interupting WEB access.
0
 

Author Comment

by:Thesan
ID: 1972736
I'm not using ASP (.connect), I am just using .htx and .idc files.

This will not be uploaded since it is for the INTRAnet and it IS on the "real server" - however, the folks here don't want to run IIS on their main (file) server for reasons of stabilitz and performance. That's why IIS/PWS is supposed to be installed on another machine but (of course) has to access the main database.

I set the rights to "full control for everyone" (on a dummy directory, of course :-)), but it still doesn't work.

Cymbolic: So your "web server" is running on the server, right? Your files are on the same server?
What did you enter as DSN? The mapped drive letter or an absolute network path? I tried both but neither one works :-( The entry is "correct", because I chose it using the "Browse" button - no chance to make a mistake there! I also tried setting access to "read-only".
Also, at the current stage, there isn't anybody else accessing those files!
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 3

Accepted Solution

by:
NullTerminator earned 50 total points
ID: 1972737
IIS runs under the IUSR_MACHINENAME account.  You have to do two things to make this work. 1) Change he password on the IUSR_... account to a known password and not the random generated name used by NT. 2) create a local account on the server where the mdb file exisit with the same name and password as the IUSR_ account from the web server.

Also, OK its three things,  go to the management console for the default website on the web server, click 'Directory  Security" tab and the Edit button next to "Allow Anonymous Access"  Be sure "Automatic password synchronization is turned on.

Use an absolute network path to establish your DSN because the mapped drive letter may not be valid for IUSR_  account.  Especially true if the machine is rebooted and no one logs onto the console.

'\0'
0
 

Author Comment

by:Thesan
ID: 1972738
sounds good :-)

Trouble is: it still doesn't work :-((

This doesn't seem to be a problem with access rights! To check, I logged on as IUSR_MYCOMPUTERNAME at the NT login. I can see and open the *.mdb files with this account.

In a browser, I still get the same error message I posted above (and yes, I did "adjust" the options in PWS and also changed the ODBC-source to an absolute network path (strangely, you cannot "pick" it by browsing, but have to manually enter it!!). Would it be possible to create the mapped drive for the IUSR_... account and choose "reconnect at logon"? Would that correctly map the drive if PWS uses the account??)

Any (more) ideas?? :-(
0
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1972739
Yes, if you log on as IUSR_  and map the drive, then PWS should see the mapping, but realise if the machine is rebooted or some one else logs on the mapping might not be valid.

Another way would be to create a script for the .default user to open the mapping for iusr_ .  The script  (batch file) should have the following line

Net use x:  \\theDBServer\theShare   iusrPassword  /user:IUSR_MYCOMPUTER
0
 

Author Comment

by:Thesan
ID: 1972740
ohhhh... that's not very nice (having to map sth for everybody just to make PWS happy)

Also, "the Admins" changed their mind and installed IIS on the server - not the "simple queries" work fine, BUT
the database contains linked tables from another database. In order to make the database work on all workstations in the network (regardless of mapping), the linked tables are linked as "\\theServer\Apps\Database\DB\somename.mdb"

For some reason (?), ODBC can't handle those "links". I keep getting the error message:
=============
Error Performing Query

*   [State=S1000][Error=-1032][Microsoft][ODBC Microsoft
Access 97 Driver] The Microsoft Jet database engine cannot
open the file '\\theServer\apps\DB\linkedfile.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
=============

Any ideas about that one? Shouldn't be related to mapping or access rights, because the linked table is in the same directory as the "main database"... any ideas?
0
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1972741
So are you saying that IIS is on the same server as the DB?  Mapping is out of the picture?   IUSR_ must have permissions to the folder in which the DB lives, read, write, create.  It needs to be able to create the lock file  (LDB)

1) Access 97 has two versions of the drivers.  I haven't been able to tell them apart.  But if someone with the 'a' drivers has the db open, others with 'a' drivers can open it.  If someone with 'b' drivers has it open, those with 'a' drivers can't. We have two groups of people using a db on webserver.  IIS is in group 'a'.  If a 'b' user has it open IIS fails.  If IIS has it open group 'b' fails. It sucks. If IIS has it open and group 'a' tries, no problem.

2) Resolve first that you can work with the tables local to the first database without problem. Make sure for the initial testing that no other users are connected to the mdb.  If an LDB is present try to delete it.  If you can no one else is active.

3) Test the 'linked' table configuration.  

4) It shouldn't be a problem for admins to make common mappings for the users.  Set it up in there profiles.  This is a common default for every on haviing a common say u: mapped to sys volume.

5) Since for IIS the db1 is linked to db2 in the same directory, don't specify an absolute path.  Only specify db.  Current directory will be assumed.

Have fun,  Just do it one step at a time.  Don't try to link an mdb to an mdb through ODBC, usually it just hangs.

'\0'
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6875595
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.  Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now