Check if table exist

  • Thread starter Thread starter David W
  • Start date Start date
You can find the linked tables by looping through the TableDefs collection,
and finding those that have a Connect property that is longer than zero
characters.

Alternatively, you can query the table Access uses to manage the objects in
your database:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (([Type] IN (6,4))
AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*"))
ORDER BY MsysObjects.Name;
 
Allen Browne said:
Alternatively, you can query the table Access uses to manage the objects
in your database:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (([Type] IN (6,4))
AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*"))
ORDER BY MsysObjects.Name;
Click to expand...

I hadn't noticed before, but Microsoft doesn't follow its own "rules" for
the use of reserved words such as Name...

weird.
 
I am wanting to check at the opening of my frontend program to see if the
linked tables are present or not, and if they are not all present I would
like to prompt the user of the problem and how would you go about linking
them back through code.
 
Please excuse me for being a dummy with this,
but this is a little over my head,

where does this go and does it need to be modified to work with what I have
got?

Its one dbf file say its named "000C8"
and there are eleven tables in that dbf.

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function

Sub sListPath()
Dim loTd As TableDef
CurrentDb.TableDefs.Refresh
For Each loTd In CurrentDb.TableDefs
Debug.Print fGetLinkPath(loTd.Name)
Next loTd
Set loTd = Nothing
End Sub
 
Back
Top