How To Create a 3-Tier Application Using VB, MTS, and SQL Server (186342)



The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7

This article was previously published under Q186342

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).
A more detailed explanation of each of the steps follows.

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. If you are using Com+ which ships with windows 2000 you will want to check Com+ Services Type 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. Under Windows 2000 start Programs, Administrative Tools, Component Services.
  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. Under Windows 2000 this is Com+ Applications.
  3. Right-click Packages Installed (Com+ Applications), 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. Under Com+ choose Library Application under Activation type.
  5. Select the Components folder under the newly created package, right-click, choose New and then select Component. Under Com+ select New Component from the Action menu drop down.
  6. Select Import component(s) that are already registered and choose MTSTest.Class1 from the list that displays.
NOTE: Now you should be able to run the client on the server computer successfully. If you want to set up remote clients, use the instructions found in step 4.

(Optional) Step 4: Setting up Remote Clients

There are really two parts to this step. The first part is creating a Visual Basic client setup program using the Visual Basic application setup wizard. The second part is creating and using the client setup program generated by MTS.

Detailed instructions are below for creating and using the client setup program generated by MTS.

For information on distributing VB Clients using the Visual Basic setup wizard, please see the article listed in the REFERENCES section.

NOTE: Please disregard step eight located in the "Distribute the Client", section of the article. There is no need to add a reference to the VBR file when using the client setup program provided by MTS. During the Visual Basic client setup process, remove all references to any remote components.

Steps to Create the MTS/Com+ 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. In Com+ a wizard will come up. On the first screen select the radio button which says Application Proxy.
  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). In Com+ there is no clients directory. You will find an MSI file to move to the CLIENT computer. This is a windows installer file. 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. If windows installer is on the machine just double click the MSI file to install.
You should now be able to run your client remotely against the component that you created and installed into MTS/Com+. If any problems are encountered please see the references section for information on troubleshooting and creating distributed applications.

REFERENCES

MTS 2.0 Online Help

MSDN Library Platform SDK, Component Services, COM+, Deployment

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

161837 How To Create a DCOM Client/Server Application

177394 How To Troubleshoot Run-Time Error '429' in DCOM Applications

259719 How To Export a COM+ Application to Client Computers

266717 How To Create a DCOM Client/Server Application by Using VB

267836 How To Create a DCOM Client/Server with Events by Using Visual Basic

268550 How To Use Dcomcnfg for a Visual Basic DCOM Client/Server Application

269330 How To Troubleshoot DCOM for Visual Basic Client/Server Applications


Modification Type:MinorLast Reviewed:3/2/2005
Keywords:kbDatabase kbDCOM kbhowto KB186342