By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

SQL-DMO tutorial or sample code?

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.