Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Word Merge Function Issue...

Discussion in 'Information Technology' started by AccessARS, Nov 17, 2005.

  1. AccessARS

    AccessARS
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Origin Of Word Waheguru Sikh Sikhi Sikhism Aug 3, 2016
    The Sword - Bhagoutee - ਭਗੌਤੀ Sikh Sikhi Sikhism Mar 25, 2016
    Sikhi A Powerful Four-letter Word: SEVA! Sikh Sikhi Sikhism Jan 30, 2016
    Consensus Gurbani Word Meanings Intellectual Translations by SPNers Jan 16, 2016
    Is SARBAT KHALSA some magic word that will cure Sikh woes? Sikh Sikhi Sikhism Jan 9, 2016

  3. Someone

    Someone
    Expand Collapse
    Guest

    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" <AccessARS@discussions.microsoft.com> wrote in message
    news:FD7704B6-FC1E-44CC-AB49-38D6AC9B4A44@microsoft.com...
    > 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
    >
    >
     
  4. AccessARS

    AccessARS
    Expand Collapse
    Guest

    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.


    "Someone" wrote:

    > 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" <AccessARS@discussions.microsoft.com> wrote in message
    > news:FD7704B6-FC1E-44CC-AB49-38D6AC9B4A44@microsoft.com...
    > > 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
    > >
    > >

    >
    >
    >
     
  5. PMK

    PMK
    Expand Collapse
    Guest

    See Albert Kallal's excellent word merge facility at

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

    HTH

    PMK

    "AccessARS" wrote:

    > 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
    >
    >
     
  6. AccessARS

    AccessARS
    Expand Collapse
    Guest

    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

    "PMK" wrote:

    > See Albert Kallal's excellent word merge facility at
    >
    > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
    >
    > HTH
    >
    > PMK
    >
    > "AccessARS" wrote:
    >
    > > 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
    > >
    > >
     
  7. AccessARS

    AccessARS
    Expand Collapse
    Guest

    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.


    "AccessARS" wrote:

    > 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
    >
    > "PMK" wrote:
    >
    > > See Albert Kallal's excellent word merge facility at
    > >
    > > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
    > >
    > > HTH
    > >
    > > PMK
    > >
    > > "AccessARS" wrote:
    > >
    > > > 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
    > > >
    > > >
     

Share This Page