Learning about an unfamiliar Access setup?

[aboyd]

I've just been given the task of "fixing" a too-big Access database. It's actually about 250 tables in 29 .mdb files scattered over a filesystem. But I'm very green at Access (although I'm pretty OK at SQL in general). So here's my question: how the heck would you all explore/learn about this Access setup? Is there an automated way to cruise through all the .mdb files, get lists of all the tables, and even lists of field names? What about getting a list of the type & relationship of each field?

I know I can get some of this by opening a file, right-clicking on a table, and selecting "Design View." But doing this 250 times sounds painful, and the properties window cannot be printed, anyway.

Any tips about this?

-Tony

 

[pdx_man]

VBA, my friend, VBA.

Here is some code that I used to update the APP property for all of the linked tables in Access DBs that I have entered the path in a local table called Tbl_Files. This should give you the logic you need to get the table definitions for your remote DBs:

Public Sub Update_Connection_Remote()
Dim dbs, dbsb As dao.DataBase
Dim rst As dao.Recordset
Dim HoldApp, NewApp As String
Dim TblDef As dao.TableDef
Dim FirstPos, LastPos, IndCount As Integer
Dim TblInd As dao.Index
Dim HoldIndFields, HoldIndName As String
Dim SqlStr As String

Set dbsb = CurrentDb
Set rst = dbsb.OpenRecordset("Select * from Tbl_Files")

Do Until rst.EOF

    Set dbs = DBEngine.Workspaces(0).OpenDatabase(rst!FileToUpdate)
    
    ' Retrieve the DB name
    NewApp = Right(dbs.name, Len(dbs.name) - InStrRev(dbs.name, "\"))
    On Error GoTo HandleTbl

    ' Alter the APP parameter in the connection string for linked tables
    For Each TblDef In dbs.TableDefs
    Debug.Print TblDef.name
        If TblDef.Connect <> "" Then
            IndCount = TblDef.Indexes.Count
            For Each TblInd In TblDef.Indexes
                HoldIndFields = TblInd.Fields
                HoldIndName = TblInd.name
            Next
            FirstPos = InStr(TblDef.Connect, "APP")
            LastPos = InStr(FirstPos, TblDef.Connect, ";")
            HoldApp = Mid(TblDef.Connect, FirstPos + 4, LastPos - (FirstPos + 4))
            TblDef.Connect = Replace(TblDef.Connect, HoldApp, NewApp)
            TblDef.RefreshLink
            If TblDef.Indexes.Count <> IndCount Then
                HoldIndFields = Replace(HoldIndFields, "+", "")
                HoldIndFields = Replace(HoldIndFields, ";", ",")
                SqlStr = "CREATE UNIQUE INDEX " & HoldIndName & " ON " & TblDef.name & " (" & HoldIndFields & ")"
                dbs.Execute SqlStr
            End If
        End If
GetNextTbl:
    Next
    
rst.Close
dbs.Close

Exit Sub

HandleTbl:
    ' Append the APP parameter in the connection string for linked tables
    If Err.Number = 5 Or Err.Number = 3059 Then      ' If other than 5, just go to the next table
        TblDef.Connect = TblDef.Connect & ";APP=" & NewApp
    End If
    Resume GetNextTbl

End Sub

 

[aboyd]

Hey pdx_man, I gave you some rep. Thanks so much for that code, it helped me to visualize exactly how I might crawl though this programmatically. Much appreciated.

-Tony

 

[Adeptus]

You can also use Tools - Analyze - Documenter
This will produce a report containing every detail you could want about Access database objects.
Be warned, it can take a while, and can produce a very LONG report if you ask for all the details...

 

[The_Doc_Man]

Another issue is whether the tables are linked or opened through VBA. If the former, your Documenter and the VBA code will work. Otherwise, you have to try to find the {database}.Open code in any VBA in the master DB.