Get linked Table description vba

[jaryszek]

Hi,

i am trying to get Description from linked table:





My whole code is:

Option Compare Database

Function ConnectToOdbcTables()


Dim username, pass As String


username = Environ("username")
pass = "PasswordAccess"


'''You can use a pass-through query to list the table names from your SQL Server database. Open a recordset based on that query. Then loop through the recordset rows and link each table.
On Error GoTo ErroHandler


strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
"DATABASE=AccessLog;" & _
"SERVER=localhost;" & _
"PORT=5432;" & _
"UID=" & username & ";" & _
"PWD=" & pass & ";" & _
"Trusted_Connection=Yes;"




 DoCmd.Hourglass True
    Dim db As DAO.Database
    Dim tdef As TableDef
    Dim qdef As QueryDef
    Dim missingTbl() As String
    Dim upper As Integer
    Dim strMsg As String
    
    ReDim missingTbl(0 To 0)
    
    Set db = CurrentDb
    For i = 0 To CurrentDb.TableDefs.Count - 1
        Set tdef = db.TableDefs(i)
        Debug.Print tdef.Name
  ''          If Len(tdef.Connect) > 0 And Left(tdef.Name, 1) = "t" Then
            If Not (tdef.Name Like "MSys*" Or tdef.Name Like "~*") Then
                tdef.Connect = strConnect
                tdef.RefreshLink
            End If
    Next i


    For Each qdef In CurrentDb.QueryDefs
    Debug.Print qdef.Name
        If qdef.Type = dbQSQLPassThrough Then
            qdef.Connect = strConnect
        End If
    Next qdef
    
    DoCmd.Hourglass False
    
Exit Function


ErroHandler:
MsgBox "Error number is: " & Err.Number & "; " & Err.Description
DoCmd.Hourglass False


End Function

I was trying :

CurrentDb.TableDefs("t_cpuinfo").properties("Description")


tdef.Properties("Description")

goal is to get Table name like "public.t_bladeservers".

and method below from immediate also is not working:

?CurrentDb.TableDefs("t_cpuinfo").connect
ODBC;DRIVER={PostgreSQL Unicode};DATABASE=AccessLog;SERVER=localhost;PORT=5432;Trusted_Connection=Yes;

but it failed..

Please help,
Jacek

[June7]

Don't understand goal. Don't you already have table name?

[isladogs]

What you have highlighted is the linked table's connection string
You can get that and much more from the hidden, system table MSysObjects

In the query below
Connect - will give you the connection string (your description property) for a SQL Server table or similar datasource
Database - gives the path of a linked Access database
Name - linked table name in your database
ForeignName - table name in external database
Type = 4 (linked SQL Server) ; 6 (linked Access)

SELECT MSysObjects.Connect, MSysObjects.Database, MSysObjects.Flags, MSysObjects.Name, MSysObjects.ForeignName, MSysObjects.Type FROM MSysObjects
WHERE (((MSysObjects.Connect)<>'') AND ((MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

There are other fields in MSysObjects such as DateCreate, DateUpdate that may also be useful

HTH

[jaryszek]

Hi Ridders,

thank you, this is working.
Hmm i thought that there is a method to directy get connect field description

But this is ok, i can write SQL command and take the result.

But problem is still existing...

June7: Goal is to get description field from linked table. Why?
To now which schema i am using from description field:

TABLE=public.t_bladeservers

Best,
Jacek

[isladogs]

I used MSysObjects as it gives lots of info in one place and because it wasn't clear whether you wanted table name or the connection string itself.
It is unnecessarily convoluted to get the connection string for a table from the property description of one of its fields

The following code will give you the name, alias & connection string for all linked tables

Sub GetAllTableLinks()    Dim db As DAO.Database
    Dim myTDF As TableDef
    
    Set db = CurrentDb
    
    For Each myTDF In db.TableDefs
        If Len(myTDF.Connect) > 0 Then
            'Table is linked as it has a connection string
            'Name,Alias,ConnectionString
           Debug.Print myTDF.SourceTableName & "," & myTDF.Name & "," & myTDF.Connect
        End If
    Next myTDF


End Sub

[jaryszek]

thank you ridders52,

yes but still the connectionString will be without "TABLE=public."

i have to use postgreqsl query to see waht is a current schema.

Best,
Jacek

[jaryszek]

No other ways?

Best,
Jacek

[isladogs]

What did you get using each of the methods I suggested?

[jaryszek]

Hi ridders,

I am getting:

DSN=NowyTest;DATABASE=AccessLog;SERVER=localhost;PORT=5432;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4

using sql statement.

But the second method ( sorry i didnt notice "SourceTableName" before) is working!

SourceTableName in my case is "public.t_bladeservers"

So this is what i wanted!

Best,
Jacek

[isladogs]

You're welcome
You do realise the description field in your screenshot just combines the connection string & table name so you can get exactly that by combining the two items by either method
Modified code below for both methods

1. SysObjects

SELECT MSysObjects.Name, [Connect] & "; TABLE=" & [ForeignName] AS Description 
FROM MSysObjects
WHERE (((MSysObjects.Connect)<>'') AND ((MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

2. TableDefs

Sub GetAllTableLinks()

Dim db As DAO.Database
    Dim myTDF As TableDef
    Set db = CurrentDb
    
    For Each myTDF In db.TableDefs
        If Len(myTDF.Connect) > 0 Then
            'Table is linked as it has a connection string
            'Name,Alias,ConnectionString
          '  Debug.Print myTDF.SourceTableName & "," & myTDF.Name & "," & myTDF.Connect
           Debug.Print myTDF.Connect & "; TABLE =" & myTDF.SourceTableName
        End If
    Next myTDF
End Sub