Can't read Access db over network

[sherwood]

Two Windows 98 machines, named Office and Lab, are connected in a LAN.
One (Office) has an Access db, MyDataBase.mdb. An application pgm which
uses ODBC, MyPgm, is installed on both machines, and using ODBC
Administrator, on each machine MyData is set up to point to MyDB.mdb.
This has all worked fine for many years.

Office is now replaced by a Windows XP (Home) system. The database is
copied to the new machine, and the application pgm installed. ODBC
Administrator is used to set the data source on the XP machine, Office.
The application works correctly on the new Office.

However, the Windows 98 machine, Lab, is unable to read the Access db.
When ODBC Administrator is used to set the data source on Lab, you get
the message "MS Jet database engine cannot open
\\office\Data\MyDB.mdb. It is already opened exclusively by another
user, or you need permission to view its data."

I believe this message means there is something that Access has set
which is preventing others on the network from seeing the database. On
the Lab machine, if I display the Data directory on Office, I see
MyDB.mdb, among other files. If I attempt to open MyDB.mdb with
Notepad, I get a permission error (I don't expect to make any sense of
the file with Notepad; this is just a test to see if the file is
accessible). I can open other files in the same directory with Notepad
without error.

The folder on Office is set for both read and write access. Since this
is XP Home, there are no other permission settings at the file system
level. MyDB.mdb is not set in Access to allow exclusive open (and is
not open when the error occurs in any case).

What other permission or setting have I missed, to be able to have the
Windows 98 machine, Lab, read the Access file MyDB.mdb from the XP Home
machine, Office?

 

[vdar@msft]

Suggestion:

On the new XP machine open any database.

Click Tools Options / General Tab / insure the DEFAULT OPEN MODE is "Shared"
and Not "Exclusive"

It sounds like that system may be set to open db in Exclusive Mode and
locking others out.

dr

 

[Mastercafe - Juan]

With WinXP always have many problems with permissions.
Have sure that you can access to the main folder of the Office pc and the
main MDB from the LabPC. Try to open without the Office Pc have any
application running.
If don't run you have more permission problems in win xp.
I you can do this, and the problem is when you open on the Office
application, try to modify the open file from Access, normally open access
then File/Open select the file and then on combo OPEN select Shared.
Check your Access/Tools/Options to shared on Advanced page, and put record
locking to edited record.

Check all of this and tell me if run

--
Saludos desde Oviedo (Asturias)

Juan Menéndez
Mastercafe S.L.
www.mastercafe.com
(e-mail address removed)

Si la información recibido te ha servido indicalo con otro post.
En caso de resolverlo por otros medios, indica la solución usada
ayudaras a otros y aprenderemos todos.

 

[vdar@msft]

Correction that would be on the "ADVANCED" tab.

Suggestion:

On the new XP machine open any database.

Click Tools Options / General Tab / insure the DEFAULT OPEN MODE is
"Shared" and Not "Exclusive"

It sounds like that system may be set to open db in Exclusive Mode and
locking others out.

dr

 

[sherwood]

Click Tools Options / General Tab / insure the DEFAULT OPEN MODE is "Shared"
and Not "Exclusive"

As I said, I did that, and the mode was shared.

 

[sherwood]

With WinXP always have many problems with permissions.
Have sure that you can access to the main folder of the Office pc and the
main MDB from the LabPC. Try to open without the Office Pc have any
application running.

I'll certainly agree with your initial sentence! Yes, from the remote
Windows 98 machine, Lab, I can access the folder in which the db is
contained on Office, and I can open any file in the folder EXCEPT the
Access db, using Notepad. That is why I conclude that the problem is
with an Access setting, rather than an XP setting.

 

[John Vinson]

es, from the remote
Windows 98 machine, Lab, I can access the folder in which the db is
contained on Office, and I can open any file in the folder EXCEPT the
Access db, using Notepad. That is why I conclude that the problem is
with an Access setting, rather than an XP setting.

It may still be XP. In order to use an Access database, the user must
have FULL permissions - to read, write, create and delete files - in
the folder containing the .mdb file. Your notepad experiment checks
only the read privilege.

John W. Vinson[MVP]

 

[sherwood]

It may still be XP. In order to use an Access database, the user must
have FULL permissions - to read, write, create and delete files - in
the folder containing the .mdb file. Your notepad experiment checks
only the read privilege.

Agreed. That's why, when the .mdb file does not have even the read
privilege, there is something wrong. Using Windows Explorer does not
show any file attribute that would prevent reading the file. Are there
other attributes or permissions? This is XP Home, so I cannot use
anything but the simple permission settings.

 

[Arvin Meyer [MVP]]

First, be careful of opening Access in other applications. Opening it in
Word, for instance, will corrupt it irreparably if autosave is on. I'd guess
that if Notepad has a permissions problem, then the real issue is
permissions. Access does not set it's own file attributes except that if the
LDB (locking file) cannot be created or destroyed, Access will only allow a
single user on the database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Tom Wickerath]

Hi Sherwood,

Use NotePad from the lab computer to verify that you can create a text file,
in the same folder that the .mdb file is located in, on the office computer.
You should be able to create a text file, close & reopen it, edit it, save
changes, etc. Finally, you should be able to use Windows Explorer from the
lab computer to delete the text file that you created with NotePad. If all of
these tests pass, then you have verified the required RWCD privleges for the
folder in question. As Arvin stated, you should never attempt to open a .mdb
file with something like NotePad or Word.

What version of Access is loaded on the lab and office PC's? Do they both
have service pack 3 installed? Are you running the latest service pack for
the JET Database engine? Can you create a new DB on the lab computer, and
then use File > Get External Data > Linked tables and browse to the office
computer's folder?

Have you used the MDAC Component Checker tool?
http://support.microsoft.com/?id=307255


Next suggestion is a long shot:
http://support.microsoft.com/kb/815142/en-us


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[sherwood]

Use NotePad from the lab computer to verify that you can create a text file,
in the same folder that the .mdb file is located in, on the office computer.
You should be able to create a text file, close & reopen it, edit it, save
changes, etc. Finally, you should be able to use Windows Explorer from the
lab computer to delete the text file that you created with NotePad.

This is a good suggestion; I will try it.

you should never attempt to open a .mdb file with something like NotePad or Word.

Only doing this to try to diagnose the trouble.

What version of Access is loaded on the lab and office PC's?

Access 2000 on Office; Access is not installed on Lab. Don't know about
the SP for Access or Jet.

Can you create a new DB on the lab computer

I didn't think to try this, because Lab just reads the db on Office,
using a local copy of MyPgm with the ODBC data source ->
Office\Data\MyDB.mdb.

Have you used the MDAC Component Checker tool?

No. Remember that everything was working until W98 Office was replaced
with an XP machine. At that time, Access 2000 was reinstalled on
Office. No changes were made to Lab.

 

[Tom Wickerath]

Only doing this to try to diagnose the trouble.

Yeah, but you can compound your problems--without necessarily knowing about
it at the time--by attempting to use something like NotePad or Word to open
an Access application.

Access 2000 on Office; Access is not installed on Lab. Don't know about
the SP for Access or Jet.

Okay, Access 2000 should be patched to service pack 3. The JET database
engine should be service pack 8. Use the instructions shown in this KB
article to make sure that you have the latest service packs installed for the
operating system, Office and the JET database engine:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

Lab just reads the db on Office, using a local copy of MyPgm with
the ODBC data source -> Office\Data\MyDB.mdb.

What exactly is "MyPgm"? Is this a .exe application created in Visual Basic
that reads the .mdb file? If MyPgm is a .mdb file, then I guess that means
that you are running a remote copy of Access from lab. If that is the case,
it does not sound good. On the other hand, if MyPgm is some other application
that can read data from your MyDB.mdb file, via an ODBC connection, then that
should be okay.

Regarding the MDAC Component Checker tool, I'd still use it on both PC's
just to see if there were any problems reported.


Good Luck,

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

PS.
Is there any chance that MyPgm is expecting to see a JET 3.5 database, but
someone might have unknowingly converted MyDB.mdb from the 3.5 file format,
to the 4.0 file format, after installing the new operating system on your
office PC? Normally, you should get an "Unrecognized database format" error
message, if one attempts to use Access 97 to open a .mdb file saved in the
JET 4 file format. However, I'm not sure what kind of error message would be
returned to MyPgm via ODBC in this case.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[sherwood]

Yeah, but you can compound your problems--without necessarily knowing about
it at the time--by attempting to use something like NotePad or Word to open
an Access application.

NotePad does not know, or care, what kind of file MyDB.mdb is. It just
reads bytes. Since it will not attempt to write anything unless I
explicitly tell it to (with Save), it should not change anything.

Okay, Access 2000 should be patched to service pack 3. The JET database
engine should be service pack 8.

In general it's good policy to keep these things up to date, but it
seems unlikely this is the trouble. Access is not being used at all.
The Jet db engine is used, through its ODBC interface, but probably
doesn't manage permissions. And, the app worked fine before.

What exactly is "MyPgm"? Is this a .exe application created in Visual Basic
that reads the .mdb file? If MyPgm is a .mdb file, then I guess that means
that you are running a remote copy of Access from lab. If that is the case,
it does not sound good. On the other hand, if MyPgm is some other application
that can read data from your MyDB.mdb file, via an ODBC connection, then that
should be okay.

MyPgm is a Visual C++ pgm, that uses ODBC to read an Access db. Access
itself is never run on either machine.

 

[sherwood]

Is there any chance that MyPgm is expecting to see a JET 3.5 database, but
someone might have unknowingly converted MyDB.mdb from the 3.5 file format,
to the 4.0 file format, after installing the new operating system on your
office PC?

No, no conversion was done, and the db is accessed only through the
ODBC interface. The app on Office has no problem reading and writing
the db, just as before the change from W98 to XP.

 

[Tom Wickerath]

NotePad does not know, or care, what kind of file MyDB.mdb is. It just

reads bytes. Since it will not attempt to write anything unless I
explicitly tell it to (with Save), it should not change anything.

I understand this quite well. Nevertheless, my advice stands as given. While
you are obviously intelligent enough to never attempt to save the file using
NotePad, this doesn't mean that someone else might not try the same trick and
hit the Save toolbar button, creating burn't toast out of a perfectly good
database. By the way, did you perform the NotePad tests I recommended to
verify RWCD privileges?

The Jet db engine is used, through its ODBC interface, but probably
doesn't manage permissions.

Excuse me? If User Level Security (ULS) was implemented, then JET most
definately will manage permissions. You don't skirt ULS by accessing the JET
database using a non-Access application! I don't know if ULS was
specifically implemented on MyDB.mdb, but even if it was not, by default
Security is in place. It's just that each user is considered the Admin user
with a blank password if ULS was not implemented. What this means is that Jet
MUST know where to find the System.mdw file (or whatever the name of the
expected workgroup information file is), even if you did not implement ULS.

And, the app worked fine before.

I understand that point very clearly. No need to keep repeating it.

MyPgm is a Visual C++ pgm, that uses ODBC to read an Access db. Access
itself is never run on either machine.

Okay, thank you for clarifying that. This was not obvious from reading the
earlier posts.




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[sherwood]

By the way, did you perform the NotePad tests I recommended to
verify RWCD privileges?

Not yet. The person who can do this is away this week (the machine is
on the other side of the country).

Excuse me? If User Level Security (ULS) was implemented, then JET most
definately will manage permissions. You don't skirt ULS by accessing the JET
database using a non-Access application! I don't know if ULS was
specifically implemented on MyDB.mdb, but even if it was not, by default
Security is in place. It's just that each user is considered the Admin user
with a blank password if ULS was not implemented. What this means is that Jet
MUST know where to find the System.mdw file (or whatever the name of the
expected workgroup information file is), even if you did not implement ULS.

This sounds like a promising area to investigate. How do I check
whether ULS was implemented (it would not have been done deliberately)?
I installed Access 2000 on the XP machine, Office. I am guessing that
the W98 machine, Lab, uses the copy of Jet on Office. Could using
Notepad to try to open a file invoke Jet?

Since the app works on Office, I suppose Jet on Office is finding
System.mdw.

 

[Tom Wickerath]

Not yet. The person who can do this is away this week (the machine is

on the other side of the country).

The lab machine is on the other side of the country??? That's a pretty
important fact to be revealed this late in this thread! From your previous
discussions, it would be perfectly normal to assume that the Office PC and
the Lab PC were in the same building.

How do I checkwhether ULS was implemented (it would not have been
done deliberately)?

Then it's probably not an issue. However, as I said before, security is used
on all JET databases, it's just that it is transparent to the user. This
means that the registered workgroup information file, usually named
System.mdw, must be found in the expected folder in order for Access to
start. (I believe later versions of Access, such as A2002, will automajically
regenerate a missing System.mdw file, but I'm pretty sure that A2000 will not
do this).

I am guessing that the W98 machine, Lab, uses the copy of Jet on Office.

I don't know either. I also don't know if it is using it's own copy of
System.mdw, or is using a remote copy. If it is using a remote copy, and the
path is not identical to what it used to be, then I would expect a problem.

Could using Notepad to try to open a file invoke Jet?

No. I don't believe so.

Since the app works on Office, I suppose Jet on Office is finding
System.mdw.

Yes. I believe you can demonstrate this to yourself. Temproarily rename
System.mdw to System.old (without having Access or MyPgm opened). Then see if
MyPgm still works. I'm kinda thinking that it will not work.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

You know, since you've mentioned that MyPgm is a Visual C++ app. that uses a
JET database, you've really posted your question in the wrong newsgroup.
"Access" does not belong in the subject line of this post, since you are not
using Access.

In any case, here is a suggestion to try out. From a second PC in the
Office, try to set up an ODBC connection to the Office PC. Record the exact
steps that are required in order to get a successful connection. Send these
steps by e-mail to your remote contact in the Lab. Have them try first from a
different PC in the lab. If all works, have them delete the original ODBC
connection on the lab PC and re-establish it.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

['69 Camaro]

Hi, Sherwood.

the Windows 98 machine, Lab, is unable to read the Access db.

It appears that the Windows 98 machine doesn't have Windows security
permissions to the MDB file. It's possible, although extremely unlikely,
that User-level Security permissions are interfering, but you'd have to set
User-level Security with a secure workgroup file that may have been lost
with the new load on the Windows XP Home computer, but it sounds like Access
security has not been applied. If it were, then if the Windows 98 computer
user tries to open the database in exclusive mode and this particular Access
user doesn't have permissions to open the database exclusively via the new
default workgroup file, then he'll get a "permission denied" error.

MyDB.mdb is not set in Access to allow exclusive open

Your C++ application isn't using Access's default open mode when connecting
to the MDB file. It's using the DSN's settings.

Check the Windows XP Home DSN to ensure that the DSN isn't causing the
database to open in exclusive mode, and the C++ program keeping this
connection open -- even when you think the database is closed. It's hard to
tell that the C++ program still has a lock on the MDB file, since there's no
LDB file when the user has the MDB file open exclusively. Check Windows
Task Manager and see if the MSAccess.EXE process is still running when you
think the C++ program has disconnected. If it's still running, then there
is at least one computer connected to that file.

When the Windows 98 computer becomes available for testing, reboot the
Windows XP Home computer and then try to connect to the database with the
Windows 98 computer, without the Windows XP Home computer ever having opened
the C++ program first, and see whether or not the Windows 98 computer is
still locked out of the MDB.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.