Linked Tables

[sims.anderson2010]

I have a table, in what I call my "catalog" database, with a list of access databases that we have at our facility. Is there a way with VBA to loop throught this list and determine if each database has linked tables or passthrough queries and then store these "links" as paths in another table.

We have over 300 databases and they are interlinked. I am currently looking at each database and logging the links in my "catalog" database, but it is taking a long time.

Sims

 

[plog]

list of access databases that we have at our facility

Define "list". Do you have the full path (i.e. "P:\Databases\Accounting.mdb") of all these databases, or just a name (i.e. "Accounting Database").

If you have the full path, you should be able to write a function to open all those databases up run a query against each databases 'MsysObjects' table and throw that information into a table on the database that you are running that function out of.

 

[sims.anderson2010]

By list, I have a table, in which one field is the name.extension and another field is the path, so, concatenated, yes I have the full path. I just have no Idea how to do what you said below. I have been doing access programming for about 1.5 yrs, but it is all things that I learn in books or from this and similar forums.

Any help in coding that you can supply would be wonderful.

I know how to loop through the recordset, but getting the info from the database to "throw" into a table in my catalog is where I'm stuck.

Sims

 

[plog]

MsysObjects is a hidden table in all Access databases, it lists the objects within that database (i.e. queries, tables). Open up an existing database and paste this SQL into a query:

SELECT * FROM MsysObjects;

Run it and sort by 'Type', all the ones with a 1 are tables within your database, all those not begining with 'Msys*' are ones that are shown under Tables in your database viewer.

Offhand, I don't know the answer to the following, so you need to spend some time on google finding these things out:

1. Can you determine from MsysObjects (or another system table) if a table is linked or not? Is it a different Type value or a different field in there that denotes a linked table?

2. If 1 is possible, can and how do you determine the exact path of the linked table?

3. How to use VBA to connect to a MsysObjects table in a database that you are not currently in?

Personally, I'd break this up into chuncks. First create a test table with a dummy local table and a linked one. Then find out how you differentiate the two. Next, in that same test database I'd create a Schema table to hold the information I want to hold (Database name, table name, table type, etc.) and I would concentrate on writing code to populate that table for the tables in test database. Then I'd move on to connecting to one remote database and extracting its data. Finally, Id' bring in my table of databases and their paths and modify my code to loop through all those records to extract the data into that Schema table.

 

[sims.anderson2010]

Ok. I'll keep looking and hope there are more replies

 

[TJPoorman]

Here is the code I use to get the table names and connections from a particular database:

Public Function GetDatabaseTables(strDatabase As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
 
Set db = OpenDatabase([COLOR=red]strFrontEndPath[/COLOR] & strDatabase & ".accde")
DoCmd.SetWarnings False
 
For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) = "msys" Then  'skip these tables
    ElseIf Nz(tdf.Connect, "") = "" Then    'these are local tables.  record them with the tag ***Local***
        DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                    "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '***Local***')"
    ElseIf Left(tdf.Connect, 9) <> ";DATABASE" Then     'these are not standard database connections so record the whole connect string
        DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                    "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '" & tdf.Connect & "')"
    Else    'these are standard database connections so record only the connected database
        strConnect = Mid(tdf.Connect, InStrRev(tdf.Connect, "\") + 1)
        DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                    "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '" & strConnect & "')"
    End If
Next tdf
 
DoCmd.SetWarnings True
db.Close
Set db = Nothing
End Function

You could then create a loop through all your databases calling this function. You will need to change the part in red to your full file path minus the database.

I have a table call tblDatabaseTables with 3 fields: Database, TableName, Connection

 

[sims.anderson2010]

Much appreciated, I can definately see how this could be modifiedfor my needs. Thanks.