Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Word97 connecting to Access97 (W97/ A97)

    Everyone has been so helpful with my "dilemmas", I'm back for more:

    I have created a W97 Template to write FRSs for our IT dept. Now, I need to link it to an ACCESS application (Requirement Traceability Matrix Tool) to extract the list of Requirements and insert them into the document. Currently, the user uses a form (on the Access tool) with combo boxes to generate the query. The query outputs a file that my W97 Template inserts and formats. For Version II of the Template, I want to bring all of the functionality to my template so the process is more automated. In doing so, I have created a form in my W97 template (similar to the ACCESS form) with combo boxes for the user to select criteria - to run the query. Here's were I'm having difficulty (Besides the fact that I'm a beginning VBAer).... The ACCESS form had property fields for Row Source, On Change, and On Got Focus, that tell the form where to go and what to do, when... How do I add that information to my form fields.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97 connecting to Access97 (W97/ A97)

    Access 97 wasn't an automation server the way Word 97 was or like later versions of Access are, so you will have some problems controlling it from Word. Access in all versions also uses a different variety of forms and controls than the rest of Office, so you can't do the same things with the Office controls and it would take a lot of programming to approximate them. Some things you can't do, period, with MSForms and controls.

    You can query the Access database from Word, but in 97, you're going to wind up with Access being launched and visible when you do so. Perhaps if you explain more about what you're trying to get from/put into Access from Word, someone who programs both can give you more direction.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97 connecting to Access97 (W97/ A97)

    List of queries? What are you trying to do with the values you're capturing on your userform? I'm more confused now than before. Are you trying to create a query using MSQuery or are you trying to pass parameters to an Access parameter query or what? If you're talking about the Database Retriever add-in from that site, why don't you just use that? What do you need a query list for?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    <P ID="edit" class=small>(Edited by charlotte on 26-Sep-02 19:50. Activate link)</P>That's exactly what I'm wanting to do.... Query the Access database from word. I want to press a toolbar button, activate a UserForm, select from 4 combo boxes and send the "answers" from the combo boxes to an ACCESS.mbd query. Once the query has the info , it will run against the tables and send back the responses and drop them into the W97 document. It doesn't bother me if the user sees ACCESS open, for I am disabling the keyboard until the process is completed.

    I found a tool on http://www.cdev.co.uk/ that is doing something simular to what I want, but it is retrieving the list of tables.... I'm looking at the code (what I can understand of it) and trying to modify it so that it will display the list of Queries instead of the list of Tables... But I may be jogging down the wrong path... - Jody

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    OK, trying not to confuse anyone more....
    Currently, (in Access) the User selects from 4 combo boxes. The 'answers' are passed on to an ACCESS query#1. The results of that query then pass through 30 other queries before there is an output (which I want to drop into my document). So that the user doesn't have to manually enter ACCESS, complete the UserForm to pass the paramaters, and output the results to a .txt file... which my template will retrieve and drop in..... I want the user to Select (from a Word UserForm) the 4 paramaters and pass those parameters to the ACCESS query #1. Take the results of the series of queries and insert that information into my word doc. I have included a diagram, which I hope explains the situation better. - Jody
    Attached Files Attached Files

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

    Re: Word97 connecting to Access97 (W97/ A97)

    Here is a suggestion, even though you say you don't want your users to go into Access, but....

    Why not just generate the Word doc from Access when they make the selections from the Access form.

    If you look at this page and read the two articles there about automating Word from within Access.

    If you don't like that idea, then you will need to look at using running the queries and returning the recordset to Word using DAO or ADO. The articles will show you a bit of using DAO to access the data. But bear in mind that the articles are looking from within Access.
    --
    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

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    I'll check into the articals.... I can't generate from Access, because the ACCESS output is only one section of a large Word document. The user needs to be able to update the section of the document inserted from ACCESS and not loose the other contents of the document.

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

    Re: Word97 connecting to Access97 (W97/ A97)

    Depending on HOW you insert the data from Access into Word (all this is assuming you are still working in Access), you will not loose any of the original information in Word.

    The Part II article does just that. It uses a "boilerplate" document, and just inserts the data into the spots that they need to go., without overwriting any of the boilerplate text that is there.
    --
    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

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    I'm a bit confused as to how to do it, but by looking at the sample Word doc, it looks as though It might do what I want.... I just can't open the Access file (I'm on 97). Will check it this weekend at home. - Jody

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

    Re: Word97 connecting to Access97 (W97/ A97)

    I converted it to A97.

    It's too large to post to the Louge, but if you PM your e-mail address, I will e-mail it to you. It's about 400K
    --
    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

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97 connecting to Access97 (W97/ A97)

    How were you planning to pass the parameters to Access and run the queries? In Access there is either code behind the form to generate the query or pass it parameters or else the query references the Access form for its parameters. In either case, just filling in a form in Word won't acomplish the same thing, so what did you have in mind? As I pointed out before, Access 97 was not an automation server, so you can't just run this stuff from within Word.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    Well Charlotte, I certainly don't know the particulars (that's why I'm here) on passing the paramaters to Access.... I haven't yet thought that I couldn't do it.... I tend to hop in, not thinking that it's not possible - I just start swimming.... musical1@essex1.com

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97 connecting to Access97 (W97/ A97)

    Just jumping in the deep end isn't the best way to work with Access. Have you looked at what the Access form and queries are actually doing? You're going to have to start there, whether you do this from Access or try to do it from Word.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    My email is down, so try this email address, Thanks - Jody Jody_Dyche@merck.com <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  15. #15
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Word97 connecting to Access97 (W97/ A97)

    I do have "connection info" ..... in my first posting, I mentioned that >> The ACCESS form had property fields for Row Source, On Change, and On Got Focus, that tell the form where to go and what to do, when... How do I add that information to my form fields>> I think this is the info I need.... I just don't know how to set those properties to read/ execute from an W97 form.

Page 1 of 2 12 LastLast

Posting Permissions

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