Word Merge Function Issue...

[AccessARS]

SCENARIO:
I have the following module in my database which merges data from a
temporary table (temp_PIC) in the same database into a COPY of a pre-designed
Word merge document template located on our server...

Function MergeIt()

Dim objWord As Word.Document
Set objWord =
GetObject("\\holnt01\public\Projects-Hollywood\CustomerCareAdministrative\Attendance\Write-ups\PIC Output\PicMerge1.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _

Name:="\\holnt01\public\Projects-Hollywood\S3\Databases\Working\Attendance\Attendance.mdb", _
LinkToSource:=True, _
Connection:="TABLE temp_PIC", _
SQLStatement:="SELECT * FROM [temp_PIC]"
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close

End Function

....The original Word merge document template is already linked to the temp
table therefore in the process when it opens the original prior to making the
copy it prompts …

Opening this document will run the following SQL command:

SELECT * FROM[temp_PIC]

Data from your database will be placed in the document. Do you want to
continue?

YES/NO


....When I click YES it gives me a message that the database is in use and
locked therefore it cannot establish connection.

QUESTIONS:
How can I avoid the initial prompt indicated above on the open of the
original template and what can I do about the database being unavailable
since its obviously going to be open when this process is running?


Thank you in advance for your assistance

 

[Someone]

Hi

The prompt is by design. See this article from Microsoft
http://support.microsoft.com/default.aspx?scid=KB;EN-US;825765.

I would suggest changing from using a Mail Merge to using bookmarks in a
template and populate the bookmarks from your table. Its's more
straightforward.

Re access to your database - does it have permissions to access the db?

M

 

[AccessARS]

Yes, it does have permissions. It's just that the Access database where the
Word merge document is attempting to access the temp table from is where the
code is running from in effect causing the error.

Thanks for that article...it actually had a fix for that initial prompt...
"Note Setting DisplayAlerts=none through VBA suppresses the prompt when the
file is opened by using VBA, but uses the NO option to open the document and
no data is attached to the mail merge main document."
....which works out for my procedure.

Hi

The prompt is by design. See this article from Microsoft
http://support.microsoft.com/default.aspx?scid=KB;EN-US;825765.

I would suggest changing from using a Mail Merge to using bookmarks in a
template and populate the bookmarks from your table. Its's more
straightforward.

Re access to your database - does it have permissions to access the db?

M

SCENARIO:
I have the following module in my database which merges data from a
temporary table (temp_PIC) in the same database into a COPY of a
pre-designed
Word merge document template located on our server...

Function MergeIt()

Dim objWord As Word.Document
Set objWord =
GetObject("\\holnt01\public\Projects-Hollywood\CustomerCareAdministrative\Attendance\Write-ups\PIC
Output\PicMerge1.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _

Name:="\\holnt01\public\Projects-Hollywood\S3\Databases\Working\Attendance\Attendance.mdb",
_
LinkToSource:=True, _
Connection:="TABLE temp_PIC", _
SQLStatement:="SELECT * FROM [temp_PIC]"
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close

End Function

...The original Word merge document template is already linked to the temp
table therefore in the process when it opens the original prior to making
the
copy it prompts .

Opening this document will run the following SQL command:

SELECT * FROM[temp_PIC]

Data from your database will be placed in the document. Do you want to
continue?

YES/NO


...When I click YES it gives me a message that the database is in use and
locked therefore it cannot establish connection.

QUESTIONS:
How can I avoid the initial prompt indicated above on the open of the
original template and what can I do about the database being unavailable
since its obviously going to be open when this process is running?


Thank you in advance for your assistance

 

[PMK]

See Albert Kallal's excellent word merge facility at

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

HTH

PMK

 

[AccessARS]

thank you for the link but I already have gone through that site and his
approach is not compatible in with my design without completely redesigning
the entire application.

I know in the past I have established connection under the same circumstance
with no conflicts. I think it had to do with DAO or ADO connection but not
sure

See Albert Kallal's excellent word merge facility at

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

HTH

PMK

SCENARIO:
I have the following module in my database which merges data from a
temporary table (temp_PIC) in the same database into a COPY of a pre-designed
Word merge document template located on our server...

Function MergeIt()

Dim objWord As Word.Document
Set objWord =
GetObject("\\holnt01\public\Projects-Hollywood\CustomerCareAdministrative\Attendance\Write-ups\PIC Output\PicMerge1.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _

Name:="\\holnt01\public\Projects-Hollywood\S3\Databases\Working\Attendance\Attendance.mdb", _
LinkToSource:=True, _
Connection:="TABLE temp_PIC", _
SQLStatement:="SELECT * FROM [temp_PIC]"
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close

End Function

...The original Word merge document template is already linked to the temp
table therefore in the process when it opens the original prior to making the
copy it prompts …

Opening this document will run the following SQL command:

SELECT * FROM[temp_PIC]

Data from your database will be placed in the document. Do you want to
continue?

YES/NO


...When I click YES it gives me a message that the database is in use and
locked therefore it cannot establish connection.

QUESTIONS:
How can I avoid the initial prompt indicated above on the open of the
original template and what can I do about the database being unavailable
since its obviously going to be open when this process is running?


Thank you in advance for your assistance

 

[AccessARS]

FYI...
This is the message I get when the Word document opens for merge:

ODBC Microsoft Access Driver Login Failed
The database has been placed in a state by user 'Admin' on machine 'SCHOLD-
WKF-8014' that prevents it from being opened or locked.

....'SCHOLD-WKF-8014' is my computer.

thank you for the link but I already have gone through that site and his
approach is not compatible in with my design without completely redesigning
the entire application.

I know in the past I have established connection under the same circumstance
with no conflicts. I think it had to do with DAO or ADO connection but not
sure

See Albert Kallal's excellent word merge facility at

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

HTH

PMK

SCENARIO:
I have the following module in my database which merges data from a
temporary table (temp_PIC) in the same database into a COPY of a pre-designed
Word merge document template located on our server...

Function MergeIt()

Dim objWord As Word.Document
Set objWord =
GetObject("\\holnt01\public\Projects-Hollywood\CustomerCareAdministrative\Attendance\Write-ups\PIC Output\PicMerge1.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _

Name:="\\holnt01\public\Projects-Hollywood\S3\Databases\Working\Attendance\Attendance.mdb", _
LinkToSource:=True, _
Connection:="TABLE temp_PIC", _
SQLStatement:="SELECT * FROM [temp_PIC]"
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close

End Function

...The original Word merge document template is already linked to the temp
table therefore in the process when it opens the original prior to making the
copy it prompts …

Opening this document will run the following SQL command:

SELECT * FROM[temp_PIC]

Data from your database will be placed in the document. Do you want to
continue?

YES/NO


...When I click YES it gives me a message that the database is in use and
locked therefore it cannot establish connection.

QUESTIONS:
How can I avoid the initial prompt indicated above on the open of the
original template and what can I do about the database being unavailable
since its obviously going to be open when this process is running?


Thank you in advance for your assistance