Looking for Linked Table information utility 2

[Toledo]

I am looking for a utility or method to scan a listing of Access 2000 databases and produce a list of the linked tables in each and the linking path information

 

[JeremyNYC]

This will show you all the databases you're linked to:
SELECT MSysObjects.Database
FROM MSysObjects
GROUP BY MSysObjects.Database
HAVING (((MSysObjects.Database) Is Not Null))
ORDER BY MSysObjects.Database;

This will list it by table:
SELECT MSysObjects.Name, MSysObjects.Database
FROM MSysObjects
WHERE (((MSysObjects.Database) Is Not Null))
ORDER BY MSysObjects.Name;

Hope that helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Toledo]

Thanks for your help.

 

[MichaelRed]

I think Toledo is wanting to go one (not to small a) step further. the statement " ... scan a listing of Access 2000 databases ... " implies (or I INFER?) that there is a list (recordset - tables array -etc?) which is a list of .MDBs where the desired function is to get the list of linked tables form all / each .MDB, not just the ones for the local (i.e. "currentdb&quot. That is a slightly more ambitious undertraking.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Sure, just write the code to sweep through whatever directories you want, connecting to each, building the recordset with the sql above, and putting the results somewhere to be used later.



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MichaelRed]

hmmmmmmmmmmmmm, again, I infer a different drumbeat? Toledo (as I read it) has the list, so the sweeping part is not really necessary. But there IS the mattter of opening each seperate db (not "CurrentDb&quot and using hte MSysObjects of THAT db, not the local ("CurrentDb) one.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Toledo]

You are right. I have a list of over 1000 Access 2000 databases and I'm looking for a method to programmatically open each one, select the names and data about attached tables from the MSysObjects table, export it to a file, close the database and go on to the next one until the list is complete.

I realize this can be done on an individual basis, but how to automate it is the problem.

Thanx,
Bud

 

[JeremyNYC]

Untested code:


So now the trick is to create a recordset of all of your databases and loop through it. For each one, set sPath and db. Then set rst
Sub GetLinks()
Dim strPath As String
Dim strsql As String
Dim dbLocal As DAO.Database
Dim rstLocal As DAO.Recordset
Dim dbElse As DAO.Database
Dim rstElse As DAO.Recordset

strsql = "SELECT Path from tblDatabase"
Set dbLocal = CurrentDb
Set rstLocal = dbLocal.OpenRecordset(strsql, dbOpenSnapshot)
If Not (rstLocal.BOF And rstLocal.EOF) Then
Do Until rstLocal.EOF
strsql = "SELECT MSysObjects.Database AS SourceDB" _
& " FROM MSysObjects " _
& " GROUP BY MSysObjects.Database " _
& " HAVING (((MSysObjects.Database) Is Not Null)) "
Set dbElse = DBEngine.OpenDatabase(rstLocal!Path)
Set rstElse = dbElse.OpenRecordset(strsql, dbOpenSnapshot)
If Not (rstElse.EOF And rstElse.BOF) Then
Do Until rstElse.EOF
Debug.Print rstElse!SourceDB
rstElse.MoveNext
Loop
End If
rstLocal.MoveNext
Loop
End If

End Sub

Hope this helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MichaelRed]

pseudo tested code:

Public Function basGetInfo()

    'Michael Red 7/1/2003 for Tek-Tips thread700-589833, "Toledo"
    'Pseudo Tested:
    
    'Requirements:
    '   A table [tblMyDbs] with the single field [dbName] as Text (length to suit you needs) _
        The entry is the full path and name of the database file (.MDB) where you want to know _
        (i.e. collect) the names and other info re the linked tables in THAT db

    '   A Table [MyLinkInfo] with fields: _
            [dbName] as Text, length to suit (the [ForeginName] from the DB) _
            [Flags] as Text, length to suit - suggest 20 (the flag field from from the DB) _
            [Name] as Text, Length to suit The Name filed fro the db.   Theis is the "Name" _
                used in the db to reference the linked object (table) in the DB
            
    '   Of course anyone with this code can easily change / adapt these to their needs, the above are _
        for use with the UN-Modified code shown here.

    Dim WrkSpc As DAO.Workspace
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim dbsLocal As DAO.Database
    Dim rstLocal As DAO.Recordset

    Dim strSql As String
    Dim strSqlIns As String
    Dim strSqlSel As String
    Dim strSqlFrm As String
    Dim strSqlWhr As String
    Dim strSqlGrp As String
    Dim strSqlHvg As String
    Dim strSqlOrBy As String
    Dim strSqlEnd As String
    Dim Quo As String * 1
    Dim Idx As Integer

    Quo = Chr(34)

    Set dbsLocal = CurrentDb
    Set WrkSpc = CreateWorkspace("", "admin", "", dbUseJet)

    Set rstLocal = dbsLocal.OpenRecordset("tblMyDbs", dbOpenDynaset)

    On Error GoTo ErrExit

    While Not (rstLocal.EOF)

        Set dbs = WrkSpc.OpenDatabase(rstLocal!dbName, False)

        strSqlIns = "Insert InTo MyLinkInfo " & _
                    "(dbName, Flags, ForeignName, Name, LinkDbName) "

        strSqlSel = "Select " & _
                    "Database, " & _
                    "Flags, " & _
                    "Name, " & _
                    "ForeignName, " & _
                    Quo & rstLocal!dbName & Quo & " as LinkDbName "

        strSqlFrm = "FROM " & rstLocal!dbName & ".MSysObjects "

        strSqlWhr = "Where " & _
                    "(MSysObjects.Name Not Like " & Quo & "MSys*" & Quo & _
                    " and " & _
                    "MSysObjects.Type = 6) "

        strSqlGrp = "GROUP BY " & _
                    "Database, " & _
                    "Flags, " & _
                    "Name, " & _
                    "ForeignName "

        strSqlHvg = "HAVING ((Database) Is Not Null) "

        strSqlOrBy = "ORDER BY Database"

        strSqlEnd = ";"

        strSql = strSqlSel & strSqlFrm & strSqlWhr & _
                 strSqlGrp & strSqlHvg & strSqlOrBy & strSqlEnd

'******* This BLOCK of code is just for convenince in testing.  It CAN (and Should) be removed for Production
        Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset)

        While Not rst.EOF
            Debug.Print rst!Name
            rst.MoveNext
        Wend

        rstLocal.MoveNext
'*******End Testing Block

        strSql = strSqlIns & strSqlSel & strSqlFrm & strSqlWhr & _
                 strSqlGrp & strSqlHvg & strSqlOrBy & strSqlEnd

        dbsLocal.Execute strSql

    Wend

    GoTo NormExit

ErrExit:
    Stop

NormExit:

End Function

An interesting exercise. A bit more frustrating than usual, perhaps the frustration was enhanced by forgetting to take my meds yesterday. I ALWAYS seenm to have extra fumbledness in the keyboard:brain connections when I do that.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places