Help with SQL Server table linker function.

[sparkbyte]

I have a function that links all the available tables in a SQL Server database. Works great except... it also links the SQL Server system tables for the database. I need to stop it from also linking the system tables.

Here is the function.

Public Function Link_SQL_Tables()

Dim stConnect As String
Dim cnn As New ADODB.Connection

Dim objSQLDataBaseCat As ADOX.Catalog
Dim objLocalAccesDBCat As ADOX.Catalog
Dim objAccessLinkedTbl As ADOX.Table
Dim objSQLTbl As ADOX.Table

Set objSQLDataBaseCat = New ADOX.Catalog
cnn.Open "Driver={SQL Server};" & _
           "Server=MyServer;" & _
           "Database=MyDatabase;" & _
           "Trusted_Connection=True"

Set objSQLDataBaseCat.ActiveConnection = cnn

'****************************************

'*******>>> LOCAL ACCESS CAT
Set objLocalAccesDBCat = New ADOX.Catalog

'for local connections, for remote connections set a connection string here
objLocalAccesDBCat.ActiveConnection = CurrentProject.Connection
'***************************

For Each objSQLTbl In objSQLDataBaseCat.tables
  
      If objSQLTbl.Type = "TABLE" Then
      
            'check if it's linked

            If IsNull(DLookup("ForeignName", "MSysObjects", "ForeignName ='" + _
                        "dbo." + objSQLTbl.Name + "' AND MSysObjects.Type=6")) Then
                   'not there, link it

                    '   (MSysObjects.Type)
                    '   1  Table  -  Local Table
                    '   4  Table  -  Linked Access Table
                    '   6  Table  -  Linked ODBC Table

                    Set objAccessLinkedTbl = New ADOX.Table
                    objAccessLinkedTbl.Name = objSQLTbl.Name
                    Call AttachDSNLessTable(objSQLTbl.Name, "dbo." & objSQLTbl.Name, "MyServer", "MyDatabase")
                    
            Else
            
            End If
    
      End If
Next
Debug.Print "Completed Connecting SQL Tables."
Set objSQLDataBaseCat = Nothing

End Function

Thanks

John Fuhrman

 

[Andrzejek]

"the SQL Server system tables for the database" can you tell which tables are those? Do they have some property in common, like the beginning of the name of the tables? If so, you can exclude them somehow, like (just a guess here):

If objSQLTbl.Type = "TABLE" Then[blue]
    If Left(objSQLTbl.Name, 3) <> "SYS" Then[/blue][green]
        'check if it's linked[/green]
...

Have fun.

---- Andy