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

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 ?
JP_GobletAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
You can't - exclusive means exclusive, NO other user has access to the database.
0
AndyAinscowFreelance programmer / ConsultantCommented:
I'm surprised it worked with Access 97.
0
JP_GobletAuthor 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 ?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

AndyAinscowFreelance programmer / ConsultantCommented:
<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)
0
JP_GobletAuthor 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
0
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0
JP_GobletAuthor 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
0
AndyAinscowFreelance programmer / ConsultantCommented:
I agree, it is a hack.
Exactly why do you need the locking.
0
JP_GobletAuthor Commented:
why do i need locking ? for the usual reason : i have to exclude the possibility that 2 users modify the same data simultaneously
0
AndyAinscowFreelance programmer / ConsultantCommented:
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).  
0
JP_GobletAuthor 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.
0
AndyAinscowFreelance programmer / ConsultantCommented:
I assume that two databases (one for the general table, the other for the details) is out of the question.
0
JP_GobletAuthor Commented:
completely out of question ...
0
AndyAinscowFreelance programmer / ConsultantCommented:
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?
0
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0
JP_GobletAuthor 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.

0
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JP_GobletAuthor 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.
0
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.