ActiveX - Opening Databases

Opening a database and then drawing a Daisy Chart was described in Drawing a Daisy Chart.

The actual opening of databases and the various techniques that can be used are described in detail here.

Three ways of opening databases and loading the data into the internal database of Daisy :-

• LoadDatabase ... methods can be used to load a database directly.

• MenuOpen ... methods can be called to bring up dialogs which are then used to select the database to be loaded.

• Special Methods such as AddDatabaseFields and AddDatabaseRecords can be used to enter a database one record at a time.

LoadDatabase ... Methods

The LoadDatabase ... methods are designed for loading databases, which have either been chosen or generated by the calling program.

Several separate methods each of which handles a specific database type can be used :-

• LoadDatabaseADO loads a database through a Microsoft ADO connection.
• LoadDatabaseCSV loads a CSV file.
• LoadDatabaseExcel loads an Excel worksheet.
• LoadDatabaseFileDirectory loads a directory listing as a database.
• LoadDatabaseWebSiteLog loads a log file for a web site.

These methods are very comprehensive with several parameters, which determine how the database will be loaded.

The code below shows the loading of an Excel worksheet.

  Dim wrkError as String
  Dim wrkPath as String
  ...
  wrkPath = ...
  if (Daisy.LoadDatabaseExcel(wrkPath, "Sheet1", wrkError, _
          dsxXLField9Data11, 2, 25, 1000) = False) Then
      Daisy.MessageError wrkError
      Exit Sub
  End If
  ...

Note :-

1. The database path, wrkPath, must be set in some way.

2. The database path, worksheet name, 'Sheet1', and the error message arguments must always be present in the call.

3. dsxXLField9Data11 means that the field names are in row 9, with the data starting in 11.

4. The next argument, 2, means that at least two values must contain data for the row to be loaded.

5. 25 and 1000 determine that the maximum width of each value is 25 characters and only 1000 rows will be loaded.

6. MessageError is used to display an error message in a Daisy style and format.

MenuOpen ... Methods

The MenuOpen ... methods directly action the various methods that call dialogs to select and load databases.

Several separate methods each of which handles a specific database type can be used :-

• MenuOpenADO selects and opens a database through a Microsoft ADO connection.
• MenuOpenCSV loads a CSV file.
• MenuOpenExcel loads an Excel worksheet.
• MenuOpenFileDirectory loads a directory listing as a database.
• MenuOpenWebSiteLog loads a Web Site Log file.

The code below shows the loading of a CSV file.

  ...
  Daisy.DirectoryDatabase = ...
  Daisy.MenuOpenCSV
  if (Daisy.TestDatabaseOpen = False) Then
      Daisy.MessageInfo "No database opened!"
      Exit Sub
  End If
  ...

Note :-

1. DirectoryDatabase is set in the program, so that it knows where databases are stored.

2. MenuOpenCSV opens the database and TestDatabaseOpen tests whether one has been opened.

In addition two commands, MenuOpenExamples and MenuOpenShowcase can be used to open the examples and the showcase respectively.

Special Methods

In some applications, it may be necessary to enter the database into Daisy in a much more direct manner.

Suppose you have a complex scientific instrument, which generates a series of results and the set are to be analysed. The results might be held as a two-dimensional array in the controlling program.

These could be saved to disc and then Daisy could load the file, but there is a more direct and perhaps in many cases simpler way, using direct methods.

The code below shows the loading of an array, Results, of 1000 records into Daisy.

  Dim Names(1 to 10) As String
  Dim Values(1 to 10) as String
  Dim wrkError as String
  Dim kk as Long
  Dim kkk as Long
  ...
  If (Daisy.LoadDatabaseStart(wrkError) = False) Then
      Daisy.Message wrkError
      Exit Sub
  End If
  Names(1) = "Date"
  Names(2) = "Time"
  Names(3) = "Value"
  Names(4) = "Comment"
  Names(5) = "X1"
  Names(6) = "Y1"
  If (Daisy.AddDatabaseFields(6, Names(), wrkError) = False) Then
      Daisy.MessageError wrkError
      Exit Sub
  End If
  For kk = 1 to 1000
      For kkk = 1 to 6
          Values(kkk) = Results(kk, kkk)
      Next kkk    
      If (Daisy.AddDatabaseRecord(Values(), wrkError = False) Then
          Daisy.Message wrkError
          Exit Sub
      End If    
  Next kk
  If (Daisy.LoadDatabaseFinish("Results", wrkError) = False) Then
      Daisy.Message wrkError
      Exit Sub
  End If
  Daisy.ShowDaisy
  ...

Note :-

1. LoadDatabaseStart is used to start the loading process and must be called.

2. AddDatabaseFields adds the field definitions to the program. It can only be called with the database empty.

3. Each of the individual records is then added to the program using AddDatabaseRecord.

4. The Values array that is used with AddDatabaseRecord must be a string. Convert the values if required.

5. LoadDatabaseFinish is then used to tell Daisy, the name of the database and expose it for analysis.

6. Finally, ShowDaisy is used to expose Daisy.

7. You don't have to load the database with Daisy hidden, but in many cases it is a better method, as it enables progress messages to be shown in the callingprogram.