Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access automation with Word (Access97->XP)

    I want to use Word to get at some data stored in an Access 97 Jet database. I bet there's a lot of overhead opening Access to get the data and I'm wondering if I use an ADO connection string (instead of DAO), will this mean I'm not really opening Access and therefore mean lower overhead/faster access? I'm running Office 97, but am willing to upgrade if ADO will give me better performance in this scenario.
    Many thanks,
    Gwenda

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access automation with Word (Access97->XP)

    Hi Gwenda,
    DDE is slow, but you could try using an ODBC data source - they are a fair bit quicker. ADO and Access97 don't provide much capability, and I'm not sure how you would even force the use of ADO using Word Mail Merges. On the other hand, Office XP and its OLE DB data source are quicker than either. Unfortunately there are some issues with security and the Word mail merge. Just to clarify, I presume you are driving the automation from the Access database, but running the merge in Word. If that's not what you are doing, explain and I'll do my best to help.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access automation with Word (Access97->XP)

    Dearest lightning-fast Wendell,
    I'm not doing a mail merge. I have VBA code in a Word template that needs to open a parameters table in Access to get the name of the writer, business name, and a bunch of other stuff that gets plugged into the letter. The data is stored in Access as part of a larger Access application. I'll just open the recordset once, grab the data I need and load it into variables, close the recordset and go on with my processing in Word.

    From your message, I judge that I should perhaps try an ODBC data source (I know that DDE stands for Dynamic Data Exchange, but I don't know what it means). Or do you think I should upgrade to Office XP?
    Thanks again,
    Gwenda

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access automation with Word (Access97->XP)

    ARGH! I'm so fast I assumed you were doing Mail Merges. Yes, where you are doing an automated generation of a document in Word, you are not doing DDE stuff, so it isn't terribly sluggish when compared to Mail Merges. Actually, I don't think you will get much of a speed boost using ADO as compared to DAO. Are you doing a one-off creating of a single document, or are you doing a multi-record document? We found that in doing a 600 page Word document based on about 4000 records of data, the automation solution created something that looked identical to a Word catalog style Mail Merge, and took less than 25% of the time. In that case we were using DAO and ODBC linked SQL Server tables. From your description, I gather you are going the other way and loading Access data into Word using VBA in Word - is that correct?
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access automation with Word (Access97->XP)

    Gwenda,

    If you use DAO (or ADO for that matter), you aren't actually opening Access to get at your data. You are just using the Jet Engine.

    All you have to do is set a reference, in Word, to Microsoft DAO 3.51 Object Library and Use code like this to access the data:

    Dim db As Database
    Dim strSQL As String
    Dim rst As Recordset

    <font color=448800>'Open database</font color=448800>
    Set db = OpenDatabase("C:fullpathtoyourdatabasefile.mdb")
    strSQL = "SELECT * FROM tblContact"
    <font color=448800>'Open Recordset</font color=448800>
    Set rst = db.OpenRecordset(strSQL)
    <font color=448800>'Loop through the recordset</font color=448800>
    Do While Not (rst.EOF)
    With Selection
    .InsertAfter rst.Fields(1)
    .InsertParagraphAfter
    End With
    rst.MoveNext
    Loop

    <font color=448800>'Close and release pointers</font color=448800>
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access automation with Word (Access97->XP)

    Dear Wendell and Bryan,
    Thank you so much for your help! Between your responses I have all the information I need. I've learned so much at Woody's and am ever grateful to you.
    Gwenda <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access automation with Word (Access97->XP)

    Thinking about your performance question while I was out, the real issue doing a one-off document is that you have to start the Jet database engine each time. You might get a significant speed boost by migrating to 2000 or 2002 and using the Desktop Engine, which is really SQL Server in disguise. It runs as a background task or service, and would thus not require starting up. It does also have some inherent speed advantage in returning data, but that would likely be in the noise level, for what it's worth. Have a good day.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access automation with Word (Access97->XP)

    Thanks Wendell. That's exactly my concern. I'm going to try it first with DAO as Bryan suggested. Then if performance is poor, I'll try it with the Desktop Engine (MSDE).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •