Mail Merge database gets disconnected

[Idahoguy]

I can’t seem to find the answer or reason for this problem. I have an address label document into which I insert a date consecutively into all 30 labels using a Mail Merge Date field. The ODT document is in my Write folder and the ODS data file I link to is in my Calc folder. Once I create the document I then print it. The print process of course asks if this is a merge document, to which I click yes and the dialog comes up showing the data source. The print process goes OK with a different date in each label for one sheet of 30 labels. I save and close the document.

The next time I open the document, after I have updated the ODS data file I get the question about updating all the fields. I answer yes. But when I go to print it I get an error message that the connection to the database cannot be completed. This has been a hit and miss problem, mostly after I switched from OpenOffice to LibreOffice. I can’t remember from one month to the next if I select saving or not saving changes when I close the merge document.

Any idea why the connection to the database gets broken sometimes, but not other times? About the only way I can reestablish the connection is to delete the Date field and insert it again. I also use the Next Record field.

Along with this how do I delete an extra entry in the connections list in the connections dialog?

[Villeroy]

Find the database document (*odb) that is connected with your spreadsheet.
menu:Tools>Options>Base>Databases…
[New…]
Add the database document.

[Idahoguy]

I must be dense! First, I do not find any odb file. When I go to Tools/Options/LO Base/Databases in Calc this is what the link is for the database file: vnd.sun.star.pkg://file:%2F%2F%2FD:%2FMac%2FWork%2FOpen%2520Office%2FWriter%2FDinner%2520Pill%2520Labels.odt/EmbeddedDatabase. The registered name is Daily Date List. If I click on Edit it can’t find the database. However, as of right now the document does print properly. I’ll just have to wait until the next time I go through the process and see if it gets disconnected again.

I did get rid of the redundant database entry though, so thanks for the info that got me there. It makes it less confusing when there is only one registered database name for the file I’m using.

[mikekaganski]

@Idahoguy

First, I do not find any odb file. When I go to Tools/Options/LO Base/Databases in Calc this is what the link is for the database file: vnd.sun.star.pkg://file:%2F%2F%2FD:%2FMac%2FWork%2FOpen%2520Office%2FWriter%2FDinner%2520Pill%2520Labels.odt/EmbeddedDatabase.

This is embedded data sources, available since v.5.1. When you have your ODT saved at the point when you use Mail Merge wizard, you do not create an ODB, but embed the data source definition into the ODT itself. This should (1) let you avoid dependence of third file (you only need an ODT and the data, like ODS); and (2) register the embedded database for you automatically when you open the ODT (so that you may move the two files freely, including to different systems).

Possibly you change relative placement - i.e., where the ODS is relative to ODT?

[Villeroy]

It has been deleted or not transfered to another machine. Easy:

menu:File>New>Database…
[X] Connect to existing database of type “Spreadsheet”
Specify the spreadsheet document (*.ods)
[X] Yes, register the database (to be used in things like mail merge)
Notice the status bar of this database document: [Spreadsheet] [Path/to/your/spreadsheet.ods]
Save the database document, close and forget it. But don’t (re-)move.

If your mail merge still does not work because the name has changed:
Writer-menu:Edit>Exchange Database…
Specify the right database and table.

---

[Spreadsheet.ods] —> [registered Database.odb] —> [Mail Merge Document.odt]

All mail merge documents read data from a database document. In most cases a spreadsheet document is interpreted as if it were a database. Each used range on a sheet is interpreted as a table in a pseudo-database. A mail merge document always reads from a database even when the actual source behind the odb document is not a database at all.

[Idahoguy]

Sorry guys. I left out a piece of information that turns out to be absolutely crucial and is the key to my problem. First, the names and folder locations of the ODT and ODS files stays constant, so losing connection because of link changes isn’t the problem.

What I didn’t mention in my initial post is I have one ODS file, but three ODT files linked to it. What I’ve been doing is opening all three files at the same time by selecting and double clicking in File Explorer. LibreOffice does not like that! After I merge print the first of the three ODT files the next two lose the connection to the database. All three files have the database file embedded.

If I open only one file, print it, and close with either save or not save changes, and open the next one by itself all is good! It looks like my only solution is to open and print all three files one at a time.

I followed Villeroy’s suggestion of creating a separate ODB file and linking that. However that gets converted to an embedded database so doesn’t solve the problem.

Also, how do I force an ODT document to delete an embedded database? I deleted the mail merge fields and deleted the database in the database dialog, and saved the file. But when I opened it again the embedded database link showed up again. Why?

[Villeroy]

But you have no ODB file which is why I told you how to create one within a minute. Once you have one database connection, you can link as many office documents to this data source as you need.

@Idahoguy

I followed Villeroy’s suggestion of creating a separate ODB file

So you did not follow my instructions properly.

@Villeroy

[X] Connect to existing database of type “Spreadsheet”

On that dialog you have 3 options. Create a new database, open a database document or connect to an existing database. You have to choose the 3rd option. Then you choose “Spreadsheet”

[Idahoguy]

Sorry, but that is exactly what I did! The result was a new ODB file in my LO documents folder.

[Villeroy]

@Idahoguy

However that gets converted to an embedded database so doesn’t solve the problem.

So you got a database document with a status bar looking like this:

It should be like this:

[mikekaganski]

@Villeroy: @Idahoguy talks about the embedded data source that was mentioned at

@mikekaganski

This is embedded data sources, available since v.5.1

This has nothing to do with embedded database, even if @Idahoguy used a confusing term.

[Idahoguy]

Not sure why you say I used a confusing term. The end of the entry in Tools/Options/LO Base/Databases shows “EmbeddedDatabase”.

[mikekaganski]

@Idahoguy

vnd.sun.star.pkg://file:%2F%2F%2FD:%2FMac%2FWork%2FOpen%2520Office%2FWriter%2FDinner%2520Pill%2520Labels.odt/EmbeddedDatabase

This, right? Heh, we managed to confuse everyone. This “EmbeddedDatabase”, and the one that @Villeroy mentioned above, are different, and “embedded database” term should only be used in the original sense, that @Villeroy uses it, and this new functionality of embedding ODB inside ODT should be referred as “embedded data source”.

[Idahoguy]

OK, it looks like I’ve got it all figured out! The ODB status bar for the Tables info does show Spreadsheet and the path as you say it should.

The problem apparently is caused by corrupted ODT files. I’m pretty sure the files were moved over from OpenOffice, which in itself should not have caused a problem, I wouldn’t think. But I did edit them, resaved them, edited the Mail Merge fields, deleted and replaced the MM fields, and who knows what else!

All of this handling must have screwed up the documents somehow. Today I started over creating two new clean label sheet documents linking to a copy of the ODB file, which links to the original ODS file I’ve been using. I opened up both ODT files and printed them. All went well as we would expect, correctly reflecting an update in the ODS file. The two ODT files show only the ODB file in database entry dialog. No embedded database is listed.

I guess the end takeaway is sometimes there is no good choice but to start over.

[Idahoguy]

I just ran another test using three test label sheets. All worked well!

Thanks for your help. I had no idea about the use of an intermediate database for mail merge. Somewhat more complicated than what I dealt with when I was using Lotus WordPro.

So far, in my limited use of the Mail Merge function, the only significant improvement I see over WordPro, when it comes to labels, is the Synchronize Labels button. That feature is great!

[Villeroy]

@Idahoguy

I guess the end takeaway is sometimes there is no good choice but to start over.

No, I don’t think so. Please upload a copy of the mail merge document with confidential information removed. Keep at least one mail merge field.

[Idahoguy]

Here it is. Do you want a copy of the ODS and ODB files also or just this one?

Morning Pill Labels - Reasearch Copy.odt (14.3 KB)

[Villeroy]

When you turn on menu:View>Field Names [Ctrl+F9] and remove the word “Morning” for more space, you see that this database field used to be connected to a database registered as “Daily Date list Open”, table “A”, column “Date2”.You get the same information when you hover the mouse cursor over the field and if tool tips are enabled.
So your spreadsheet has a sheet named “A” and a list on sheet “A” has a column named “Date2”, right?
Now you registered the new database document (connected to that same spreadsheet) under some different name.
There are 2 solutions: Either you register that database document as “Daily Date list Open” or you switch the database field like this:
Place the text cursor in the top-left label which is the only editable one. The other labels are linked to the first one.
menu:Edit>Exchange database… double-click the right database name and select table “A”.
Now click the [Synchronize Labels] button to propagate the change to the linked labels.

Third solution: Remove the database field. Select the right database table in the data source window (Ctrl+Shift+F4) and drag the grey column header “Date2” to the place where the removed field was. Or do the same via Ctrl+F2, tab “Database” and choose the right database, table and field.

[Idahoguy]

After doing the database exchange should I not register the database? After I did the exchange on the first ODT, “Morning”, I looked at the list of registered databases. It showed Daily Date List 2 and the path was to the ODB file. I then saved and closed it. I then did the same exchange on the other two ODT documents, “Dinner” and “Bedtime”…

I then opened all three documents from File Manager. The registered database list for each, Dinner and Bedtime, was empty so I clicked on New and added the ODB file and saved and closed. But the registered db list for Morning showed the registered name Daily Date List 2 but the path was now the “vnd.sun./EmbeddedDatabase". I closed it and then reopened all three from File Manager. Now the registered db file for each ODT file is shown as "vnd.sun./EmbeddedDatabase”.All three have Daily Date List 2 for the name.

Now I print one of the three ODT files. All goes correctly. But when I click on Print for each of the other two I get the disconnect error message and when I check the registered db list, it is empty. Obviously, printing any one of the three files obliterates the connection for the other two. If I close the ODT files without saving and open them again, either as a group or individually, they show the registered db. This does not happen with the new ODT files I created as I discuss above.

The forum software deleted the asterisks I had between sun. and /Embedded

[Idahoguy]

More info!
It is not printing that kills the database connection, it is closing one of the documents. I just now did another test. I opened all three ODTs and printed all three while leaving them all open. I then closed one of the three. Then I went to print one of the two still open documents and lo and behold, no data source connection.

This problem is not present with the three new ODTs I created earlier today.