Detect if Access in lock state (1 Viewer)

Blueskies

Registered User.
Local time
Today, 13:02
Joined
Oct 23, 2009
Messages
69
Hi

I have an Access database (which only I use) with a table.

I have a Word doc which uses this table as it's data source.

I launch the Word doc with via VBA from Access and it works well unless I've been tinkering with the form designs in Access, in which case I get the message:

Error has occurred: the database has been placed in a state by user xxxxx on machine yyyyyy that prevents it from being opened or locked.

It's no problem to get around this - I just close Access and re-open it.

However, is there a bit of VBA that could detect if Access is in this state before I launch the Word docs? - something like application.dirty = true or something?

Thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:02
Joined
May 7, 2009
Messages
19,169
does word open a Connection back to the db, say for mailmerge, etc.
 

Blueskies

Registered User.
Local time
Today, 13:02
Joined
Oct 23, 2009
Messages
69
Hi

Yes, the word doc's mail merge source is one of the tables.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 19, 2002
Messages
42,970
There may be a way but I don't know what it is.

Most people run the mail merge from Access rather than the other way around and that would solve the problem. Access doesn't play well with others. So, you need to be more conscious of saving objects after you modify them and saving records once you dirty them.
 

Blueskies

Registered User.
Local time
Today, 13:02
Joined
Oct 23, 2009
Messages
69
OK thanks.

I'm doing it this way because a client supplied the word doc and likes to change it periodically, so it seemed to make more sense to leave it as word rather than make it into an Access report.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
My question is whether this table that Word is going to use is also the table that the opened form was going to use? If so, is the form set for either Optimistic Locks or No Locks? If this form touches the table and has Pessimistic Locks, that might be an issue.

Since this is a single-user case, you COULD try to have Word test for the existence of a .LDB file. However, if I recall correctly, an Exclusive Open might not create a lock file since there would be no internal locks to arbitrate.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 19, 2002
Messages
42,970
I've built Access apps that populate THOUSANDS of word documents using OLE. MailMerge is too restrictive. The users manage all the Word documents themselves. They just have to remember to update the cross reference table for that document if they add or remove a bookmark. They are restricted to using the fields that the application knows about. But in one of the apps, I used an ultra normalized schema which actually allows them to define new fields on the fly since each field was stored as a single row in the table.

So, even with MailMerge, you can allow the user to modify the text of the document. They just can't change any of the merge fields easily.
 

Blueskies

Registered User.
Local time
Today, 13:02
Joined
Oct 23, 2009
Messages
69
Thanks for the replies guys.

Doc_Man: The table that Word uses isn't one the form uses.

The Access form prepares the data is various ways - adding fields, indexes etc, then opens Word to allow merging from there.

Pat: Sounds like you are using methods beyond what I've done before, I'd need to research this!


I'm just surprised there isn't an easy way to tell if I'm going to get a locked message before opening Word. I suppose I could loop through all the forms to check if they are dirty.....
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
I'm just surprised there isn't an easy way to tell if I'm going to get a locked message before opening Word.

When you ask the question specifically that way, I have a different answer: That isn't the way that most of Office is set up. If you are going to get an error, you will get an error - but Office products want you to trap the error after-the-fact. If you have error trapping code in place and know the error you will get, you can put special error trap routines in place to detect that the error occurred and plan for a non-catastrophic pathway to "back your way out" of that situation. That is the primary philosophy of most of the Office products that use VBA.

The fact that this error will occur in another member of Office complicates the situation greatly, particularly since MailMerge is part of the "black box" nature of Word. I.e. MS doesn't publish their internal code on any of this so we have NO WAY of knowing how things will react. Since we can't see the internal MailMerge code past a certain point, we don't know a completel list of what conditions will trigger its errors.
 

Users who are viewing this thread

Top Bottom