Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Tables (Access '97)

    I am looking to migrate a database from 97 to 2k.

    The database has lots of linked tables with long file names, linking to network areas with lots of sub-folders - i.e. I can't see where they are coming from.

    I've used the documenter to get the links and this looks OK when I export it to word. What I would like though is a nice add-in or bit of code that will put this information into excel like so:

    Table Links To
    Table99 S:BlahBlahblahSOURCEDAT.mdb

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (Access '97)

    In your database create a table with Name = tblConnections, with two text fields, txtName and txtConnextion. Make txtConnection large enough to contain the connection string.
    Use the following code to create your XLS workbook:<pre>Sub ConnectionsToExcel()
    Dim db As dao.Database
    Dim tdf As dao.TableDef
    Dim rst As dao.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "Delete * from tblConnections"
    db.Execute strSQL
    Set rst = db.OpenRecordset("tblConnections", dbOpenDynaset)
    For Each tdf In db.TableDefs
    If tdf.Connect <> "" Then
    With rst
    .AddNew
    !txtname = tdf.Name
    !txtconnection = tdf.Connect
    .Update
    End With
    End If
    Next
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    "tblConnections", "Connections.XLS", False
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    End Sub
    </pre>

    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (Access '97)

    if you run it from Access 97 chage the line Docmd to :
    <pre> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
    "tblConnections", "Connections.XLS", False</pre>

    Sorry, I was writing it in Access 2000
    Francois

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (Access '97)

    Francois,

    Perfect - actually better

    Not only did it give me the links, it also provided me with the password to the source database!!!

    Obviously i knew the password, but if i ever forget, then i'll be able to find out

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

    Re: Linked Tables (Access '97)

    <hr> i knew the password, but if i ever forget, then i'll be able to find out <hr>
    Just remember than anyone else could also figure it out from your code.
    Charlotte

Posting Permissions

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