Utility to Build VB Class to Match SQL Stored Procedures

As usual, I am loathe to trudge through what I call the "housekeeping" code. You know what I mean, code to initalize variables and test for nulls. It is necessary and is included in the source code, but I'll refrain from explaining each line of code here.

Instead, I'll focus on the 2 methods that are the most critical:

• ConnectToDB()
• CreateCodeFromStoredProcs()

Function: ConnectToDB()

The connection routine references objects from the SQLDMO (Distributed Manangement Objects) and SQLNS (SQL Namespace) libraries. We will be using Server, Database and Namespace objects in the code behind our main form, so the first thing we need to do is declare some form level variables.

    Private oSQLServer As SQLDMO.SQLServer
    Private oCurDB As SQLDMO.Database
    Private objSQLNSObj As SQLNamespaceObject
    Private objSQLNS As SQLNamespace
    Private hArray(10) As Long

Note: The following code was adapted from the Microsoft Sample code that ships with SQL Server. The code in these examples is an excellent source for developers wishing to learn more about how to manipulate the SQL Server DMO and NS objects from code.

If the user has supplied valid connection variables (Server, Database, User ID and Password), then an attempt is made to connect to a database by executing the following sub routine:

' **************** BEGIN CODE HERE ****************
Private Sub ConnectToDB()
    On Error Resume Next

    Dim i As Integer
    Dim strConnect As String
    
    Err.Clear
    Screen.MousePointer = vbHourglass
    
    ' Assume success
    fConnected = True

    ' Set SQLDMO object

    Set oSQLServer = New SQLDMO.SQLServer
    oSQLServer.LoginTimeout = 10
    oSQLServer.ODBCPrefix = False
    
    'Attempt a connection, then fill the properties stuff
    oSQLServer.ApplicationName = "SQL-DMO Explorer"
    oSQLServer.Connect strServer, strUID, strPWD
    oSQLServer.Application.GroupRegistrationServer = ""
    
    ' Set Current SQLDMO Database object
    Set oCurDB = oSQLServer.Databases(strDatabase)
        
    ' Create SQL Namespace Object
    Set objSQLNS = New SQLNamespace
    objSQLNS.Initialize "EMS Admin Namespace", SQLNSRootType_Server, _
                            "Server=" & strServer _
                          & ";UID=" & strUID _
                          & ";pwd=" & strPWD & ";", hWnd
    
    ' get a root object of type Server and walk down the hierarchy from there

    hArray(0) = objSQLNS.GetRootItem
    ' get first level server->databases
    hArray(1) = objSQLNS.GetFirstChildItem(hArray(0), SQLNSOBJECTTYPE_DATABASES)
    ' get second level server->databases->database('pubs')
    hArray(2) = objSQLNS.GetFirstChildItem(hArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)
    ' get a SQLNamespaceObject to execute commands against on the wanted level
    Set objSQLNSObj = objSQLNS.GetSQLNamespaceObject(hArray(2))

    Screen.MousePointer = vbDefault

    If Err.Number > 0 Then fConnected = False

End Sub
' ***************** End CODE HERE *****************