HOWTO: Create a 3-Tier App using VB, MTS and SQL Server

• ActiveX Data Objects (ADO), version 1.5

SUMMARY

This article describes how to create a Visual Basic database application, which uses components that are installed under Microsoft Transaction Server (MTS). The steps in the MORE INFORMATION section outline what is necessary to set up the database application using Visual Basic (VB) and MTS.

This article assumes that you are running MTS 2.0 and SQL Server 6.5 on the same computer on which you develop the application.

MORE INFORMATION

To set up an application that uses remote components running under the control of MTS there are four basic steps:

1. Create the server component. This is an ActiveX DLL written in Visual Basic.



2. Create the client application. This is a standard EXE written in Visual Basic.



3. Install the server components into a MTS package.



4. Setup the client computers (if necessary).

Step 1: Creating the Remote Component

1. Create a new Visual Basic ActiveX DLL project.



2. From the Project menu, choose References and add a reference to the Microsoft ActiveX Data Objects 1.5 Library and to the Microsoft Transaction Server Type Library.
   
   NOTE: For Visual Basic 6.0, select the Microsoft ActiveX Data Objects 2.0 Library.



3. From the Project menu, choose Project Properties. Click the General tab and change the Project Name to MTSTest. Make sure that the Threading Model is Apartment Threaded.



4. Add the following code to the GENERAL DECLARATIONS section of the default class module:

   
      Option Explicit
   
      Public Function Database_Test_Method( _
      ByVal szConnect As String) As String
   
         On Error GoTo errhandler
   
         Dim ctxObject As ObjectContext
         Set ctxObject = GetObjectContext()
   
         Dim cn As ADODB.Connection
         Dim rs As ADODB.Recordset
   
         Set cn = New ADODB.Connection
         With cn
            .ConnectionTimeout = 10
            .ConnectionString = szConnect
            .Open
         End With
   
         Set rs = New ADODB.Recordset
         rs.Open "SELECT * FROM Authors", cn, adOpenForwardOnly, _
                 adLockReadOnly
   
         Database_Test_Method = "First value from database:  " _
                              & rs(0).Value
   
         rs.Close
         cn.Close
   
         Set rs = Nothing
         Set cn = Nothing
   
         ctxObject.SetComplete
         Exit Function
   
         errhandler:
   
            ctxObject.SetAbort
            Err.Raise vbObjectError, "MTSTest.Class1.Database_Test_Method", _
                                      Err.Description
            Exit Function
   
      End Function
   
      Public Function General_Test_Method() As String
   
         On Error GoTo errhandler
   
        Dim ctxObject As ObjectContext
         Set ctxObject = GetObjectContext()
   
         General_Test_Method = "String from General_Test_Method"
   
         ctxObject.SetComplete
         Exit Function
   
         errhandler:
            ctxObject.SetAbort
            Err.Raise vbObjectError, "MTSTest.Class1.General_Test_Method", _
                                      Err.Description
            Exit Function
   
      End Function 




5. Compile the project into a DLL.

Step 2: Creating the Client Application

1. Create a new Visual Basic standard EXE project.



2. Add two Command buttons to the standard form.



3. Add the following code to the GENERAL DECLARATIONS section of the form:

   
      Private Sub Command1_Click()
         Dim obj As Object
         Set obj = CreateObject("MTSTest.Class1")
   
         MsgBox obj.General_Test_Method
         Set obj = Nothing
      End Sub
   
      Private Sub Command2_Click()
         Dim szConnect As String
         szConnect = "Driver={SQL Server};" & _
               "Server=YourServerName;Uid=sa;Pwd=;Database=pubs"
   
         '(NOTE: You may need to change the connection
         ' information to connect to your database.)
   
         Dim obj As Object
         Set obj = CreateObject("MTSTest.Class1")
   
         MsgBox obj.Database_Test_Method(szConnect)
         Set obj = Nothing
   
      End Sub
   
     Private Sub Form_Load()
   
         Command1.Caption = "Call General_Test_Method"
         Command2.Caption = "Call Database_Test_Method"
   
      End Sub 

Step 3: Installing the Component Into MTS

1. Start the Microsoft Management Console (MMC), Transaction Server Explorer.



2. Using the Treeview in the left-hand pane, expand each folder by clicking the plus sign beside the folder. First, select and expand Microsoft Transaction Server, then expand Computers, then My Computer and finally expand Packages Installed.



3. Right-click Packages Installed, select New and then choose Package.



4. When the Package Wizard screen displays, select Create and Empty Package. Name the package MTSTest and have it run as the interactive user.



5. Select the Components folder under the newly created package, right-click, choose New and then select Component.



6. Select Import component(s) that are already registered and choose MTSTest.Class1 from the list that displays.

(Optional) Step 4: Setting up Remote Clients

Steps to Create the MTS Setup Program

1. Start Microsoft Management Console.



2. Using the Treeview in the left-hand pane, expand each folder by clicking the plus sign beside the folder. First, expand Microsoft Transaction Server, then Computers, My Computer and then expand the MTSTest Package folder.



3. Right-click the MTSTest package, select Export, and then select the directory to which you want to export the package.



4. Navigate to the directory to which you exported the package and open the clients directory. In this directory, you will find an executable built by exporting. Move this EXE to the CLIENT computer and run the EXE. (Do NOT run this program on the server). This program correctly registers the remote components on the client computer.



5. Run the client setup EXE for the client application, created in step 2, on the client computer.

REFERENCES

MTS 2.0 Online Help

For additional information about distributing Visual Basic Clients using the Visual Basic setup wizard, please see the following article(s) in the Microsoft Knowledge Base:

Q161837 HOWTO: Create a DCOM Client/Server Application

Q177394 HOWTO: Troubleshoot Run-Time Error '429' in DCOM Applications

Q172869 HOWTO: Debug a Component Written with Visual Basic 5.0

Additional query words:

Keywords          : kbADO200 kbDatabase kbDCOM kbMTS kbVBp600 
Version           : WINDOWS:1.5
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: May 27, 1999