Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    > > >
    > > >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page