SQL-DMO tutorial or sample code?

**rkc** · Nov 12 '05, 10:33 PM

Re: SQL-DMO tutorial or sample code?

"Andrew" <andrew_DONT_SP AM@webster.org> wrote in message
news:IDbbc.1365 69$Wa.131863@ne ws-server.bigpond. net.au...[color=blue]
> 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[/color]
now[color=blue]
> it's an mde, and will have to be for a while yet (it's too complex to[/color]
upsize[color=blue]
> overnight).
>
> So I need to be able to write something that will list the available[/color]
Servers[color=blue]
> 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.[/color]

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 getAvailableSer vers() 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.Applicat ion
Dim n As SQLDMO.NameList
Dim s As String
Dim i As Integer

Set n = oApplication.Li stAvailableSQLS ervers

For i = 1 To n.Count
s = s & ";" & n.Item(i)
Next

s = Mid$(s, 2)
getAvailableSer vers = s

Set n = Nothing
Set oApplication = Nothing

End Function

Public Function GetDatabaseName s(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.SQLServe r
Dim oDatabase As SQLDMO.Database
Dim bUseNTAuthoriza tion As Boolean
Dim oTable As SQLDMO.Table
Dim s As String

Set oSqlServer = New SQLDMO.SQLServe r

oSqlServer.Logi nTimeout = 30
bUseNTAuthoriza tion = True

If bUseNTAuthoriza tion Then
oSqlServer.Logi nSecure = True
oSqlServer.Conn ect ServerName
Else
oSqlServer.Conn ect ServerName, "sa", "skippy"
End If

For Each oDatabase In oSqlServer.Data bases
s = s & ";" & oDatabase.Name
Next oDatabase

s = Mid$(s, 2)
GetDatabaseName s = s

Debug.Print "audit level: "; oSqlServer.Inte gratedSecurity. AuditLevel

Set oDatabase = oSqlServer.Data bases.Item(5)
Debug.Print "Database: "; oDatabase.Name
For Each oTable In oDatabase.Table s
s = oTable.Name
If Left$(s, 3) <> "Sys" Then
Debug.Print s
End If
Next

Set oDatabase = Nothing
oSqlServer.Disc onnect
Set oSqlServer = Nothing
End Function

SQL-DMO tutorial or sample code?

SQL-DMO tutorial or sample code?

Comment