Programmed MailMerge from Access

[jhowley58]

I've developed a multi user Access (mdb) site with a central Tables database. All the users access the main tables as linked tables.

The tables are well normalised. Maybe too normalised.

I've experimented with binding queries to documents but it just takes too long to obtain all the data across the network,

Recently I tried using locally built temporary tables for the document datasource. It is still not satisfactory though. Database locking conflicts appear. (VMS RDB was never like this!)

Now I know that it is possible to export a table to a disk file that acts as the datasource, using the access design screens. Can this be done programmatically?

Any help out there?

John

ps Also, is there a bugfix yet for the pagelocking which occurs when access is fired up from a shortcut?

J.

 

[tunsarod]

I too found the process of doing a Word MailMerge based on a back-end database table and a Select query too slow. So I built a mail-merge solution using the CreateObject and GetObject methods to open and control a Microsoft Word Application. It prints letters much faster than a MailMerge process does and by automating Word you can have it work in the background unseen, or have each document visible for on the fly editing and even automate saving the documents with individual filenames.

I am writing a FAQ on this topic including a working procedure example code. I just have to test it to be sure it works because I've modified it for general use from my original highly tailored design.

I'll post you on it when i've submitted the FAQ, hopefully within the next 24 hours.

Rod

 

[Shamous]

Try 'Bookmarking' your word document with the control names on the form you are using to send the letter. For example: You may have a controls:

txtFullName
txtAddress
txtCity
txtState
txtZip

you want the values of these contols to show up on your word document. Open a blank doc, where you would put the above information put bookmarks. From the command bar click Insert/Bookmark. A box will appear, just type the name of the control that is on your form - exactly. (please read up on bookmarks in then Word help. It pretty easy to use)

Once that is done, save your document. Where your data will be you will see a symbol that looks like 'I'.

Now put a command button on your Access form and use the onClick Event use [Event Procedure]. Cut and Paste the following:

Dim objword As Object
Dim objWordDoc As Object
Dim ctlAny As control
Dim DirString As String

Const wdDoNotSaveChanges = 0
Const wdAllowOnlyComments = 1

On Error Resume Next

Set objword = CreateObject("Word.Application&quot

'What is the Operating System
Select Case fOSName()
Case "Windows 2000"
DirString = "c:\documents and settings\" & CurrentUser() & "\my documents\"
Case "Windows 98"
DirString = "c:\my documents\"
Case Else
End Select
objword.Documents.Add
Set objWordDoc = GetObject("[Path and DocFile]&quot
For Each ctlAny In Me.Controls
With objWordDoc.Bookmarks
If .Exists(ctlAny.NAME) Then
If Not IsNull(ctlAny.Value) Then
.Item(ctlAny.NAME).Range.Text = ctlAny.Value
End If
End With
Next

objWordDoc.Protect wdAllowOnlyComments, , "[AnyPassword]"
objWordDoc.SaveAs (DirString & strName(i))
objWordDoc.PrintOut Background:=False
objword.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
objword.Application.Quit SaveChanges:=wdDoNotSaveChanges
Set objword = Nothing
Set objWordDoc = Nothing
Exit Sub

I DID NOT CHECK THIS FOR ERRORS. I TOOK THIS CODE FROM AN APPLICATION I WROTE.


Paste the code below into a new module. This code tells Access what version of windows you have, Since Windows 2000 handles directories different.


'**************************Start code here***************************
Private Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128
End Type

Private Declare Function apiGetVersionEx Lib "kernel32" _
Alias "GetVersionExA" _
(lpVersionInformation As Any) _
As Long

Private Const VER_PLATFORM_WIN32_WINDOWS = 1
Private Const VER_PLATFORM_WIN32_NT = 2

Function fOSName() As String
Dim osvi As OSVERSIONINFO
Dim strOut As String

osvi.dwOSVersionInfoSize = Len(osvi)
If CBool(apiGetVersionEx(osvi)) Then
With osvi
If .dwPlatformId = VER_PLATFORM_WIN32_NT And _
.dwMajorVersion = 5 Then
strOut = "Windows 2000 "
End If
If (.dwMajorVersion > 4 And _
(.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS And _
.dwMinorVersion > 0)) Then
strOut = "Windows 98"
End If
If (.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS And _
.dwMinorVersion = 0) Then
strOut = "Windows 95"
End If
If (.dwPlatformId = VER_PLATFORM_WIN32_NT And _
.dwMajorVersion <= 4) Then
strOut = &quot;Windows NT &quot;
End If
End With
End If
fOSName = strOut
End Function
' ********** Code End **********

 

[ottograham]

Dear Tunsaroud:

Please let me know when the FAQ entry is done.

Dear Shamous:

I'm trying your approach and am getting a Compile Error - END WITH without WITH. Also, do I need to set any other specifics such as file directory or name, or does it automatically insert into the open document with the Bookmarks?

Thanks.... Scott

 

[jhowley58]

Shamous,

Your method is fine for simple single form output. However I'm producing letters for many customers from the same document.

What I've done now is to build a temporary table from the complex query and export that table to a textfile using the .transfertext method. I use GetObject to fire up the word application. It decouples Word from the Access database OK.

The more I look at Access with linked tables the more unhappy I am. I guess I should port this lot over to MSDE or sql server. Trouble is I used DAO - which in hindsight was one big mistake..

 

[Shamous]

Thats been our problem. Access is ok, but it pulls you in and we have been using it for 6-7 years. SQL Server is better with Access 97 front end. We are in the process of converting now. The best is a completely browser based application using SQL Server and ASP pages or other.

Don't sell DAO short. ADO is better for portability, but nothing beats DAO for quick and dirty programming, especially when the boss wants an application yesterday.

Rusty

 

[jhowley58]

Shamous, LOL!! I am the boss. I want stuff I can reuse. ) AS you say DAO is quick and dirty.. I crossed from Dec OpenVMS 3/4 years back and got hold of the DAO book instead of ADO. With ADO I could have upsized this stuff. Now It's looking like a horror story..

BY the way, using the compact and repair database tool , my application software reduces from 33Mb to 18 Mb. The separate tables database reduces from, say, 5 to 4 Mb. Why is there such a wicked reduction?

John