Fastest/Easiest way to Identify if a TABLE is local or NOT

[Noob]

Hi,



I was wondering. If I have a DB with a mix of local & non local TABLE(s). What is the quickest/fastest/easiest way to know if a TABLE is local or NOT?

I am thinking it would be in the Navagation Pane. Anything with a round globe looking thing is something being referanced through the network (Just a guess on my part).

TIA,

Noob

[pbaldy]

A little arrow next to the table icon in the Nav Pane. A linked SQL Server or ODBC table will show the globe, but an Access table linked from another db will show the normal table icon, but either will have the little arrow next to it. So the arrow is the more reliable indicator.

[Noob]

So its the arrow; SWEET!!! Thanks!

In VBA how would you test for that? I am going to dig through the local variables & see if I can figure it out. Thanks again @pbaldy for saving my bacon.

[rpeare]

You'd have to check the msysobjects table (be extremely careful when you do this because it can corrupt your whole database if you attempt to make changes and don't know what you're doing)

here is some code I use to detect linked tables and re-establish them so if the database is moved none of the links break and it creates a link to SQL tables without a DSN.

I use the CONNECT field of msysobjects to detect linked items. To display the msysobjects table you have to specifically tell access to display system objects when.

Dim fsDim fsFile
'variables from odbc.txt
Dim sBoard As String
Dim sUser As String
Dim sPW As String
'------------------------
Dim sConnStr As String
Dim dbCurrent As DAO.Database
Dim tdfCurrent As DAO.TableDef
Dim iBoardCount As Long
Dim rstTables As DAO.Recordset
Dim sTable As String
Dim sCheckStr As String
Dim sErrMsg As String
'Dim iLink As Long
Dim db As Database
Dim tdf As TableDef
Dim sConn As String
Dim sDataPath As String
On Error GoTo ERRHANDLER
'Exit Sub


sDataPath = DLookup("datapath", "datapath")
'this part re-links back end access tables
If sDataPath <> CurrentProject.Path Then
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If InStr(tdf.Connect, "PPS_GOSHBoardUtility_be.accdb") Then
            sConn = Nz(tdf.Connect, "")
            tdf.Connect = ";DATABASE=" & CurrentProject.Path & "\PPS_GOSHBoardUtility_be.accdb"
            tdf.RefreshLink
        End If
    Next tdf
    db.Execute ("UPDATE DataPath SET DATAPATH ='" & CurrentProject.Path & "'")
End If

'this part establishes the connection string to the SQL database
Set fs = CreateObject("scripting.filesystemobject")
If fs.FileExists(CurrentProject.Path & IIf(Right(CurrentProject.Path, 1) = "\", "", "\") & "ODBC.txt") Then
    Set fsFile = fs.opentextfile(CurrentProject.Path & IIf(Right(CurrentProject.Path, 1) = "\", "", "\") & "ODBC.txt")
    sBoard = fsFile.readline
    sUser = fsFile.readline
    sPW = fsFile.readline
    sConnStr = "ODBC;DRIVER={SQL Server};DATABASE=GOSHB" & sBoard & ";SERVER=GOSHB" & sBoard & "DB;UID=" & sUser & ";PWD=" & sPW & ";"
    'sConnStr = "ODBC;DRIVER={SQL Server};DATABASE=GOSHB" & sBoard & "TEST;SERVER=GOSHB" & sBoard & "DBTEST;UID=" & sUser & ";PWD=" & sPW & ";"
    Debug.Print sConnStr
Else
    MsgBox "ERROR:  The File ODBC.txt does not exist in the same folder as this application" & vbCrLf & vbCrLf & "The ODBC.txt file must be present and contain the GOSH report runner user name on the first line and password on the second line" & vbCrLf & vbCrLf & "Please Create this file and try again", vbOKOnly, "Error Connecting to Database"
    Exit Sub
End If

'this part re-connects all the SQL tables as defined in a local table.
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstTables = CurrentDb.OpenRecordset("SELECT * FROM tblGOSHTables ORDER BY GOSHTable")


Do While rstTables.EOF <> True
    sTable = rstTables!goshtable
    If DCount("[name]", "MSysObjects", "[Name] = 'dbo_" & sTable & "'") = 0 Then
        'do whatever if the table does not exist adding the table is coded below
    Else
        'sCheckStr = DLookup("[connect]", "msysobjects", "[Name] = 'dbo_" & sTable & "'")
        'sCheckStr = Right(sCheckStr, Len(sCheckStr) - InStrRev(sCheckStr, ";"))
        'If sCheckStr = "DATABASE=GOSHB" & sBoard Then
        '    Debug.Print "already exists " & sTable
        '    'do whatever if the connection string is correct adding the table is coded below
        'Else
        '    Debug.Print "Relinking " & sTable
            Debug.Print "Dropping table " & sTable
            CurrentDb.Execute ("DROP TABLE dbo_" & sTable)
        'End If
    End If


    'If iLink = 1 Then
        Set tdfCurrent = dbCurrent.CreateTableDef("dbo_" & sTable)
        tdfCurrent.Connect = sConnStr
        tdfCurrent.SourceTableName = sTable
        dbCurrent.TableDefs.Append tdfCurrent
        Me.Refresh
    'End If


    rstTables.MoveNext
Loop
rstTables.Close


Set tdfCurrent = Nothing
Set dbCurrent = Nothing


Exit Sub


ERRHANDLER:
Debug.Print Err.Number & " " & Err.Description
If Err.Number = 3011 Then
    sErrMsg = sErrMsg & sTable & vbCrLf
Else
End If
    
If Len(sErrMsg) > 0 Then
    sErrMsg = "The following tables were not found in the GOSH database" & vbCrLf & vbCrLf & sErrMsg & vbCrLf & "Please verify the GOSH table name or delete the table from the table tblGOSHTables"
    MsgBox sErrMsg, vbOKOnly, "ERRORS On Database Startup"
End If

[Noob]

I found this test courtesy of Daniel Pineault.

Hi @rpeare, thanks for the help & good info about Msysobject. Yeah, I think you kind folks got me on the right path (again), so I am going to mark this off as Solved. Thanks a TON!!!

[pbaldy]

So its the arrow; SWEET!!! Thanks!

In VBA how would you test for that? I am going to dig through the local variables & see if I can figure it out. Thanks again @pbaldy for saving my bacon.

No problem. Sorry for being so slow to reply to the follow up question.