We help IT Professionals succeed at work.

with DAO 3.5, how to open in read-only mode an already opened Access file ?

JP_Goblet
JP_Goblet asked
on
Medium Priority
368 Views
Last Modified: 2013-11-20
i use MFC and DAO 3.5 to open Access 2000 files; if the file is already opened by another user in exclusive mode, i get (and intercept) an exception, then i retry to open but this time in read-only mode (so that i can at least read some infos in the file) :

m_pDatabase->Open(strPath, FALSE /* bExclusive*/, TRUE /* bReadOnly*/);

--> i get again the same exception saying that the file is already opened in exclusive mode (DAO error code = 3045);
yet that method worked with dao 3.0 and access 97 databases.
So, how can i , with dao 3.5, open a file in read-only mode even if it's already opened in exclusive mode ?
Comment
Watch Question

AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
You can't - exclusive means exclusive, NO other user has access to the database.
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
I'm surprised it worked with Access 97.

Author

Commented:
so, sorry to insist, if a file is already opened in exclusive mode, i have no way to read its tables ? (yes it worked with acces97 files);
could it be possible at least to open the file in non exclusive mode, but to lock some tables so that only one user can modify these tables ?
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
<but to lock some tables so that only one user can modify these tables>

Do you mean table structure or the data?
Do you mean both user A and user B IN PRINCIPLE can modify data but when A is modifying then B can not.  (If it was always read only for B then user/group security should be what you require)

Author

Commented:
Do you mean table structure or the data? --> the data

Do you mean both user A and user B IN PRINCIPLE can modify data but when A is modifying then B can not. --> that's it : only one user at a time can modify the data
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
This MAY be of use.
Create a table.  When user logs in, check table.  If it has a flag then the database is 'exclusive'.  If no flag then this user has priority and write a flag to prevent other users.  When user logs out remove the flag.


If you are worried about user A writing to multiple tables and user B getting only part of the update (A not finished) then wrapping the writes in a transaction should be what you require.

Author

Commented:
it means that i have to manage locking by myself ... not very satisfactory, and dangerous ;
i keep the question open for a few hours to see if someone can come with a better solution, otherwise i will accept your answer
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
I agree, it is a hack.
Exactly why do you need the locking.

Author

Commented:
why do i need locking ? for the usual reason : i have to exclude the possibility that 2 users modify the same data simultaneously
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
Access should do that itself.
When data is being written the page with the record on should be locked automatically.  (You may require a pessimistic lock -  lock when edit starts as opposed to optimistic - lock when update starts).  

Author

Commented:
locking just one record is not enough ... i tell you the whole story : in fact the application is a CAD program, in which the drawing elements (points, lines, ..) are stored in a database (with a point table, a line table, ..., with relations between these tables). When a user opens a drawing i have to lock all the tables; except one table containing general informations on the drawing; this is the table i would like to read even if the drawing is already opened.
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
I assume that two databases (one for the general table, the other for the details) is out of the question.

Author

Commented:
completely out of question ...
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
Thought so, just a wild idea.

Just to clarify a little further.  Is the locking refering to the record(s) with one drawing but that other records for an un-opened drawing in the same table would be available for writing?
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
Does it actually require the Access 2000 format (as you say it worked the way you wanted with Access 97) ?

My understanding is 2000 provided more UI support but the tables only had UNICODE text support as an extra.  An Access 2000 FE will work with an Access 97 BE.

Author

Commented:
one database contains just one drawing (so when a user opens a drawing, the other drawings are not locked);

the Access2000 format has been choosen since about one year for some reasons (i can't remember which ones now ... at this time it was a project to switch to ADO drivers rather than DAO, but finally we kept DAO because it offers much better performance with Access databases); anyway turning back to Access 97 is not an option.

Freelance programmer / Consultant
CERTIFIED EXPERT
Commented:
one database contains just one drawing (so when a user opens a drawing, the other drawings are not locked);


OK.  That gives some hope.
As you know Access has an ldb file (with the same name as the mdb) for locking information.
Assume your app opens a table (provide a dummy table for this purpose - incidentally it offers a performance 'improvement' because access doesn't have to create the ldb file when other tables are opened) and keeps it open for the duration of your app.  So if the ldb file exists some other user has the database open - open as read only for any other user.  The ldb file is removed when no tables are opened so if it doesn't exist you can open as read/write for that user.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
OK - so i never opens databases in exclusive mode, but before opening a database i watch for the presence of the associated ldb file; if present i know the database is already used  by another user. This has just a little drawback : if the application was shut off inadvertently, the .ldb file can still be there and will prevent reopening the drawing.
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Commented:
If the ldb is present open the database and one table, then close them.  See if that will remove the ldb file left by the kill application.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.