469,936 Members | 2,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

SQL-DMO tutorial or sample code?

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.

TIA

Andrew

--
I don't check mail sent to this address.
If you really want to contact me direct
then write to :
andrew at webster dot org
Nov 12 '05 #1
1 3469
rkc

"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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Peter | last post: by
10 posts views Thread by Dagwood | last post: by
9 posts views Thread by Grim Reaper | last post: by
14 posts views Thread by diskoduro | last post: by
3 posts views Thread by datapro01 | last post: by
14 posts views Thread by Developer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.