"Andrew" <an**************@webster.org> wrote in message
news:ID********************@news-server.bigpond.net.au...
Hi Group!
I'm looking for a tutorial in using SQL-DMO, or some sample code that will
allow me to let my users switch the SQL Server database that the front end
app is linking to as a back end.
I know that this is quite simple if the front end is an adp/ade, but for
now it's an mde, and will have to be for a while yet (it's too complex to
upsize overnight).
So I need to be able to write something that will list the available
Servers in a combo box or list box, then list the available databases in another
combo or list box, so that when the user clicks 'OK' the code will change
the connection. (You can imagine what the form would look like.)
I'm happy with the connection changing code - I've got a routine that does
it if you can pass it the server and database. What I need is a pointer
towards how to retrieve a list of servers, then a list of database on each
server, from whatever is available on the network at the time.
A trusted connection is fine for this app.
I hesitate to post any of this because it's just it's just playing around
with
stuff I don't know anything about code. It's not high quality, charge the
client a bundle and distribute it as an .mde software enginering.
With that out of the way, it might get you started.
Function getAvailableServers() As String
'returns a list of network visible instance of
'MS SQL Server 2000 as a semi-colon delimited string
Dim oApplication As New SQLDMO.Application
Dim n As SQLDMO.NameList
Dim s As String
Dim i As Integer
Set n = oApplication.ListAvailableSQLServers
For i = 1 To n.Count
s = s & ";" & n.Item(i)
Next
s = Mid$(s, 2)
getAvailableServers = s
Set n = Nothing
Set oApplication = Nothing
End Function
Public Function GetDatabaseNames(ServerName As String) As String
'returns a list of available databases on the
' passed in server as a semi-colon delimited string
Dim oSqlServer As SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim bUseNTAuthorization As Boolean
Dim oTable As SQLDMO.Table
Dim s As String
Set oSqlServer = New SQLDMO.SQLServer
oSqlServer.LoginTimeout = 30
bUseNTAuthorization = True
If bUseNTAuthorization Then
oSqlServer.LoginSecure = True
oSqlServer.Connect ServerName
Else
oSqlServer.Connect ServerName, "sa", "skippy"
End If
For Each oDatabase In oSqlServer.Databases
s = s & ";" & oDatabase.Name
Next oDatabase
s = Mid$(s, 2)
GetDatabaseNames = s
Debug.Print "audit level: "; oSqlServer.IntegratedSecurity.AuditLevel
Set oDatabase = oSqlServer.Databases.Item(5)
Debug.Print "Database: "; oDatabase.Name
For Each oTable In oDatabase.Tables
s = oTable.Name
If Left$(s, 3) <> "Sys" Then
Debug.Print s
End If
Next
Set oDatabase = Nothing
oSqlServer.Disconnect
Set oSqlServer = Nothing
End Function
