Linked Tables (Access ’97)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Reply | Quote

if you run it from Access 97 chage the line Docmd to :

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
        "tblConnections", "Connections.XLS", False

Sorry, I was writing it in Access 2000

Reply | Quote

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

Reply | Quote

[indent]

---

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

---

[/indent]Just remember than anyone else could also figure it out from your code.

Reply | Quote