Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link from Access (Word 2003 SP1 )

    I'm trying to figure out how to link a field in ms Access into my word documents.

    in the word document i have text Document Owner: xxxxx xxxxxx

    Lets say the name of the document is ZA-001/SD/001.S1 its filename)

    In a microsoft access back end. (security is enabled), i have fields.

    Document Reference............................Name
    ZA-001/SD/001.S1................................Joe Bloggs.

    I want the xxxxx xxxxx to be replaced by the text Joe Bloggs in the document, and all other documents.




    From what i can tell i have to create an .odc connection to the database.I have managed to create one up to the point where it shows me all of the tables and queries in the .mdb. but i keep getting strange error messages. (I am guessing bits when it offers me Jet connection, or ODBC connection etc)

    If i try to connect directly to the back end, i get a security message. (presumably because i'm not connected to the security file) and i get no further..

    If i connect to the front end of the database, (which has all the tables etc linked), i can then get to see the tables and queries.
    but it doesn't seem to work correctly. (i get error messages 'field missing 1, etc,)

    Is anyone aware of a n00b guide to how to do this ? i have looked on woodys search (every result under the sun for the search term 'access' /boggle), and also looked on the microsoft web site.. article 193716, explains the basic concept, but does not really go into such detail.

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

    Re: Link from Access (Word 2003 SP1 )

    Forward slashes are not allowed in file names. Is ZA-001/SD/001.S1 a path+filename? (But then I would have expected backslashes)

  3. #3
    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: Link from Access (Word 2003 SP1 )

    Are you doing a merge of a particular set of records, are you using a macro to search your MDB and populate the xxxx's, or did you want Word to behave like an Access form and transparently grab the data in real time? (That could be a trick question as the latter, I believe, is not part of Word's normal repetoire.)

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Link from Access (Word 2003 SP1 )

    The best resource I know of is to look at samples on Helen Feddema's website - http://www.helenfeddema.com/codesamples.htm
    The Access >>> Word section should get you going.

    Hang on - Now I re-read your post and see that this is more likely to be a security issue. I would be trying to connect to the backend rather than the front end and the connection will need to include the security file too. That makes it trickier...

    Does this give you a workable lead?
    thread on ms public access security

    A simpler alternative would be to drive the Word document production from Access. If you push from the opened database then you don't have the security issues that pulling from Word would encounter.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link from Access (Word 2003 SP1 )

    The former, i wish to the word document which knows its own filename, to look up the document's owner (which is in the mdb)

    I will investigate the posted links, many thanks, and report later.

    Hans, yea sorry, the actual filename uses underscores.

    Can you use VBA in a word document to look it up ? hmmm

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

    Re: Link from Access (Word 2003 SP1 )

    You could put code in the Document_Open event procedure in the ThisDocument module:

    Private Sub Document_Open()
    ' Modify as needed
    Const cstrDatabase = "ServerShareFolderDatabase.mdb"
    Dim strFilename As String
    Dim intPos As Integer
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String

    On Error GoTo ErrHandler

    strFilename = ActiveDocument.Name
    If LCase(Right(strFilename, 4)) = ".doc" Then
    strFilename = Left(strFilename, Len(strFilename) - 4)
    End If
    With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = cstrDatabase
    .Open
    End With

    ' Substitute correct names
    strSQL = "SELECT <!t>[Name]<!/t> FROM <!t>[NameOfTable]<!/t> " & _
    "WHERE <!t>[Document Reference]<!/t> = " & Chr(34) & strFilename & Chr(34)
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, adCmdText
    If Not rst.EOF Then
    With ActiveDocument.Content.Find
    .Text = "xxxxx xxxxx"
    .Replacement.Text = rst!<!t>[Name]<!/t>
    .ClearFormatting
    .Replacement.ClearFormatting
    .Execute Replace:=wdReplaceAll
    End With
    End If

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You must set a reference in Tools | References... to the Microsoft ActiveX Data Objects 2.n Library (try 2.8 or else 2.1)

Posting Permissions

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