Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Inserting data/field from Access (2003/sp2)

    Dear Loungers,

    I have a document in which I want to insert some data held in an Access database. If I use insert database and follow the steps everything works fine provided I am inserting the data rather than the link. However if I try to insert a link Word complains it can't find the data source. This is odd since it is the same database, query etc. Not sure how to investigate this, any ideas?

    liz

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

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    I thought you had given me the solution but although I followed the instructions it doesn't solve the problem (I did fill in the feedback form to say so).

    As instructed I inserted a space before WHERE, there was already a space before, making two spaces before. I still get a "Error! Cannot open data source." message. What else could it be? The field statement is this (I have removed the middle since this message size is limited):

    DATABASE d "Z:26 Next_GenerationBusiness Model (NGRM)BusinessModel.mdb" c "Provider=Microsoft.Jet.OLEDB.4.0;Password=""; User ID=Admin;Data Source=Z:26 Next_GenerationBusiness Model (NGRM)BusinessModel.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" s "SELECT `PROC_Name` FROM `Process` WHERE ((`PROC_id` = '101'))"

    Any other ideas - it is a network-based database which I don't think should make a difference.

    thank you.................. liz

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

    Re: Inserting data/field from Access (2003/sp2)

    It shouldn't matter whether the database is stored locally or on a networl drive.

    You might try the following:

    - Select Tools | Options...
    - Activate the General tab.
    - Tick the check box "Confirm conversion at Open".
    - Click OK.

    When you try Insert Database again and select a database, you'll be prompted to select a method to connect to it. Instead of the default OLE DB Databases method, try DDE or ODBC. Does that make a difference?

    Note: if you want to use a query stored in the database:
    - If you choose DDE, activate the Queries tab in the next dialog.
    - If you choose ODBC, click Options... in the next dialog and tick the check box "Views".

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    Hans,

    Neither work, and stranger still is that if I copy the SQL, for example generated by the DDE method, into an Access query it works beautifully and therefore is not badly formed.

    liz

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

    Re: Inserting data/field from Access (2003/sp2)

    The SQL is OK. What is Process - a table or a query?

  7. #7
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    Process is a table

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

    Re: Inserting data/field from Access (2003/sp2)

    I'm afraid I don't have an idea then. If you wish, you can post a stripped down copy of your database, so that someone with Office 2003 can experiment with it (I'm using Office XP myself). See <post#=401925>post 401925</post#> for instructions.

  9. #9
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    Hans,

    have now made DDE link work almost always.

    What seems to be a problem is I can't stop it formatting the output. It seems a bit inconsistent but with two adjacent fields I have lost the table formatting but when I update the fields (F9) the update introduces an end paragraph which i then have to edit manually. With a small document this would be fine but this will run to several hundred pages so is a real pain. The 3rd field is a list that I want formatted using my bullet styles. Having got them the way I want if I update the field I end up with a table no matter what I do - there is always more than one, it doesn't remeber the formatting; so again I have to fiddle.

    Any idea how to get the formatting to stick with multiple record and how to aviod the extra paragraph?

    Thank you.................. liz

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

    Re: Inserting data/field from Access (2003/sp2)

    I don't think I can help without being able to see what you're talking about. See my previous reply.

  11. #11
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    Hans,

    DDE seems to work mostly. When it doesn't is that it will not pick up concatenated Access data fields in a query, this falls over with an error. I have attcahed a stripped database and a related document. What I can't do is:

    retain formatting on update
    on a single embedded line stop update introducing a previous empty paragraph
    not have a multiple record dataset in a table (i can live with tis if I can preserve formatting)
    use concatenated fields (see "ProcessLabel" in the QRY process List query)

    Once I have solved these I also wanted to be able to (simply) refer to a value either entered by the user or held in the document (as a bookmark?) and use that to drive the database field - so I couid substitute the hardwired PROC_Id with a field held in the document e.g. as a book mark or supplied by the user. Is this possible without VB?


    Many Thanks......... liz
    Attached Files Attached Files

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

    Re: Inserting data/field from Access (2003/sp2)

    The field in the query is named Process Label (with a space), but your SQL uses Process_Label. If you change the underscore to a space, Word can open the data source.

    > retain formatting on update
    > on a single embedded line stop update introducing a previous empty paragraph
    > not have a multiple record dataset in a table (i can live with tis if I can preserve formatting)

    I don't think you can solve these problems using DATABASE fields. You could populate the document using VBA from Access, but that requires knowledge of both Access VBA and Word VBA.

    Yes, you can replace the parameter 101 with a reference to a bookmark. Suppose you create a bookmark named ID.
    Press Alt+F9 to display field codes.
    Delete 101 in one of the fields, and in its place press Ctrl+F9 then type ID.
    You should see { ID }
    Repeat for the other fields.
    When done, press Alt+F9 to hide the field codes, then select everything and press F9 to update (you may have to do this twice)

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

    Re: Inserting data/field from Access (2003/sp2)

    To avoid problems like the one you had, I *never* use spaces or unusual characters such as # in the names of database objects, fields and controls. You never know when they come back to bite you.

  14. #14
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Inserting data/field from Access (2003/sp2)

    Hans,

    Another thank you.

    I hadn't spotted the underscore - this was added by DDE engine, obviously not liking embedded spaces
    I have discovered that the additional paragraph added above when updating fields is only added when the line is the first in the document.

    The annoying thing about formatting is that is is retained if only one record is returned, but if graeter than one record is returned not only does formatting get lost (reverting to normal) but it intriduces a table (Rats!!!).

    I shall play with Vb - not my strength so I avoid it but can make it work if I have to.

    many thanks for the help............... liz

Posting Permissions

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