Gather properties of SQL Server Backend table via VBA

[kwvh]

I don't know if this is the correct forum, but hope so. I am wondering if it is possible to connect to SQL Server database tables with Access 2010, and then loop through the tables in VBA to determine for each table:
1. table name
2- Primary key name
3. Primary key field name

The following gets me the table names, but I don't know how to gather the other two pieces of information for each table.

Code: Select all

      Dim db As DAO.Database
      Dim tdf As DAO.TableDef
      Dim rst As DAO.Recordset
      Dim strName As String

    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tlkpTablesAndkeys", dbOpenDynaset)
        
    For Each tdf In db.TableDefs
        strName = tdf.Name
        rst.AddNew
        rst!TableName = strName
        rst.Update
    Next

    rst.Close
    Set rst = Nothing

    MsgBox "SQL Tables have been read into the table"

If you have any ideas how to accomplish this, please share.

Thanks!

Ken

[HansV]

Here is some code using DAO:

Code: Select all

Sub ListTables()
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  Set dbs = CurrentDb
  For Each tdf In dbs.TableDefs
    If (tdf.Attributes And dbSystemObject) = 0 Then
      Debug.Print tdf.Name
      For Each idx In tdf.Indexes
        If idx.Primary Then
          Debug.Print "- " & idx.Name
          For Each fld In idx.Fields
            Debug.Print "  - " & fld.Name
          Next fld
        End If
      Next idx
    End If
  Next tdf
End Sub

It can easily be adapted to add records to a table instead of producing output in the Immediate window.

[HansV]

And here is some code that uses ADODB and ADOX to look at the SQL Server database itself. You need to set references to the Microsoft ActiveX Data Objects 6.0 Library and Microsoft ADO Ext. 6.0 for DDL and Security.

It assumes that you know how to create a connection string for the SQL Server backend.

Code: Select all

Sub ListTables()
  Dim objCon As ADODB.Connection
  Dim objCat As ADOX.Catalog
  Dim objTbl As ADOX.table
  Dim objInd As ADOX.Index
  Dim objCol As ADOX.Column
  Dim strCon As String
  strCon = "..." ' replace with real connection string
  Set objCon = New ADODB.Connection
  objCon.Open strCon
  Set objCat = New ADOX.Catalog
  Set objCat.ActiveConnection = objCon
  For Each objTbl In objCat.Tables
    If objTbl.Type = "TABLE" Then
      Debug.Print objTbl.Name
      For Each objInd In objTbl.Indexes
        If objInd.PrimaryKey Then
          Debug.Print "- " & objInd.Name
          For Each objCol In objInd.Columns
            Debug.Print "  - " & objCol.Name
          Next objCol
        End If
      Next objInd
    End If
  Next objTbl
  objCon.Close
  Set objCol = Nothing
  Set objInd = Nothing
  Set objTbl = Nothing
  Set objCat = Nothing
  Set objCon = Nothing
End Sub

[kwvh]

AWESOME!!!
Thanks, Hans!!!!