The Database Has Been Placed In A State By User ‘Admin’ on Machine….

[KarinG]

Hi everyone.

We just switched from Access 2016 to Access 365. My database is now giving this error when I try to do a mailmerge: "The database has been placed in a state by user ‘Admin’ on machine ... that prevents it from being opened or locked."



There is no one else using this database and there is no lock file showing. Do I need to change the connections now that it is using 365? Could that be causing the issue?

This is what I currently have (and where the error is occurring):

       docLetters.MailMerge.OpenDataSource Name:=CurrentDb.Name, LinkToSource:=True, _
                  Connection:="TABLE tblAuthorizerLetterFinal", SQLStatement:="SELECT * FROM [tblAuthorizerLetterFinal]"

Code has these statements at the beginning:
Dim db As DAO.Database
Set db = CurrentDb
Code has these statements at the end:
db.Close
Set db = Nothing

I have been struggling trying to fix this for a while so any help you can give is very much appreciated!

Karin

[ranman256]

Do you have the db open? If so, close it, then run mailmerge (from word)
you dont need the DBO code to run a mail merge.

[KarinG]

Hi.
Thank you for the reply.
There is a button on my form which will allow the user to do the mailmerge and it will also update a whole bunch of fields in the database.
This code was working in Access 2016. Not sure what changed in Access 365 to break it.
Karin

[Gicu]

I would suggest you modify the code you use to replace the mail merge record source with a text file. I use a modified version of Albert's easy mail merge https://hitechcoach.com/downloads-ma...asy-word-merge but basically all you need is to export a query or a temp table (your tblAuthorizerLetterFinal) as text using Docmd.TransferText then pass the name of the file to the mailmerge line:

.MailMerge.OpenDataSource Name:= strMailMergeFileName, ConfirmConversions:=False, ReadOnly:= _
        False, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False
  'where strMailMergeFileName is a string variable holding the newly created text file

Cheers,

[KarinG]

Thank you Vlad. That is getting me closer. It is no longer crashing, but the mailmerge output has no data, only field names. I am assuming I am doing the DoCmd.TransferText incorrectly. This is what I am using:

DoCmd.TransferText acExportMerge, , "tblAuthorizerLetterFinal", strMailMergeFileName

[Edgar]

You might want to consider this:
I decided to discontinue the use of MailMerge for generating doc and docx files due to various reasons. Instead, I found out that implementing a custom function, which opens word documents, creating a copy of the file and facilitating value replacement in the copy, proved to be significantly more reliable.

It was also remarkably easier for my users to design their own doc and docx templates, because they just had to use some placeholders in the places where the values were to be put, instead of all that MailMerge configuration, constant undocumented issues and weird behaviors happening randomly for no apparent reason. It was much better to let the user create their own doc/docx file and just add their placeholders. It also stopped being necessary for me to intervene whenever a new template was created or modified. I could provide example code if you want to do it that way.

[moke123]

Like Vlad, I've used Albert Kallal's super easy word merge for 10+ years. I've made some modifications to my own liking and it's always worked flawlessly. Highly recommend it, even if only to read through his code to see how things work.

[Gicu]

@Karin: don't use acExportMerge as the parameter, just use acExportDelim (for a comma delimited csv file); I always used saved specifications in case some fields needed to be "tweaked".

Cheers,

[KarinG]

I looked at Albert Kallal's word merge and it needs code change to work on a 64 bit system. I don't know how to do that.

I would like to get Vlad's export to text working, as that seems the simplest solution.

I did a manual export to text of the table with the data and saved the export specification - I presume it is saved in Access somewhere? I called it "Export Specification".

When I run my program I am getting a pop up asking about Header Record Delimiters. It is using tab as the field delimiter and enter as the record delimiter.



When I click OK I get run time error 5922: Word was unable to to open the datasource. I checked the datasource and there is data in it.

Can anyone tell me what I am doing wrong? Here is my code:

        strMailMergeFileName = "M:\AADL\Authorizer\Authorizer Database TEST\Exports\MailMerge\temp.txt"

        DoCmd.TransferText acExportDelim, "Export Specification", "tblAuthorizerLetterFinal", strMailMergeFileName, False
        
        Set appWord = CreateObject("Word.Application")

        Set docLetters = appWord.Documents.Open("M:\AADL\Authorizer\Authorizer Database TEST\Letter Templates\Authorizer - Approved.docx")

         ' execute mailmerge
         docLetters.MailMerge.OpenDataSource Name:=strMailMergeFileName, _
                  ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False

Thanks for your help!
Karin

[Gicu]

Hi Karin,
Would you be able to upload a sample of your db that includes the tblAuthorizerLetterFinal table (no sensitive data, just one or two dummy records) and a copy of the Word template)?
I would suggest you try a CSV delimited export first; I think what you need is to set the last parameter in the TransferText to True (HasFieldNames), so please try this:

strMailMergeFileName = "M:\AADL\Authorizer\Authorizer Database TEST\Exports\MailMerge\temp.txt"


'DoCmd.TransferText acExportDelim, "Export Specification", "tblAuthorizerLetterFinal", strMailMergeFileName, False
 
DoCmd.TransferText acExportDelim, , "tblAuthorizerLetterFinal", strMailMergeFileName, True
 
Set appWord = CreateObject("Word.Application")


Set docLetters = appWord.Documents.Open("M:\AADL\Authorizer\Authorizer Database TEST\Letter Templates\Authorizer - Approved.docx")


' execute mailmerge
docLetters.MailMerge.OpenDataSource Name:=strMailMergeFileName, _
                  ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False

Cheers,

[KarinG]

Vlad, I tried doing the changes you suggested and I am still getting errors.
I have tried to attach a small version of my database and the Word document.
Authorizer Database TEST.zip Authorizer - Approved TEST.zip

[Gicu]

I'll have a look shortly, but the Word template is missing from the attachment.
Cheers,

[Gicu]

Also, as the file you uploaded seems like a regular front-end linked to your back-end I cannot see the tblAuthorizerLetterFinal table, would you please include that (just one or two dummy records so I could test the Word doc against the export)?

Cheers,

[moke123]

I looked at Albert Kallal's word merge and it needs code change to work on a 64 bit system. I don't know how to do that.

To be honest I didn't know how either but I just figured it out (I think). I tested it in 64 bit and it works. yea me.

Replace this block of code:

Private Declare  Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

With this block of code:

Private Declare PtrSafe Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As LongPtr, _
ByVal lpBuffer As String) As Long

[KarinG]

I forgot to upload the backend! Sorry! Here it is:
Authorizer Database_be-TEST.zip

The Word template is there, were you able to see it? Or do you want me to upload it again?

moke123 - Thanks!

Karin