[AccessD] A2003: List of Tables in an SQL dB

On 1 Feb 2006 at 21:57, Darren DICK wrote:

> Hello all
> Given the IP address of a server and the Name of the SQL dB
> Is it possible to retrieve the whole list of tables in the that SQL dB?
> 
Make sure that the SQL Server is configured to use TCP/IP
(in Enterprise Manager, right click on the server, select Properties - 
General, Click on Network Configuration and ensure TCP/IP is enabled)

Then try this  (watch for linewrap!):

Function ListTables()
Dim strConection As String
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim RS As ADODB.Recordset
strConnection = "Driver={SQL Server};Server=10.10.10.10;Database=databasename;Uid=loginname;Pwd=password;"
con.Open strConnection
Set cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT name, type FROM sysobjects WHERE type = 'U'"
Set RS = cmd.Execute
While Not RS.EOF
    Debug.Print RS(0)
    RS.MoveNext
Wend
End Function


-- 
Stuart