Data Shaping

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Easy Access

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Shaping ADO Queries with Access Subdatasheets

By Garry Robinson

Over the last year or so, I've been converting Access 97 databases to Access 2000, and developing new Access 2000 databases. What I still struggle to figure out is what there was in the upgrade for users. Most of the work has gone into programming features, such as Access/SQL Server integration and turning VBA into VB. There is one user-oriented feature, however, that I've found really useful: subdatasheets. These extensions allow you to show related records in linked tables and queries in up to five levels of detail.

This article shows how easy it is to set up a subdatasheet, and offers a wizard that will convert your subdatasheet into a shaped recordset. The wizard will allow you to convert your subdatasheets to hierarchical recordsets by writing VB, using a new data provider supplied with ADO (ActiveX Data Objects), called shaped datasets. The generated code will allow you to display the same hierarchical information in the hierarchical flexgrid component supplied with VB 6.

What Are Subdatasheets?

FIGURE 1 shows the Customers table in the Northwind 2000 database, and the Access 2000 feature I've been talking about. It's the little plus sign next to the record selector on the left-hand side. When you click on the plus sign - as I've done on customer ANTON - the orders placed by this customer are displayed. I've also clicked on the plus sign in the Orders table to display all the products (from the Order Details table) that were purchased for that order. This magical drill down capability is made available by subdatasheets.

FIGURE 1: The Customers table showing Orders, and then Order Details, as two subdatasheets.

The best way to visualize what's going on behind a subdatasheet is via the Relationships window. FIGURE 2 illustrates the relationships relevant to FIGURE 1. As you can see, there are many Orders for each Customer and many Products purchased for each Order. The subdatasheets allow you to see the records for each level of detail that reflects this hierarchy of data.

FIGURE 2: The Access Relationships window.

So how are subdatasheets added to the database? Access automatically establishes subdatasheet properties for any primary table in a one-to-many relationship. To create a subdatasheet manually, however, open a table or query in normal mode and select Insert | Subdatasheet. FIGURE 3 shows a lookup table, called tlkpProducts, where I've added the subdatasheet relationship using Insert | Subdatasheet. To create the join, you must select the related table and the field(s) on which to base the relationship. You can do more with the subdatasheets by specifying whether you want the subdatasheet to expand automatically under the Format menu. There are also some formatting options that you can set with your mouse, such as expansion width, row size, and fonts.

FIGURE 3: Open the subdatasheet properties of a table using the Insert | Subdatasheet menu options.

Another powerful feature of subdatasheets is that you can apply filters to them. This can make for some interesting data exploration, but back to the exercise at hand ...

Building Shaped Recordsets

The whole purpose of the software for this article (available for download; see end of article for details) is to help highlight the similarities between the hierarchical, shaped recordsets, and the subdatasheets. FIGURE 4 shows the form that is associated with this article.

FIGURE 4: The demonstration form for this article.

Build a Table List Using the Catalog

To generate a shaped recordset, the software must first provide the user a list of valid tables and queries. To do this, I used the ADOX Catalog object, which you will need to reference in your VB project (see FIGURE 5).

FIGURE 5: References used in this Access project.

The code shown in FIGURE 6 builds a string to hold the list of tables and views the user can employ. Because we're using ADO, a table is called a table, but a select query is called a view. This is to comply with SQL Server naming conventions. An Access select query resolves to a SQL SELECT statement. Action queries resolve to the SQL statements INSERT, UPDATE, and DELETE, and are stored in a separate part of the database catalog. Note that the views are also listed in the Tables catalog and must be filtered out.

'Set a reference for Microsoft ADO Ext. 2.1
'for DDL and security. 
Dim cboStr As String, cancel As Integer
Dim objT As Table, objV As Object
 
Set adoxCat = New ADOX.Catalog
adoxCat.ActiveConnection = strConnect
cboStr = "Table/View Name;Type;" 
 
For Each objT In adoxCat.Tables
  If Left(objT.Name, 4) = "mSys" Or _
     Left(objT.Name, 1) = "~" Then
    GoTo NotATable
  End If
 
  ' Before adding the table to the ComboBox string, make
  ' sure the table isn't a view as we'll add these later. 
  If objT.Type <> "VIEW" Then
    cboStr = cboStr & objT.Name & ";table;" 
  End If
 
NotATable: 
  Next objT

FIGURE 6: Building a string to hold the list of tables and views for the user.

Once I've finished with the tables, it's a simple process of looping through the views catalog (select queries) to add the views to the ComboBox:

'Loop through the views to add the queries. 
For EachobjV In adoxCat.Views
  cboStr = cboStr & objV.Name & ";View;" 
Next objV
 
cboT_V.RowSource = cboStr

(In April 1999, I wrote my first article for Microsoft Office & VBA Developer, titled "Visual Data Mining." In the article, I demonstrated how to use DAO (Data Access Objects) to list tables and queries. Note that the equivalent ADO catalog code, shown in FIGURE 6, is much simpler.)

Open the Table or View

To display the table or view, we must first establish whether the user wants the primary data source to be a table or a view. This is necessary because Access uses different methods to open tables and queries. To do this, we refer to the second column in the ComboBox, where we store the data source type.

One of the observations made about subdatasheets is that it's unlikely that you would expose users to raw table views. Although that is generally true, I have side-stepped this issue by opening the primary table in read-only view:

If cboT_V.Column(1) = "Table" Then
  DoCmd.OpenTable Me!cboT_V, , acReadOnly
Else
  DoCmd.OpenQuery Me!cboT_V, , acReadOnly
End If

To display the subdatasheet relationship, we open the Insert Subdatasheet menu by issuing the SendKeys command (note that % stands for the [Alt] key):

SendKeys "%IS" 

Turn the Subdatasheet into a Shaped Recordset

After some experimentation, you'll have a combination of tables and views that will have subdatasheets properties that you will wish to turn into a shaped hierarchical recordset. To start this process, the code must first establish the subdatasheet properties from the table that you choose. Surprisingly, for a brand new Access 2000 feature, this information is stored under the properties that can only be found using DAO. I have included functions in the demonstration database that will return these properties, if they exist. They're shown in FIGURE 7.

topSource = Me!cboT_V
If InStr(topSource, " ") Then
  topSource = "[" & topSource & "]" 
End If
 
sourceType = Me!cboT_V.Column(1) 
 
If sourceType = "Table" Then
  subDS = TableProperty_FX("SubdatasheetName", topSource) 
  LinkCF = TableProperty_FX("LinkChildFields", topSource) 
  LinkMF = TableProperty_FX("LinkMasterFields", topSource) 
Else
  subDS = QueryProperty_FX("SubdatasheetName", topSource) 
  LinkCF = QueryProperty_FX("LinkChildFields", topSource) 
  LinkMF = QueryProperty_FX("LinkMasterFields", topSource) 
End If

FIGURE 7: The code must first establish the subdatasheet properties from the table you choose.

The code now starts using ADO to first prepare a recordset. The first requirement of this process is to establish a connection string to the current database. This is easily achieved in Access as CurrentProject.Connection returns this string:

Set rst = New ADODB.Recordset
strConnect = CurrentProject.Connection

If you open the debug window and display strConnect, you'll see that the connection string is a complex definition. (I must confess; I find connection strings hard to establish for other databases, because I've been spoiled by the simplicity of linked tables in Access. This confusion is compounded by the lack of detail in the Access 2000 help for ADO.) Part of this very long connection string is illustrated in the following:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; 
  Data Source=D:\A_gr-fx\articles\Informant Data Shaping
  \shapes.mdb;Mode=Share Deny None;...OLEDB:SFP=False

Finally, we come to the point where we have to let the ADO recordset know where the data is coming from. When working with normal ADO recordsets, the Provider and the Data Source are the same. But the power of the ADO approach is that an alternative Provider can be used to handle more unusual types of information, such as these hierarchical datasets. In this case, the Provider we want to use is MSDataShape, as follows:

rst.ActiveConnection = _
  "Provider=MSDataShape;Data " & strConnect

Note the little trick with the connection string: I simply added the Provider and concatenated ;Data in front of the previous Provider clause that was established by the CurrentProject.Connection property (discussed earlier). Thus, the new connection string becomes:

Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0; 
  User ID=Admin;Data Source=D:\A_gr-fx\articles
  \Informant Data Shaping\shapes.mdb;Mode=Share Deny None; 
  ...;Jet OLEDB:SFP=False

This brings us to building a shaped recordset using code. The best way to illustrate this is with the syntax for the Northwind Orders and Order Details tables as follows:

SHAPE {SELECT * FROM Orders}
APPEND ({SELECT * FROM [Order Details] } AS rsDemo
RELATE OrderID TO OrderID) 

This shaped query will return a hierarchical recordset that will link the Orders and [Order Details] tables using the common OrderID field. When seen in this format, the query doesn't look hard to set up manually. Imagine this query, however, with two complex SELECT statements. Then add additional shaping syntax, such as bracketing and the RELATE clause, and include the fact that you cannot visualize this query in the normal query design tool. Then you'll understand why a builder wizard will be useful.

So how does this wizard build this shaped recordset? Initially, the SubdatasheetName property has to be stripped of its table or query prefix. Also, the software must handle table or query names with internal spaces by surrounding the table in square brackets. The Order Details table is stored in the SubdatasheetName property of the Orders table as "table order details." This code turns that into "[Order Details]":

subSourceType = Left(subDS, 6) 
subDS = M(subDS, 7) 
If InStr(subDS, " ") Then
  subDS = "[" & subDS & "]" 
End If

Now that we've established the primary table and its related table, the following string concatenation will build the required shaped query. LinkCF and LinkMF contain values from the LinkChildFields and LinkMasterFields properties from the subdatasheet definition (these define the hierarchy):

sqlstr = "SHAPE {" & _
  "SELECT * " & _
  "FROM " & topSource & "} APPEND ({SELECT * " & _
  "FROM " & subDS & " } AS rsDemo RELATE " & LinkCF & _
  " TO " & LinkMF & ")" 

Finally, we point the new recordset at the hierarchical flexgrid object and close the Recordset object, because it's no longer needed by the display control. This will display the hierarchical data as shown on the top right in FIGURE 4:

Set msHFlex.DataSource = rst
 
rst.Close
Set rst = Nothing

Get a Connection String

One of the aims of this process is to automatically produce code that's useable on remote databases, such as SQL Server. This means the CurrentProject connection string used for the Access database must be changed to suit an external database. In this case, I assume that the tables in the current Access project will be the same as in the remote project. This simulation is achieved by using linked tables to join to the remote database.

Now we must find a way to build a connection string for the remote database. I do this using the MSOLDB library, which has a tool to return a connection string. This approach generates some readable properties for the connection, so it is worth reviewing. An alternative to this approach would be the use of file DSNs:

'Get MSOLDB Service Component 1.0 Object Type Library. 
Dim objLinks As MSDASC.DataLinks
Dim objCon As ADODB.Connection
 
On Error GoTo FileOpenError
 
'Initiate the Data Link Properties dialog
'to select a connection. 
Set objLinks = New MSDASC.DataLinks
Set objCon = objLinks.PromptNew

This code brings up the tabbed dialog box shown in FIGURE 8. For this example, a SQL Server Provider for the Northwind SQL is selected.

FIGURE 8: The Connection page of the Data Link Properties dialog box. In this case, a SQL Server database is being selected.

The dialog box returns a Connection object back to the subroutine. It's necessary to modify the Connection object to apply the data shape provider, so the code must loop through all the connection properties to generate the required VBA code. The loop in the code is complicated by having to surround the text properties in quotes, and avoiding properties that have no values (see FIGURE 9).

With objCon.Properties
  For i = 0 To .Count - 1
    If Len(.Item(i).Value) >= 1 Then
      cnStr = cnStr & vbCrLf & "cn.Properties(""" & _
                .Item(i).Name &""").Value = " 
      If.Item(i).Type = adBSTR Then
        ' Text property. 
        cnStr = _
          cnStr & quoteStr &.Item(i).Value & quoteStr
      Else
        cnStr = cnStr & .Item(i).Value
      End If
    End If
  Next i
End With

FIGURE 9: Looping through the connection properties.

This produces VB-ready properties for the Connection object, as follows:

cn.Properties("Persist Security Info").Value = False
cn.Properties("User ID").Value = "sa" 
cn.Properties("Initial Catalog").Value = "NorthwindCS" 

Building the Source Code

The whole purpose of this code was to generate VB to generate a hierarchical recordset. The code in FIGURE 10 contains some of the connection code that was generated.

Dim cn As New ADODB.Connection
cn.provider = "MSDataShape" 
cn.Properties("Data Provider").Value = "SQLOLEDB" 
cn.Properties("Persist Security Info").Value = False
cn.Properties("User ID").Value = "sa" 
cn.Properties("Initial Catalog").Value = "NorthwindCS" 
cn.open
rst.Source = "SHAPE {SELECT * FROM Orders} APPEND " & _
  "({SELECT * FROM [Order Details] } AS rsDemo " & _
  "RELATE OrderID TO OrderID)" 
rst.ActiveConnection = cn
rst.Open
Set msHFlex.DataSource = rst
rst.Close
Set rst = Nothing

FIGURE 10: This code was automatically built for a SQL Server connection.

This code-building option is another example of concatenating strings with subdatasheet-supplied properties, such as the primary table, the linked table, and the fields that link the two tables. The resulting string, named txtVB, is lengthy and contains carriage returns and line feeds. In this case, the connection string, and the code to display the recordset, are placed together:

txtVB = txtConString & vbCrLf & _
  "cn.open" & vbCrLf & _
  "rst.Source = """ & sqlstr & "" & vbCrLf
  txtVB = txtVB & "rst.ActiveConnection = cn " & vbCrLf
 
txtVB = txtVB & "rst.Open" & vbCrLf & _
  "Set msHFlex.DataSource = rst" & vbCrLf & _
  "rst.Close" & vbCrLf & _
  "Set rst = Nothing" 

Testing the Code

To conclude the exercise, copy the new code from the text box, and place it under the final command button. This will display the shaped recordset in the hierarchical flexgrid control, which is what this wizard was designed to do.

What have we learned? First is that subdatasheets are worth investigating, because they can add a lot of power to an application in very little time. If nothing else, use them when you're initially testing your data model with your users. They're a lot faster to put together than forms and subforms.

The next lesson is that there is great synergy between subdatasheets and hierarchical records. You can well imagine that the subdatasheet technology is in some small way using the shaped recordsets. I recommend using the subdatasheets to test your hierarchical queries before you start developing shaped recordsets.

Finally, rather then spending a lot of time building shaped recordsets from scratch, take this wizard and use it to build them automatically. It's likely the code will need some modification to suit your purposes, but at least you'll only have to make that modification once. With a bit of lateral thinking, this wizard could easily be adapted for many other ADO tasks.

The Hidden Treat

The implementation of subdatasheets in forms is poorly documented. This is a pity, since they could have a considerable effect on how your Access applications work. What this change means to the users is as follows.

If you have an Access form with a subform, you generally would display the form as illustrated in FIGURE 11. This sort of form has a major drawback in that you can only see one primary record at a time. The solution in Access 97 was to switch to datasheet mode to see the primary records in a spreadsheet-type view. Unfortunately, you could then only see the primary record, and would have to switch back to form view to see the associated detail records.

FIGURE 11: The classic form/subform view in Access 97.

In FIGURE 12, the Access 2000 form has been switched to datasheet mode. This has all the convenience of the subdatasheets in queries and tables, combined with all the program logic that a form can have. It's a great alternative way to present your forms and subforms.

FIGURE 12: The alternative form/subform view in Access 2000 using the new subdatasheets.

The Accompanying Database and Files

An Access 2000 database called shapes.mdb has been provided with the objects shown in FIGURE 13.

FIGURE 13: An Access 2000 database called shapes.mdb has been provided with these objects.

If you're going to use this software, you'll need a registered version of the hierarchical flexgrid control on your computer. You get this by purchasing a copy of VB (the Learning Edition will do). If you want to simply try this software, you can install the simple VB project supplied in the download. This will register the ActiveX control on your PC for run-time use. You won't be able to add the control to a new access form, but you'll be able to copy the existing form.

For More Information

• Subdatasheets: Open the Access 2000 Help and search for subdatasheets.
• ADO, shaped recordsets: Try a Web site called http://www.codehound.com. This is a great source of material on VB.
• Try http://www.asptoday.com/search.asp?category=Data%20Access for free ASP articles that feature a lot of ADO code.
• If you have Office 2000 Developer Edition, try the code librarian. MSDN is also a must for this type of information.

Garry Robinson is the founder of GR-FX Pty Ltd., a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access and Visual Basic issues, visit his company's Web site at  http://www.vb123.com, or sign up for his Access e-mail newsletter by sending a blank e-mail to mailto:tips@gr-fx.com The Web site also features a popular shareware data mining tool written in Access, and class libraries that will help you run Office Automation. When Garry is not sitting at a keyboard, he can be found playing golf or snorkeling at one of Sydney's lovely bays. Garry can be reached via e-mail at mailto:garry@gr-fx.com, or via phone at +61 2 9665 2871.