ADO (ActiveX Data Objects)

ADOExpress Step-by-Step-from-Scratch Tech Note

Briefcase:  UDL and ADTG

Summary:  The Briefcase demo program, distributed as part of ADO Express, looks fairly simple, but there are some not-so-obvious steps that are needed to reproduce the program.  The Briefcase program establishes an ADO connection using a Data Link File (a UDL file).  When the database connection is dropped, data can be accessed locally in an Advanced Data Table Gram (ADTG) file and later written to the database.

A.  Background
B.  UDL File (Microsoft Data Link)
C.  ADTG (Advanced Data Table Gram)
D.  Data Module
E.  Verify Existence of UDL File and Database
F.  Setup Data Module and Form
G.  Run the Briefcase Program

A.  Background

This Tech Note is based on the Borland ADO Demo program "Briefcase" in directory
C:\Program Files\Borland\Delphi5\Demos\Ado\Briefcase)

When I first looked at the Briefcase demo program, which was installed as part of ADOExpress, I wanted to see what the properties of the various objects were — both visual and non-visual. With Delphi 5 running and the project loaded, I right clicked on the form area to see the option, View as Text, which toggled back to View as Form. With View as Text selected, I could quickly view the properties of the various objects.

A new Delphi 5 form option is also of interest here. When you right click on the form, there is a new Text DFM checkbox. With this checkbox selected, the DFM file can be viewed with an ASCII editor at any time. In earlier versions of Delphi, the DFM form files were in binary and could not be edited or viewed except through Delphi.

After reviewing the form's properties, I didn't have a clue what the UDL file was in the Provider property of the TADOConnection. The file specified as the Provider, C:\Program Files\Common Files\System\OLE DB\Data Links\DBDemos.UDL, wasn't the database since it was only 344 bytes long.

After reviewing the source code in BriefcaseMain.PAS, I didn't know why the briefcase file was saved as an ADTG file. The Employee.ADTG that was created only had 3021 bytes in it. I double-clicked on hit from the Windows Explorer, but there was no default file association. The Briefcase example makes a lot more sense if you know what an ADTG file is.

B.  UDL File (Microsoft Data Link)

An ADO database connection is established using a Microsoft Data Link File, which can be saved in a UDL file. Another Step-by-Step-from-Scratch Tech Note explains many details about UDL files and the information they contain. That Tech Note explains how to create the equivalent of the DBDemos.UDL file, which is referenced in the Provider Property of the TADOConnection in the Briefcase demo program.

A UDL file is a lot like a BDE alias. You can edit the properties of a UDL file directly in Windows, and change properties of the database connection, including the filename of the database itself. If all the properties of your database connection are "hard wired," then a UDL file is not necessary since all the information can be specified in an ADOConnection component.

C.  ADTG (Advanced Data Table Gram)

This is one of the OLE DB Persistence Providers [with the other provider being the Extensible Markup Language (XML)]. Even a search of Microsoft's web site reveals little information about this file format, which appears to be a proprietary Microsoft file format. An ADTG file will work with appropriate Microsoft products. Two Microsoft links that give a hint of information about ADTG are:
http://msdn.microsoft.com/library/psdk/dasdk/mdap6chg.htm
http://msdn.microsoft.com/library/psdk/dasdk/mspe7jg2.htm

D.  Data Module

One final note about the Briefcase demo. Since many new concepts are introduced in such demo programs, I think good programming practices should be demonstrated as part of the example. In particular, the example should have shown how to use a Data Module to "hold" the ADOConnection, ADODataSet, and DataSource, instead of cluttering the user interface in design mode with these "non-visual" components. A Data Module is the "clean" way of sharing such components, especially when more than one form needs to share them in an application. The example below shows how to use a Data Module even though the sample program only has a single form.

In Mastering Delphi 5, this briefcase model is mentioned on p. 579 in "A Snapshot of the Data" and on pp. 999-1000 in "Support the Briefcase Model."

E.  Verify Existence of UDL File and Database
  1. Verify Microsoft Data Link File (UDL file) exits. Double click on
    C:\Program Files\Common Files\System\OLE DB\Data Links\DBDemos.UDL

    2. Verify that the DBDEMOS.mdb file is specified as the database name as shown above.

  2. Close the Data Link Properties dialog box. (See Creating a UDL File Manually in Windows 98 for steps on how this file can be created manually.)
  3. Verify the Access database exists: From Windows Explorer, find the Access file C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb and double click on its icon.
  4. Verify the list of tables includes country, customer, employee, items, orders, parts, vendors.
  5. Close Access.

F.  Setup Data Module and Form
  1. Start Delphi 5

    In the Object Inspector for Form1, set these properties:
    Caption: Briefcase Demo
    Name: FormBriefcase
    Position: poScreenCenter
    Height: 390
    Width: 535

  2. File | New | Data Module | OK
    Change name from DataModule2 to DataModuleDBDemos
  3. File | Save Project As | Save In: C:\Temp (or other desired directory) |
    ScreenBriefcase.pas | Save |
    DataDBDemos.pas | Save |
    Briefcase.dpr | Save
  4. With the Components Tab visible and selected on the DataModuleDBDemos "form," select the ADO tab on the VCL palette. Double click on the ADOConnection icon to add an instance to the form. Configure ADOConnection1 with these properties:
    Name: ADOConnection
    Login Prompt: False
  5. Double click on entry area for ConnectionString property. Select the Use Data Link File radio button and in the Select Data Link File dialog, select the file:
    Look in Data Links and then choose DBDEMOS.udl.
    (C:\Program Files\Common Files\System\OLE DB\Data Links\DBDemos.UDL).

  6. Select the Open button (above) and the OK button (below) to close the Connection String dialog:

  7. With the Components Tab visible and selected on the DataModuleDBDemos "form," select the ADO tab on the VCL palette. Double click on the ADODataSet icon to add an instance to the form. Configure ADODataSet1 with these properties:
    Name: ADODataSetEmployee
    Connection: ADOConnection
    CommandType: cmdTable
    CommandText: employee
    CursorType: ctStatic
    LockType: ltBatchOptimistic
  8. For reasons that are not understood, setting the CommandText property of ADODataSetEmployee (in Step 7) changed the ConnectionString of ADOConnection. To allow the application to be run multiple times without a run-time error, select ADOConnection and repeat Steps 5 and 6 to re-specify the UDL file as the Connection String.
  9. From the Data Access VCL palette tab, double click on the DataSource icon to add an instance to the form. Configure DataSource1 with the following properties:
    Name: DataSourceEmployee
    DataSet: ADODataSetEmployee
  10. At this point the DataModuleDBDemos should look something like this:

  11. Select the DataDiagram tab on DataModuleDBDemos and drag the icons from the tree view to the Data Diagram area: ADOConnection, ADODataSetEmployee, DataSourceEmployee.
    Resize and rearrange the boxes to form a diagram like the following:

  12. From View Unit (Ctrl-F12), select ScreenBriefcase and the OK button.
  13. In the Implementation section of ScreenBriefcase, add the following USES:
    (This statement allows the items in DataModuleDBDemos to be used via the IDE.)

    14. USES DataDBDemos;
  14. Select Toggle Form/Unit (F12).
  15. From the Data Controls VCL tab, double click on DBGrid to add a new DBGrid1 to FormBriefcase. Set these properties:
    Name: DBGridEmployee
    DataSource: DataModuleDBDemos.DataSourceEmployee
    Top: 40
    Left: 8
    Width: 500
    Height: 310
  16. Note: Normally at this point, I would go back to DataModuleDBDemos and set Active to True on ADODataSetEmployee to see the DBGrid populated with data.

    For reasons I don't understand (possibly a bug?), setting Active to True will for now result in a run-time error, so "live" data will not be used in this example.

    17. NOTE: Setting this Active property to TRUE, changes the definition of the ConnectionString of the ADOConnection. Instead of being defined via the Use Data Link File Connection String selection (which was specified above in Step 6), the information is now stored in the Use Connection String setting.

    Mark Edington's (Borland) comments from UseNet Post:

    The [Briefcase] demo is designed in such a way that it will only work correctly if the dataset is closed at design time.  This is because it needs to make a determination at runtime of where to get the data.

    The ConnectionString getting expanded from the UDL filename to a complete connection string is something that the ADO connection component does.  If you close the connection it will revert to the UDL filename.  However, if you save the form and reload it while the connection is open, the reference to the UDL filename is lost completely.  The VCL needs to preserve the original connection string (with the UDL filename) in this case, but it doesn't currently do that. That's something I plan to fix for the next release.

  17. If you were to run the program at this point, no data would appear in the DBGrid. Somehow this is caused by the fix in Step 8. This may explain why the Borland example assigns a value to ADOConnection ConnectionString in the FormCreate method. Since a Data Module is being used in this example, this assignment must be added to the DataModuleCreate method instead of FormCreate.

    Select View Unit (Ctrl-F12) | DataDBDemos | OK. Double-click on any open area of the Components tab, and enter this DataModuleCreate code:
    18. procedure TDataModuleDBDemos.DataModuleCreate(Sender: TObject);
    begin
      ADODataSetEmployee.Open
    end;

    The original Borland example defined the ConnectionString in the FormCreate. That could be also added to the DataModuleCreate method above, but the statement doesn't seem to be necessary.

    ADOConnection.ConnectionString :=
         'FILE NAME=' +
         DataLinkDir + // Gets Data Link Dir from Registry
         '\DBDEMOS.UDL';

    The DataLinkDir function above (from the ADODB unit) returns the default location of a directory that contains UDL files (usually C:\Program Files\Common Files\Systems\OLE DB\Data Links).

    Mark Edington's (Borland) comments from UseNet Post:

    The value returned by the DataLinkDir function will be different than the path in the TADOConnection component if Delphi is installed on a machine with a different path to "c:\program files".  This is the case on some international versions of Windows.

  18. Select View Unit (Ctrl-F12) | ScreenBriefCase | OK
  19. Add this definition in the Private section of TFormBriefcase:

    20. DataFileName: STRING;
  20. Select Toggle Form / Unit (F12).
  21. From the Standard VCL tab, double click on the CheckBox icon to add CheckBox1 to FormBriefcase. Set these properties:
    Top: 11
    Left: 24
    Name: CheckBoxConnected
    Caption: Connected
  22. Double click on an open area of FormBriefcase, above DBGridEmployee. Complete the code for FormCreate:
    23. procedure TFormBriefcase.FormCreate(Sender: TObject);
    begin
      // Save name for private access only
      DataFileName := ExtractFilePath(Paramstr(0)) + 'EMPLOYEE.ADTG';

      // If a persistent datafile exists, assume we exited in a
      // disconnected(offline) state and load the data from the file.
      IF   FileExists(DataFileName)
      THEN DataModuleDBDemos.ADODataSetEmployee.LoadFromFile(DataFileName)
      ELSE BEGIN
        // Otherwise establish the connection and get data from the database
        CheckBoxConnected.Checked := True;
        DataModuleDBDemos.ADODataSetEmployee.Open
      END
    end;
  23. Select Save All | Project | Build BriefCase (Ctrl-F9) | Run (F9)
    There is a problem. Select OK on the following dialog and close the running application.

    24.
  24. The problem is that FormCreate is using components in DataModuleDBDemos, but the initialization of the Data Module occurs after FormBriefcase. The order of initialization must be changed in the project (.DPR) file.

    Select View Unit (Ctrl-F12) | Briefcase | OK
  25. Inside the BEGIN .. END block, change the order of the CreateForm statements. Edit the file so the statements appear in this order:

    26. Application.CreateForm(TDataModuleDBDemos, DataModuleDBDemos);
    Application.CreateForm(TFormBriefcase, FormBriefcase);
  26. Verify the initialization order problem is fixed.
    Select Save All | Project | Build BriefCase (Ctrl-F9) | Run (F9)
    You should see no error when the program is run. Close the running application.
  27. Select View Unit (Ctrl-F12) | ScreenBriefcase| OK
  28. Add the following private methods to TFormBriefCase:
    29. Private
    . . .
      DataFileName: STRING;
      PROCEDURE SaveData;
      PROCEDURE UpdateData;
    . . .
    USES
      ADODB, // pfADTG
      DataDBDemos; // DataModuleDBDemos
    . . .
    PROCEDURE TFormBriefCase.SaveData;
    BEGIN
      DataModuleDBDemos.ADODataSetEmployee.SaveToFile(DataFileName, pfADTG);
    END {SaveData};

    PROCEDURE TFormBriefCase.UpdateData;
    BEGIN
      // Connect to the database and send the pending updates
      CheckBoxConnected.Checked := TRUE;
      DataModuleDBDemos.ADODataSetEmployee.UpdateBatch;
      DeleteFile(DataFileName)
    END; {UpdateData}
  29. With FormBriefcase selected in the Object Inspector, select the Events tab and double click on the OnCloseQuery entry area.
  30. Add the following OnCloseQuery method handler:
    31. procedure TFormBriefcase.FormCloseQuery(Sender: TObject;
      var CanClose: Boolean);
    begin
      IF DataModuleDBDemos.ADODataSetEmployee.Active
      THEN BEGIN

        TRY
          // When closing, update the database if connected or
          // save it to disk if not.
          IF   DataModuleDBDemos.ADOConnection.Connected
          THEN UpdateData
          ELSE SaveData
        EXCEPT
          ON E: Exception
          DO BEGIN
            Application.HandleException(Self);
            CanClose :=
              MessageDlg('Data not saved/updated. Exit anyway?',
              mtConfirmation, mbYesNoCancel, 0) = mrYes;
          END
        END

      END
    END;
  31. Select Toggle Form / Unit (F12)
  32. Double-click on the CheckBoxConnected component, and add the following OnClick handler:
    33. procedure TFormBriefcase.CheckBoxConnectedClick(Sender: TObject);
    begin
      // Toggle the connection's state
      IF CheckBoxConnected.Checked
      THEN BEGIN
        DataModuleDBDemos.ADOConnection.Open;
        DataModuleDBDemos.ADODataSetEmployee.Connection :=
          DataModuleDBDemos.ADOConnection
      END
      ELSE BEGIN
        // Note here you must clear the connection property of
        // the dataset before closing the connection. Otherwise
        // the dataset will close with the connection.
        DataModuleDBDemos.ADODataSetEmployee.Connection := NIL;
        DataModuleDBDemos.ADOConnection.Close
      END
    end;
  33. Select Toggle Form/Unit (F12)
  34. With no visual component selected, double click on the Button icon on the Standard VCL tab three times to add Button1, Button2 and Button3 to FormBriefcase. Move the buttons to be roughly equally spaced, and in a horizontal line above DBGridEmployee.
  35. Select Button1 and set these properties:
    Name: ButtonUpdateServer
    Caption: Update Server
    Left: 150
    Top: 8
    Width: 100
  36. Double-click on ButtonUpdateServer to set this OnClick method:
    37. procedure TFormBriefcase.ButtonUpdateServerClick(Sender: TObject);
    begin
      UpdateData
    end;
  37. Select Toggle Form/Unit (F12)
  38. Select Button2 and set these properties:
    Name: ButtonRefreshData
    Caption: Refresh Data
    Left: 275
    Top: 8
    Width: 100
  39. Double-click on ButtonRefreshData to set this OnClick method:
    40. procedure TFormBriefcase.ButtonRefreshDataClick(Sender: TObject);
    begin
      // Close and reopen the dataset to refresh the data.
      // Note that in this demo there is no checking for
      // pending updates so they are lost if you click the
      // refresh data button before clicking the Update
      // database button.
      CheckBoxConnected.Checked := True;
      WITH DataModuleDBDemos.ADODataSetEmployee DO
      BEGIN
        Close;
        CommandType := cmdTable;
        CommandText := 'Employee';
        Open
      END
    end;
  40. Select Toggle Form/Unit (F12)
  41. Select Button3 and set these properties:
    Name: ButtonSaveToDisk
    Caption: Save to Disk
    Left: 400
    Top: 8
    Width: 100
  42. Double-click on ButtonSaveToDisk to set this OnClick method:
    43. procedure TFormBriefcase.ButtonSaveToDiskClick(Sender: TObject);
    begin
      SaveData
    end;
  43. Select Save All | Project | Build BriefCase (Ctrl-F9) | File | Close All | File | Exit

G.  Run the Briefcase Program
  1. From Windows Explorer, double click on the Briefcase.EXE icon.

  2. Uncheck the Connected check box.
  3. For EmpNo = 2, change the LastName "Nelson" to "XXXXXX".

  4. Press the Save to Disk button.
  5. Exit the program. From Windows Explorer, verify the file EMPLOYEE.ADTG was created (3037 bytes).
  6. Start the Briefcase program again. The LastName is XXXXXX, as shown above, and the Connected checkbox is not checked.
  7. Press the Refresh Data button and see the name "Nelson" restored. The Connected checkbox is checked.
  8. Uncheck the Connected check box.
  9. For EmpNo = 2, change the LastName "Nelson" to "YYYYYYYY".
  10. Exit the program.
  11. Start the Briefcase program again. The LastName is YYYYYYYY, as shown above, and the Connected checkbox is not checked.
  12. Press the Update Server button. The Connected checkbox becomes checked.
  13. Exit the program. From Windows Explorer, verify the file EMPLOYEE.ADTG was deleted.
  14. Restart the program. Change the Name YYYYYYYY back to Nelson. Exit the program.

Also see:
  The Briefcase Model:  When Your Application Must Travel Well, Delphi Informant, April 1999
Using the Briefcase Model for Laptop Databases, Delphi Developer, April 1999

Links Verified 7 Feb 2000
Updated 18 Feb 2002


since 7 Feb 2000