Word 2007 customising fields in the data source

[Teresa]

If I want to customise the fields in a data source (Word 2007) I can either
open the source as a Word table and make the changes there, or I can open the
main document that the data source is attached to, and use Edit Recipients.

However, if I have created the data source from scratch when using the Mail
merge Wizard, it's saved as an .mdb file. Is there any way to open an .mdb
file INDEPENDENTLY of a main document (because the same data source is used
with multiple main doocuments) and WITHOUT opening it in Access?

I tried clicking on Select Recipients on the Mailings tab (with no main
document open) then Edit Recipient List, but this only gives me the Data
Form. What I want is the Customise Columns button (which you get if the main
doc that the data source is attached to is open). The View Source button,
which I've used many times in older versions of Word to display the data
source in a table, does appear on the Data Form using the method described
above - but it's greyed out!)

Thanks

 

[Peter Jamieson]

If you created this .mdb via Word Mailmerge, then the Customize Columns
button should be available when you try to edit the .mdb.

Since it isn't, that suggests to me that Word may be recognising the
..mdb as an Access database but not as an Office Address List, which is
simply a .mdb with a very specific structure.

FWIW if you try to edit an OAL directly with Access, you may in any case
damage its structure.

I don't know of any other way to edit the OAL (and more specifically, to
change the column names etc.) available out-of-the-box in Office. There
could well be free database editing utilities that can do it easily but
it's not an area I'm familiar with.

What you may be able to do is create a new .mdb that that copies all the
data from the existing one but is recognised as an OAL by Word. You can
try the following macro - I have hardly had time to test it properly but
it does seem to do the job here.

There are some general instructions for setting up Word macros on Graham
Mayor's site at http://www.gmayor.com/installing_macro.htm

In this case you should
a. close any Word mail merge main documents that are linked to the OAL
b. make a copy of the OAL
c. when you have copied the macro code into a module in the VB Editor,
- ensure that you have put any "wrapped" lines of code back together
- use the VB Editor's Tools->References menu option to locate the
following 2 libraries and check them:
- Microsoft ActiveX Data Objects 2.7 Library
- Microsoft ADO Ext. 6.0 for DDL and Security

(If you don't have libraries with those names, choose the
closest, e.g. Microsoft ActiveX Data Objects 2.6 Library

- in strSourceOAL, specify the pathname of the copy OAL you made in
step (b)
- in strTargetOAL, specify the pathname of the OAL you want to
create. The .mdb must not already exist, but the folder it is in must
already exist
d. run the macro
e. if all goes well, create a new mail merge main document, attach the
new data source, and check that the data you expect is there. Then try
editing it and see if the Customize Columns button is now available. If
it is, then you can replace your old data source and continue.

'----------------------------------------------


Sub CreateOalAndCopy()
' Peter Jamieson 2009. Needs testing and error management
' Specify the OLE DB Provider.
Const strProvider = "Microsoft.ACE.OLEDB.12.0"

' In the following line, specify the full path
' name of the Office Address List .mdb you are
' trying to fix
Const strSourceOAL = "c:\a\source.mdb"

' In this line, specify the full path name
' of a .mdb file. The folder must exist
' but the .mdb will be created
Const strTargetOAL = "c:\a\target.mdb"

Dim objCatalog As ADOX.Catalog
Dim objProcedure As ADOX.Procedure
Dim objTable As ADOX.Table
Dim objTables As ADOX.tables
Dim objView As ADOX.View
Dim objViews As ADOX.views
Dim objCommand As ADODB.Command
Dim objParameter As ADODB.Parameter
Dim strCommandText As String


Set objCatalog = New ADOX.Catalog
Set objCatalog.ActiveConnection = objCatalog.Create( _
"Provider='" & strProvider & "';" & _
"Data Source= '" & strTargetOAL & "';" & _
"Jet OLEDB:Engine Type=5;")

Debug.Print objCatalog.ActiveConnection

Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objCatalog.ActiveConnection

' Office_Address_List should be the only Table
' Create a copy of it
strCommandText = " SELECT *" & _
" INTO [Office_Address_List]" & _
" FROM [Office_Address_List]" & _
" IN '" & strSourceOAL & "'"
' do it
objCommand.CommandText = strCommandText
objCommand.Execute

' Office Address List should be the only query.
' Create it
objCommand.CommandText = _
" CREATE VIEW [Office Address List] AS" & _
" SELECT *" & _
" FROM [Office_Address_List]"
objCommand.Execute
'objCatalog.views.Append "Office Address List", objCommand

Set objCommand = Nothing

Set objCatalog.ActiveConnection = Nothing
Set objCatalog = Nothing

End Sub

'----------------------------------------------


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Doug Robbins - Word MVP]

If the INDEPENDENCE from a mail merge main document is not an absolute
criteria, start a new mail merge main document and attach the data source to
it. I suspect that the view data source button will then be enabled and
after customizing the fields (I assume that you mean the data in the fields,
not the fields themselves), you can then discard the mail merge main
document.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Sela]

Following on from previous post:

It seems that the editing facilities available after clicking 'Edit
Recipients' during a merge) vary depending on *how* the data source was
created and I'd be really grateful if someone could confirm this:

-Data source is an Excel spreadsheet - New Entry & Find available; Delete
Entry and Customise Columns greyed out.
-Word table: none of these buttons are available - all I get is a little
'Data Form' to enter & locate records
-Office Address list - New Entry, Find, Delete Entry, Customise Columns -
all available.

So does this mean that if I want to add and delete FIELDS from the data
source I cannot rely on 'Customise Columns' being available, and may have no
option but to open the original file in Excel or Word? Are the benefits to
creating new data sources as a Word table in the first place, rather than
during the merge - is this more flexible?

Thanks

 

[Peter Jamieson]

It seems that the editing facilities available after clicking 'Edit

Recipients' during a merge) vary depending on *how* the data source was
created

Broadly speaking, this is the case, and Word behaves the way you describe.

if you want to delve into the detail, it's probably better to think in
terms of:
a. the format of the data source
b. the method Word uses to connect to it (could be an internal text
converter, external text converter, DDE, ODBC or OLE DB)

Excel is in some ways a good example because it is possible to connect
to Excel workbooks using an external text converter (although that is
obsolete in Word 2007, the converter available with earlier versions of
Word can be used in some cases).

Also, Excel can save and work with the older .xls format (Excel 2003 and
earlier) and the new .xlsx/.xlsm format (Excel 2007), and there can be
differences in behaviour.

For example, if you connect to Excel using DDE, which a lot of people
end up doing to overcome formatting and other problems, Word lets you
select the Edit option, but then you probably have to realise that the
data source is open in Excel and you have to go and edit it there,
rather than in the Word dialog box. And at that point you could in
theory add a column or some rows. But here, I find that Word doesn't
like that scenario much and seems reluctant to save.

If you connect using OLE DB (which is the default method in Word 2007),
as you say, in Word's dialog you can modify and add Excel records, but
not delete them, and you cannot modify the columns. Further, Word may
impose some editing constraints that Excel would not - e.g. if Word
thinks a column is numeric, you won't be able to enter a date. There are
other potential problems. for example, a cell in Excel can contain a
formula that gets the cell value from elsewhere. In these cases, Word
will probably flag an error if you try to alter the cell value.

So does this mean that if I want to add and delete FIELDS from the data
source I cannot rely on 'Customise Columns' being available

Correct. As far as I know, you only get that option with "Office Address
List" format sources.

and may have no
option but to open the original file in Excel or Word?

Correct - or in Access if it's an Access data source, etc. etc.

Are the benefits to
creating new data sources as a Word table in the first place, rather
than during the merge - is this more flexible?

IMO there is no "perfect" data source for Word mailmerges. For one
thing, I can't think of a single type of data source that does not have
a drawback of some kind. But mostly, the choice of data source is a
compromise between a number of factors, including:
a. who's creating it, and what skills do they have?
b. who's using it, and what skills do they have?
c. how long does the data source need to last (is it a throwaway, or
do you need to retain the data for years?)
d. does other software need to use the same data?
e. should the data source be updateable, and/or usable, by multiple
users at the same time?
f. if the file format breaks in some way, can you get the data out
and/or recreate the file?
g. limits on column count, column size, row count, and performance
h. how much control over sorting/filtering might you need?

....and those are just a few of the considerations.

Word table format does have a couple of advantages over most other types
of data source:
a. it's relatively easy to maintain multiline data in a Word cell.
b. if you need to maintain formatting in the data source that survives
the merge (e.g. colouring text), Word format is pretty much the only
option, although even this relies on a "trick".

On ther other hand, Word table format is limited to 64/65 columns,
connection/merge can be slow, editing Word files with large numbers of
columns /directly/ is not easy, and so on.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Sela]

Peter, thanks for this comprehensive response - it has really helped to
clarify things for me.