Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word unable to open Access datasource (2003)

    <P ID="edit" class=small>(Edited by jscher2000 on 29-Jul-05 12:36. Edited subject line to be more descriptive.)</P>After researching this issue on this forum and other sources, I have still not found an adequate solution to the problem.

    I have numeruous Access queries(some very complicated including custom functions)- I would like to link these queries to an existing Word Doc. Using the Database feature of Word to link to the underlying queries. I have no problem with the basic queries and tables linking to Word. It is the more complex queries that give me the error: "Word unable to open datasource"

    My current workaround is to change the complex queries into make table queries and link the updated table to the word doc. Which means I need to run Access first and then update the tables in word. If there is a solution to using complex queries in word Please point me in the right direction.

    Karen

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word unable to open Access datasource (2003)

    Can you give some addition information on how you are linking from Word to Access? For example, is there an embedded object, or is it in the context of a mail merge, or are you using VBA code to populate a UserForm or insert text into a document?

    If there are particular queries that break the Word-Access linkage, perhaps you could post the most problematic parts.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word unable to open Access datasource (2003)

    The default method to connect to an Access database doesn't recognize custom functions. Try the following:
    - In Word, select Tools | Options...
    - Activate the General tab.
    - Tick the check box 'Confirm conversion at Open'.
    - Click OK.
    - Go to step 3 of the Mail Merge task pane to specify the data source again.
    - Click browse and locate the database.
    - You'll be prompted for a method to connect to the database.
    - Select the DDE option.
    - Note: this will cause problems if you have given the database an application title (in Tools | Startup) that does not begin with Microsoft Access.

  4. #4
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word unable to open Access datasource (2003)

    I have tried both mail merge and database datasources and because of my complex queries I am still running into the issue.

    I have tried with using the Database tool bar from within Word as embedded objects. I would be happy with either method if it worked. When I try mail merge I am unable to merge that data correctly into a Word table to display as a table and not a single line of data for each page.

    I would like to use the merge/database feature, but I may have to resort to make tables from within Access the linking those tables to the Word document - unfortunately this creates an extra step that my user will not like. From my research the problem lies with my custom functions in my complex queres. Any Ideas?

    karen

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word unable to open Access datasource (2003)

    Hans,

    I have already tried this method, except for the title - So I changed the title back to Microsoft Access. Stepped thru the process again and still no luck. I even tried opening the queries into MS Query and trying to link the data is way - unfortunately it doesn't recognize my functions that are embedded in my complex queries.

    Any suggestions.

    Karen

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word unable to open Access datasource (2003)

    MS Query won't work - it uses ODBC, and ODBC doesn't recognize custom functions.

    The DDE method of connecting to the database DOES recognize custom functions, but I don't know what kind of calculations your functions perform. I don't know if it is possible for you to provide information about that, or to attach a stripped down copy of the database.

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

    Re: Word unable to open Access datasource (2003)

    In addition to the comments from Hans and Jefferson, it would be useful to know whether Access User Security has been applied to the database in question, and whether or not your queries use parameters. Either can have nasty side effects.
    Wendell

  8. #8
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word unable to open Access datasource (2003)

    Wendell,

    No to both.

    Karen

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word unable to open Access datasource (2003)

    Your query is so complicated that I can well understand that Word can't handle it. Maybe it would work if you broke it down into a series of separate queries, but I cannot guarantee that. Using a make-table query may well be your best bet.

  10. #10
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word unable to open Access datasource (2003)

    Thanks for your time, Looks like I stump Microsoft again. I will stick with the make table qrys for now. This sucks. As much time as this has taken I could have manually inputted the data over and over again.

    Thanks again.

    Karen

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word unable to open Access datasource (2003)

    Okay, this is way beyond me. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Since it does work to run intermediate queries (such as Make Table queries) first, you can try doing that from inside Word using automation, and then either add some code to run your merge or do the merge afterwards.

    I'm attaching a sample of code that demonstrates the use of ADO inside a Word procedure to run several stored Access queries without actually running Access. The queries include DELETE, APPEND, and UPDATE queries, wihch prepare a table for reporting. Hope this helps.

    Added: Just as a footnote, the queries are stored in "my" MDB database, which uses tables linked to the target application's MDB database; I am not allowed to make any changes to the target application's MDB file. There is a separate bit of code (using ADOX) that can dynamically link to a different database with the identical format, in case one wants to report from someone else's time database, but unless you are getting new MDBs every time you need to run this, that piece probably is not relevant to you.
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word unable to open Access datasource (2003)

    <P ID="edit" class=small>(Edited by jscher2000 on 29-Jul-05 14:55. Undeleted the post text, without restoring the attachment, so one can still follow the thread.)</P>Attachment deleted by kfschaefer

    Here is my stripped down version, I am using the DDE selection and even with this version of the mdb I am still unable to link to the word doc.

Posting Permissions

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