split databases and mailmerge docs

[darren via AccessMonster.com]

Hi

I am building my first split database for users with multiple FEs and its
just occurer to me that this will leave me facing the prospect of setting up
not only each users front end but also a set of merge documents for them. I
am therefore looking to give them a fe with a set of merge docs and
programmativally set their datasources....... but it's not going so well...
(note also that the front end may be password protected.)

Public Function fnMergeIt()
On Error GoTo Err_fnMergeIt

Dim db As Database
Dim objWord As Word.Document
Dim doc As String
Dim dbPath As String
Dim strConnection As String

dbPath = CurrentProject.FullName
doc = CurrentProject.path & "\TestSL.doc"

strConnection = "DSN=MS Access Database;DBQ=" & dbPath & ";FIL=MS
Access;PWD=Password;UID=admin;"

Set objWord = GetObject(doc, "Word.Document")

' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the calling database.
objWord.MailMerge.OpenDataSource _
Name:=dbPath, _
ReadOnly:=True, _
LinkToSource:=True, _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM [tmptblSL]"

' Execute the mail merge.
objWord.MailMerge.Execute

Exit_fnMergeIt:
Set objWord = Nothing
Exit Function

Err_fnMergeIt:
If Err.Number = 247 Then
MsgBox " The merge document is already open."
Else
MsgBox "fnMergeIt: " & Err.Number & " - " & Err.Description
End If
Resume Exit_fnMergeIt

End Function

When run this open the word doc but I get a dialog bow 'data link properties'
pop up (which I don't want). When I click ok to the execute command then
comes into effect and opens up another non-merged version of the letter as
well???

If I disable the execute command then I think I'm left with the original with
the correct datasource.

How can I stop the data link properties box appearing and am I missing
anything from my logic?

Thanks

Help! (please)

 

[Albert D.Kallal]

I would suggest that you DO NOT let word connect tot he actual database. It
can be the source of MANY problems.

I have a sample download that works on secured databases, and does not EVER
let word connect to the actual data (so, no prompts, no passwords etc. to
worry about). With my sample you can also make any form a SINGLE record
merge to a "list" of templates with one line of code.

You can find my example here
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[darren via AccessMonster.com]

Hi Albert

I checked your sample download out. It's not quite what I'm looking for but
is very creative. I get your points about using a csv. It does solve a few
issues. I think I'll opt for a csv output and then merge to this as per your
suggestion.

Are you aware of any advserse side effects of this method e.g. field size
constraints with memo fields, etc.

Thanks

 

[Albert D.Kallal]

Are you aware of any advserse side effects of this method e.g. field size

constraints with memo fields, etc.

Yes, memo fields with hard cr/lf (carriage returns, and a line feed) are a
often a problem.

Further, the code in my example will not output uni-code...so, for
international, or non English language applications, it is a problem...

 

[ghjfg]

ʲôÊÇ°®£¬°®ÊÇʲô£¬ÄãÏàÐÅÂð

 

[darren via AccessMonster.com]

Hi Albert

It is an English only application so in this respect it is not a problem but
memo fields are used.
Short-term I'm working the merge off of a number of temporary tables. However,
creating one self-defining external file sounds better as long as the this
does not create any other issies.

Any other suggestions.