Find table names of linked tables

[josephff]

I have this code here which will get me the table names that are local..

Dim strSQL As String
    Dim lst As ListBox
 
    Set lst = Me![YearsList]
 
    strSQL = "SELECT [Name] FROM MsysObjects " & _
    "WHERE (([Type] = 1) AND ([Name] Not Like '~*') AND ([Name] Not Like 'MSys*') AND ([Name] Not Like 'Branches') AND ([Name] Not Like 'CurrentVariables') AND ([Name] Not Like 'CO-Main') AND ([Name] Not Like 'COMain') AND ([Name] Not Like 'Employee') AND ([Name] Not Like 'Training') AND ([Name] Not Like 'TrainingTitles') AND ([Name] Not Like 'TrainingUpload') AND ([Name] Not Like '441-Main') AND (Left$([Name], 5) = '" & branchsub & "') " & _
    "AND ([Name] Not Like 'Parts') AND ([Name] Not Like 'POMain')AND ([Name] Not Like 'Users') AND ([Name] Not Like 'Years')AND ([Name] Not Like 'f_D45D44BBAA584A60B80EACD979B58087_Data') AND ([Name] Not Like 'WBTTitle') AND ([Name] Not Like 'TrainingEvents') AND ([Name] Not Like 'PayCards')) ORDER BY [Name];"
 
 
    lst.RowSourceType = "Table/Query"
    lst.RowSource = strSQL

but since ive split my database it will not populate them...does anyone know a way to pull the names of all linked tables?

 

[Mr. B]

The following sql statement will list all local tables first and then list all of the Linked tables:

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=6) AND ((MSysObjects.Flags)=2097152)) OR (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Flags;

To get the local tables you need the criteria:
Type = 1 and Flag = 0

To get the linked tables you need the crieria:
Type = 6 and Flag = 2097152

 

[josephff]

The following sql statement will list all local tables first and then list all of the Linked tables:

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=6) AND ((MSysObjects.Flags)=2097152)) OR (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Flags;

To get the local tables you need the criteria:
Type = 1 and Flag = 0

To get the linked tables you need the crieria:
Type = 6 and Flag = 2097152

Thank you I will try this out and get back to you. Is there a website that maybe has all the variables for MsysObjects that you know of?

EDIT: scratch that ignorant question. I just googled MsysObjects and found a listing of them....Thanks again

 

[jdraw]

Use this sql to get linked table names

SELECT Name, ObjTyp
FROM (SELECT Name, Linked Table' AS ObjTyp
FROM MSysObjects WHERE ([Name] not Like '~*' AND [Name] not Like 'MSys*' AND MsysObjects.Type=6))

Good luck


Here's an old routine that should list the linked tables to the immediate window

Function fLinkedDB() As String

'Routine to tell in this database which file a linked table is linked to
'
'Call format - flinkedDB()
'Modified Aug 24, 2000 to write output to a file LinkedTbls.log

Dim mDB As Database
Dim tdf As TableDefs
Dim i As Integer
Dim MyBEPath As String
Dim strMsg As String
Dim sResponse As Variant
Set mDB = CurrentDb
Set tdf = mDB.TableDefs
On Error Resume Next


For i = 0 To tdf.Count - 1
MyBEPath = Mid(mDB(i).Connect, 10)
If MyBEPath <> "" Then
Debug.Print mDB(i).name
End If
Next i
Get_out:
End Function

 

[josephff]

simply changing MsysObjects.Type=1 to MsysObjects.Type=6 did the trick in my code.

I didnt have to do anything with the flags though? What exactly does the flag portion do?

Thanks again gentlemen for the swift help

 

[gemma-the-husky]

or this sort of thing

for each tdf in currentdb.tabledefs
     if tdf.connect<>"" then
           msgbox("linked table: " & tdf.name)
     end if
next