DISTRIBUTING A CLIENT/SERVER SOLUTION

After your application is fully migrated to client/server, a few outstanding issues still exist, such as how you can distribute the application. The largest challenge comes into play when you need to send it to someone geographically removed from you or when you determine that you have a salable commercial application. Some things to consider are creating the SQL Server database, setting up an ODBC data source, and loading any existing data into the SQL Server tables.

Programmatically Setting Up an ODBC Data Source

To implement a client/server solution, it's necessary to set up the ODBC data source. It's not a difficult task to complete manually, but if you're developing applications for the commercial market or large numbers of corporate users, you can consider including an automated way to accomplish this; your alternatives are to visit each desktop yourself and configure each PC individually, or teach your users how to install a data source themselves. Visiting every single desktop is generally impractical—especially in the case of a commercialized application—and your users might not be able to set up the ODBC data source. Therefore, creating your own custom installation program might be a good alternative.

You can use DLL functions to set up an ODBC data source. The ODBC Administrator itself is a DLL that contains a number of functions for installing drivers and adding, modifying, and removing data source definitions. The installation program uses these functions to install the ODBC components necessary to use the sample time and billing database.

Listing 24.3 shows the primary subroutine that runs the installation program, which is contained in the code module for frmODBCInstaller that's in Chap24.mdb on the CD-ROM. You can view this code yourself by opening the form's module in Design view. You can use this as the basis for your own installation program. Listing 24.3 installs the SQL Server ODBC driver and sets up a data source. It can optionally install ODBC Driver Manager and the Control Panel application. Figure 24.11 shows frmODBCInstaller in Form view.

Listing 24.3. Chap24.mdb: Running the Installation Program

Private Sub DoInstallerFunctions()
    On Error GoTo DoInstallerFunctions_Error

    Dim strInfFile As String
    Dim strDriverInfo As String
    Dim strPath As String
    Dim lngBytes As Long
    Dim strDriver As String
    Dim strAttrib As String

    ' Install ODBC driver?
    If Me!chkInstDriver Then
        ' Assign null to INF file argument to force
        ' installer to use the driver info
        strInfFile = vbNullChar

        ' Build driver information
        strDriverInfo = Me!txtDriverName & vbNullChar & _
            "Driver=" & Me!txtDriverDLL & vbNullChar & _
            "Setup=" & Me!txtDriverDLL & vbNullChar & _
            "APILevel=1" & vbNullChar & _
            "ConnectFunctions=YYN" & vbNullChar & _
            "SQLLevel=1" & vbNullChar & _
            vbNullChar

        ' Set buffer for destination path
        strPath = Space$(acsMaxPathLen)

        ' Call SQLInstallDriver installer function
        If acsSQLInstallDriver(strInfFile, strDriverInfo, _
            strPath, acsMaxPathLen, lngBytes) Then

            mstrSystemDir = Left(strPath, lngBytes)
            SafeCopy mstrSourceDir & Me!txtDriverDLL, _
                mstrSystemDir & "" & Me!txtDriverDLL
        Else
            Error 30001
        End If
    End If

    ' Configure data source?
    If Me!chkInstDS Then
        ' Build data source attribute info
        strAttrib = "DSN=" & Me!txtDSN & vbNullChar & _
            "Server=" & Me!txtServer & vbNullChar & _
            "Database=" & Me!txtDatabase & vbNullChar & _
            "Description=SQL Server Data" & vbNullChar & _
            "OEMtoANSI=No" & vbNullChar & _
            vbNullChar

        ' Call SQLConfigDataSource installer function
        If Not acsSQLConfigDataSource(0&, odbcAddDSN, _
            Me!txtDriverName, strAttrib) Then
            Error 30002
        End If
    End If

    ' Install driver manager?
    If Me!chkInstDriverMan Then
        ' Set buffer for destination path
        strPath = Space$(acsMaxPathLen)

        ' Call SQLInstallDriverManager to get install path
        If Not acsSQLInstallDriverManager(strPath, _
            acsMaxPathLen, lngBytes) Then
                Error 30003
        Else
            mstrSystemDir = Left(strPath, lngBytes)
            SafeCopy mstrSourceDir & "odbc32.dll", _
                mstrSystemDir & "odbc32.dll"
            SafeCopy mstrSourceDir & "odbccr32.dll", _
                mstrSystemDir & "odbccr32.dll"
        End If
    End If

    ' Install control panel applet?
    If Me!chkInstAdmin Then
         ' Set buffer for destination path
        strPath = Space$(acsMaxPathLen)
        lngBytes = acsGetSystemDirectory(strPath, acsMaxPathLen)
        If lngBytes > 0 Then
            mstrSystemDir = Left(strPath, lngBytes)
            SafeCopy mstrSourceDir & "odbccp32.dl~", _
                mstrSystemDir & "odbccp32.dll"
            acsWritePrivateProfileString _
                "MMCPL", "odbc", _
                mstrSystemDir & "odbccp32.dll", _
                "CONTROL.INI"
        Else
            Error 30004
        End If
    End If

    MsgBox "Installation complete!", vbInformation, Me.Caption

DoInstallerFunctions_Exit:
    Exit Sub
DoInstallerFunctions_Error:
    Select Case Err
        Case 30001 To 30005
            Call acsErrODBCInst("DoInstallerFunctions")
        Case Else
            MsgBox Error, vbExclamation, Me.Caption
    End Select
    Resume DoInstallerFunctions_Exit
End Sub

Re-creating a SQL Database with Server Scripts

SQL Server provides a great utility for re-creating SQL Server objects. By using Enterprise Manager, one of the administrative tools provided with SQL Server, you can select which objects you want to create, and SQL Server writes the scripts. This is especially useful for distributing your client/server application commercially, or for distributing to sites that don't have a resident SQL Server expert to set up the SQL Server device, set up the database, and then create all the tables with their necessary defaults, rules, and so forth.

To create SQL scripts, follow these steps:

Another alternative is using Automation to create your SQL Server database. A feature in SQL Server 6.0 and later is Distributed Management Objects (DMO). The SQL-DMO object model includes objects, properties, methods, and collections that you can use to write programs to administer multiple SQL Server databases distributed across your network. You can use Visual Basic for Applications or C++ to write programs that use SQL-DMO objects (from Access, create a reference in VBA to the Microsoft SQLDMO Object Library). Refer to the SQL Server Books Online for more information on DMO. SQL Server 7.0 has an additional tool set—Data Transformation Services (DTS)—that can be scripted or programmed for various database management tasks. Finally, you can use ADO to send data definition language (DDL) commands from your application to SQL Server to modify a database structure (see the Access help topic Microsoft ADO Extensions for DDL and Security (ADOX) Programmer's Reference for more information).

Loading Existing Data into SQL Server

After your tables are created in SQL Server, you should have a plan for loading any existing data onto the server. This can be as simple as a series of Access queries or as complex as an interactive, flexible data-conversion program.