CASE STUDY

   

You've been asked to prepare a database named ShortStay, which will contain information about hospital patients who spend only a brief period of time as inpatients. The database needs to maintain information about the patients, the medical and surgical procedures used for each, the cost of supplies, the hospital staff involved, and so on.

Having set up the ShortStay database using DAO, at a later point you find that you want to create a table that contains a field with the names of medical procedures and one that contains a procedure's ID, which will be the table's primary key. You'll use the table subsequently as a lookup table: You'll store a procedure's ID in the main data table and display its name when you bring a record back to Excel from the database.

You begin by inserting a new module into your workbook and setting references to these libraries:

  • Microsoft ActiveX Data Objects 2.x Library

  • Microsoft ADO Ext. 2.x for DDL and Security

  • The standard references to VBA, the Excel object library, OLE Automation, and the Office object library

NOTE

In both cases, 2.x refers to the highest level version on your system. (If you're running Office 97, you probably have version 2.1; if you're running Office 2003, you probably have version 2.7.)


You enter the following code into the module:

 Sub NewShortStayTable() Dim cnConnectToShortStay As ADODB.Connection Dim SourceName As String Dim rsProcs As ADODB.Recordset Dim i As Integer Dim LastProc As Long Dim catDatabaseFile As ADOX.Catalog Dim tdfProcs As ADOX.Table 

There are two new items in this list of declarations: an ADOX catalog and an ADOX table. Both belong to the ADO extensions mentioned at the beginning of the chapter. A catalog, as used here, is much the same as a database. ADO uses it as a container for the objects you normally find in a database: tables, queries, and so on. The ADOX table is no different from the tables that have been discussed and used so far in this book.

 Set cnConnectToShortStay = New ADODB.Connection Set catDatabaseFile = New ADOX.Catalog Set tdfProcs = New ADOX.Table 

Three object variables are set: a connection variable that will be pointed at the ShortStay database, a catalog variable that will represent the database itself, and a table variable that will represent the new table to be created.

 SourceName = ThisWorkbook.Path & "\ShortStay.mdb" cnConnectToShortStay.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceName 

The SourceName variable is used to store the path to and name of the database. The connection specifies the provider and the path and name, just as shown in Chapter 8.

 Set catDatabaseFile.ActiveConnection = cnConnectToShortStay 

The code connects to the database by way of the connection.

 tdfProcs.Name = "Procedures" 

The new table is named Procedures. Then two new fields are appended to the table. Notice the difference from DAO, where a new field is first established and then appended to the table's collection of fields. In ADO, you name the field, specify its type, and append it to the table in one step, as shown in the next two statements:

 tdfProcs.Columns.Append "ProcedureID", adInteger tdfProcs.Columns.Append "ProcedureName", adVarWChar, 50 

The first statement appends a new field named ProcedureID to the Procedures table. The second statement appends a new field named ProcedureName. The adVarWChar type results in a Text field in Access. The final argument, 50, specifies a maximum length of 50 characters for the field.

 tdfProcs.Keys.Append "PrimaryKey", adKeyPrimary, "ProcedureID" 

A primary key is established for the table. Recall from Chapter 9, "Managing Database Objects," that a table can have more than one index. If the table has a primary key, that's just one particular index, distinguished by the facts that it doesn't allow duplicate values, and that only one index at a time can be designated as the table's primary key.

The primary key is established here by simply appending a key to the table's collection of keys (ADO uses the term keys whereas Access uses the term indexes; they're largely synonymous). The key is named PrimaryKey, its type is adKeyPrimary (other options are adKeyUnique to create a unique index that is not the primary key, and adKeyForeign to link to another table's primary key). The statement specifies ProcedureID as the key's basis.

 catDatabaseFile.Tables.Append tdfProcs 

The newly created table is appended to the catalog's collection of tables, and it's time to put records in the table. Your code begins by counting the number of records on the worksheet (see Figure 10.3).

 LastProc = ThisWorkbook.Worksheets("Procedures") _ .Cells(65536, 1).End(xlUp).Row 

Figure 10.3. If the procedures will be used later in a dropdown, it's useful to begin by sorting them alphabetically.

graphics/10fig03.jpg


Then establish and open a new recordset based on the Procedures table.

 Set rsProcs = New ADODB.Recordset rsProcs.Open "Procedures", cnConnectToShortStay, _ adOpenStatic, adLockOptimistic, adCmdTable 

Establish a With block and run a For-Next loop to populate the Procedures table. During each cycle through the loop, its counter i is used to provide a unique value to the primary key field, and the procedure name is placed in its field.

 With rsProcs   For i = 2 To LastProc     .AddNew     .Fields("ProcedureID") = i - 1     .Fields("ProcedureName") = ThisWorkbook.Sheets("Procedures").Cells(i, 1)     .Update   Next i End With 

Clean up by setting the object variables to Nothing (thus releasing the variables) and closing the connection.

 Set tdfProcs = Nothing Set catDatabaseFile = Nothing cnConnectToShortStay.Close Set cnConnectToShortStay = Nothing End Sub 

You now have a new table in the database, with a primary key and a text field, as shown in Figure 10.4.

Figure 10.4. It can be convenient, but it's not descriptive to name a table's primary key as PrimaryKey.

graphics/10fig04.jpg


Here's the full subroutine:

 Sub NewShortStayTable() Dim cnConnectToShortStay As ADODB.Connection Dim catDatabaseFile As ADOX.Catalog Dim tdfProcs As ADOX.Table Dim SourceName As String Dim rsProcs As ADODB.Recordset Dim i As Integer Dim LastProc As Long Set cnConnectToShortStay = New ADODB.Connection Set catDatabaseFile = New ADOX.Catalog Set tdfProcs = New ADOX.Table SourceName = ThisWorkbook.Path & "\ShortStay.mdb" cnConnectToShortStay.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceName Set catDatabaseFile.ActiveConnection = cnConnectToShortStay tdfProcs.Name = "Procedures" tdfProcs.Columns.Append "ProcedureID", adInteger tdfProcs.Columns.Append "ProcedureName", adVarWChar, 50 tdfProcs.Keys.Append "PrimaryKey", adKeyPrimary, "ProcedureID" catDatabaseFile.Tables.Append tdfProcs Set rsProcs = New ADODB.Recordset LastProc = ThisWorkbook.Worksheets("Procedures") _ .Cells(65536, 1).End(xlUp).Row rsProcs.Open "Procedures", cnConnectToShortStay, _ adOpenForwardOnly, adLockOptimistic With rsProcs     For i = 2 To LastProc         .AddNew         .Fields("ProcedureID") = i - 1         .Fields("ProcedureName") = ThisWorkbook _         .Sheets("Procedures").Cells(i, 1)         .Update     Next i End With Set tdfProcs = Nothing Set catDatabaseFile = Nothing cnConnectToShortStay.Close Set cnConnectToShortStay = Nothing End Sub 



Managing Data with Microsoft Excel
Managing Data with Microsoft Excel
ISBN: 789731002
EAN: N/A
Year: 2003
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net