How to check that linked table exists? (1 Viewer)

[BigHappyDaddy]

Background: In my Access FE, I have created a "linked table" to a file on our AS400 database. I know I can check the MSysObjects table for the linked table name, but sometimes the file actually does not exist on the AS400.

The file can be in one of three states.

1. File exists with data.
2. File exists, but is empty.
3. File doesn't exist.

Question: What is the best way to determine the status of this linked table (file)?

 

[BigHappyDaddy]

Ok, I come up with a simple solution, but I would still be interested if there are better solutions available.

Public Function DoesBGONE7Exist() As Long
'****************************************
'*  Returns -1 if file does not exist   *
'*  Returns  0 if file is empty         *
'*  Returns  1 if file has records      *
'****************************************
On Error GoTo Err_Handler
    Dim lngCount As Long
 
    'If BGONE7_SCPP055 file does not exist, goes to Err_Handler
    lngCount = DCount("*", "BGONE7_DCPP055")
    If lngCount > 0 Then
        'Non-Empty table
        DoesBGONE7Exist = 1
    Else
        'Table exists, but contains no records
        DoesBGONE7Exist = 0
    End If
 
Exit Function
Err_Handler:
    DoesBGONE7Exist = -1
End Function