link to all tables in a SQL database

[sparkbyte]

Is there a way to list all the user tables in a SQL server database and then refresh the link if it exists or create it if it doesn't?

Thanks

John Fuhrman

 

[PWise]

select name 
from sysobjects 
where type = 'U'

will give you all table from a SqlServer Datebase

run this in a pass truh query and compare to

SELECT MSysObjects.Name, MSysObjects.ForeignName
FROM MSysObjects
WHERE (((MSysObjects.Type)=4));

 

[vbajock]

This will create a link to any data table in a secured SQL Db to an access db, or if it is already linked, refresh the link string.

Function ConnectSQLDatabaseToAccess()

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


'*******>>> SQL SERVER CAT ->>>> replace hard code with parameters or table lookup based on
'*** a form

Set objSQLDataBaseCat = New ADOX.Catalog
cnn.Open "Driver={SQL Server};" & _
"Server=SomeSQLServer;" & _
"Database=SomeDatabase;" & _
"Uid=xxx;" & _
"Pwd=yyyyyy;"

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=4")) Then

'not there, link it

Set objAccessLinkedTbl = New ADOX.Table
objAccessLinkedTbl.Name = objSQLTbl.Name

Set objAccessLinkedTbl.ParentCatalog = objLocalAccesDBCat

With objAccessLinkedTbl
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = stconnect
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Remote Table Name") = objSQLTbl.Name
End With

objLocalAccesDBCat.Tables.Append objAccessLinkedTbl

Set objAccessLinkedTbl = Nothing

Else
'refresh


Set objAccessLinkedTbl = New ADOX.Table
Set objAccessLinkedTbl.ParentCatalog = objLocalAccesDBCat
objAccessLinkedTbl.Name = DLookup("Name", "MSysObjects", "ForeignName ='" + "dbo." + objSQLTbl.Name + "' AND MSysObjects.Type=4")


With objAccessLinkedTbl
.Properties("Jet OLEDB:Link Provider String") = stconnect
End With

objLocalAccesDBCat.Tables.Refresh

Set objAccessLinkedTbl = Nothing
End If

End If
Next

Set objSQLDataBaseCat = Nothing



End Function

 

[sparkbyte]

vbajock, How would this be modified to use trusted connections?

I am getting a runtime error on

objLocalAccesDBCat.Tables.Append objAccessLinkedTbl



Thanks

John Fuhrman

 

[sparkbyte]

OK, god it changed to use windows Authentication.

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


But still getting the error.

Thanks

John Fuhrman

 

[vbajock]

You should post the error you are getting in detail, so we don't have to guess. Right now, I am guessing you did not set a reference to ADOX (Microsoft ADO Extension 2.8 for DDL and Security.


This should work for a trusted connection:

cnn.Open "Driver={SQL Server};" & _
"Server=SomeSQLServer;" & _
"Database=SomeDatabase;" & _
"Trusted_Connection=True;

 

[vbajock]

Also, for trusted security, you might want to comment out

.Properties("Jet OLEDB:Cache Link Name/Password") = True