Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GET data from database into word (2000 / 2003)

    Hi

    Is it possible to adapt the code below to GET data from the access table instead of adding it

    Set dbs = DBEngine.OpenDatabase("H:TEST.mdb")
    strSQL = " tblLetterLog ([TimeStamp], CRN, SPEC) VALUES (" & _
    " Now(), " & Chr(34) & strCRN & Chr(34) & ", " & Chr(34) & strSpec & Chr(34) & ")"
    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError

    dbs.Close
    Set dbs = Nothing

    I will be using a different table but basically want to fetch a clients address by matching the CRN number by first getting the user to enter the CRN into word via an inputbox. Then put the results on the document.

    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: GET data from database into word (2000 / 2003)

    Look up InsertDatabase in the Word VBA help. Here is an example:
    <code>
    Dim lngID As Long
    Dim strDb As String
    Dim strSQL As String
    lngID = InputBox("Enter Employee ID")
    strDb = "CatabasesTest.mdb"
    strSQL = "SELECT strLastName, strFirstName FROM tblEmployees " & _
    "WHERE pkeyEmployeeID = " & lngID
    Selection.Range.InsertDatabase DataSource:=strDb, _
    SQLStatement:=strSQL, IncludeFields:=True</code>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GET data from database into word (2000 / 2003)

    Hans, once again Thanks.

    I read the help file, it give me some idea but not with the problem I have.

    I used your code and created a test databse in the same place as your code states. Using the same field names etc.

    When I try to run the code I get the following message.

    "This opperation cannot be completed because of Dialog or dbEngine failures. Please try again later."

    After sticking an error handler in this is the result

    "5922 Word was unable to open the data source"

    I'm beat!

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: GET data from database into word (2000 / 2003)

    You should, of course, change the SQL string to reflect the table and field names that YOU want to use. The SQL string in my example was just that - an example.
    Also, if you have opened the database in Access and modified the design of a database object such as a table or query, the database will be locked, so that Word can't get at it.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GET data from database into word (2000 / 2003)

    Thanks for your reply

    I no I need to change the SQL for me, but I created a test database first just to test it out, didn't want to mess with 850'000 records just incase.

    I cant understand why it wont open the database, if I comment out the SQL line it seems to try and do something but I end up with a system crash.

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: GET data from database into word (2000 / 2003)

    Which version of Word are you using to try this out?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GET data from database into word (2000 / 2003)

    2003 Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: GET data from database into word (2000 / 2003)

    It should work OK there, so without seeing the database and document I can't tell you what goes wrong.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GET data from database into word (2000 / 2003)

    Thanks Hans,

    I got it now, I forgot the Chr(34) again!
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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