Data Shaping

Over the years, while perusing various help files and articles on ADO I had seen 'DataShaping' mentioned many times as being a time saving new way of working with certain kinds of recordsets. I was recently working on an Access project and had a little time before the deadline and so decided to give it a whirl. I thought I would jot down my experiences here for the benefit of anyone experimenting with/learning to use datashaping in Access as I hit a number of frustrations and found few useful resources via Google searches.

Problem 1: Making the ADO Connection - Specifying the Provider

As you will be aware, you must make an ADO Connection in order to be able to open a recordset based on a table or query. This was my first hurdle to jump.

Normally to open a recordset in Access you would simply use lines such as:

Dim rs As New ADODB.Recordset
rs.Open "tblCompany", CurrentProject.Connection, adOpenDynamic

The CurrentProject.Connection part returns a connection string to the current database (for some reason I have terrible trouble remembering this as I have become so used to the DAO way of doing things - old dogs and new tricks).

The problem with the connection and datashaping is that the string returned by CurrentProject.Connection appears as follows:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=...

This provider (Microsoft.Jet.OLEDB.4.0) does not support data shaping, for that you must specify the Provider as "MSDataShape". Of course, as soon as you create a connection string specifying MSDataShape as the provider, you can't open the recordset as this provider can only shape the records, not open them. Aggghhh!

This was the bit that had me foxed for a while. If the Provider is Microsoft.Jet.OLEDB.4.0 I can't do any shaping. If the provider is MSDataShape then I can't get hold of the records I want to shape.

After much searching through help files and on Google I discovered that it is possible to specify a Provider (for manipulating the records) and a "Data Provider" (for getting hold of the records you want to manipulate in the first place).

The solution I eventually wound up using was (conn is an ADO Connection object):

conn.Provider = "MSDataShape"
conn.Open "Data " & CurrentProject.Connection
rs.Open "SHAPE {SELECT * from tblCategory}" & _
        " APPEND ((SHAPE {select * from tblSubCategory} " & _
        " APPEND ({select * from tblItem} AS rsItem " & _
        " RELATE SubCategoryID TO SubCategoryID)) AS rsSubCategory " & _
        " RELATE CategoryID TO CategoryID)", conn

Note that the Provider is explicitly set, then I open the Connection with a string that specifies the Data Provider by prefixing the CurrentProject.Connection property (which as we saw above returns a string) with "Data ".

In the command to open the recordset, the connection object (conn) is the last argument (don't worry about the scary Shaping syntax in the SQL statement, we'll discuss that later).

You can actually shorten the set up of the connection object to a single line if you wish.

conn.Open "Provider=MSDataShape;Data " & CurrentProject.Connection

The above creates a connection object identical to the first example, but does so on one line rather than two.

Problem 2: Database Locking During Design

This is a problem that I have still not resolved. It is not a show stopper, just an annoyance. My data shaping code gets called from a form. If I have the form closed and examine the code, all is well. If I open the form in design mode and examine the code, again all is well. If I open the form normally, then change to design view and then change back to normal view, the next time the code is run I get the error:

"The database has been placed in a state by user (me) on machine (my laptop) that prevents it from being opened or locked."

I can resolve the problem by closing and re-opening the form, though sometimes I then get the message that I don't have exclusive access to the mdb and won't be able to save changes. When this happens I have to close and re-open the mdb after which all returns to normal.

I am closing the connection and recordsets and setting them to nothing, so I don't know what the issue is here.

Problem 3: OK, Its Working. How Do I DataShape?

Now that I had figured out the connection string settings I was ready to start shaping - but how? what can shaping do for me and what is the syntax?

Fortunately a Google search yielded much more on this issue and this MS Knowledge Base article has excellent details.

In short, DataShaping can be a real bonus where you have a hierarchy such as Customer, Orders and Order Details. DataShaping creates hierarchical recordsets so you can easily open the recordset for the first customer, then drill down into a recordset of the orders that apply to that customer, then as you work through each order, you can drill down into a recordset for the order details.

You can achieve the same results with normal recordsets, but it does require much more work and depending on your approach, a lot more work for the database too (if you fetch the order data for each customer in turn for example).

For my project I needed to display a list of options that were grouped in Categories and Sub-Categories. I had the following tables

tblCategory
tblSubCategory (with a reference to tblCategory.CategoryID)
tblItem (with reference to tblSubCategory.SubCategoryID)

To return this data as a hierarchical recordset, I used the following SQL:

rs.Open "SHAPE {SELECT * from tblCategory}" & _
        " APPEND ((SHAPE {select * from tblSubCategory} " & _
        " APPEND ({select * from tblItem} AS rsItem " & _
        " RELATE SubCategoryID TO SubCategoryID)) AS rsSubCategory " & _
        " RELATE CategoryID TO CategoryID)", conn

Having three levels makes things much more confusing than having two! Essentially I am relating Item to Subcategory (and specifying the fields used to relate them, much like a join) and I am relating Category to the result.

Problem 4: How Do I Manipulate The Hierarchical Recordset In Code?

Now we have a connection and a hierarchical recordset we need to do something with it.

The example below shows how to work with a hierarchical data set. Basically, where we have related one recordset to another (e.g. relating the SubCategories to the Categories), each parent record contains a recordset of the children. For each Category, as well as all the normal fields in the recordset, there is a rsSubCategory field that is actually a recordset of all the SubCateries for that Category.

In the code we can set a recordset object to the rsSubCategory 'field'. We can then loop through and manipulate this recordset. We can then do the same thing with the Items as there is an rsItem 'field' that is actually a recordset of all the Items related to the current SubCategory. I may not have done myself (or readers) a favor by giving the 'field/recordset' in the SQL the same name as the recordset objects I use in the code, e.g:

Set rsItem = rsSubCategory!rsItem.Value

Set [ChildRecordset] = [ParentRecordset]![Field.Value]

In the above, the second reference to rsItem is the name of the 'field' in the rsSubCategory recordset object.

If you want to test this, download the following zip file (compatible with Access 2000 and above) that contains a database including this code and some tables populated with sample data. Open the module basDataShapeSample, ensure the immediate window is open (Ctrl+G) and type ?Test and hit the enter key. The results will appear in the debug window. DataShapeSample.zip

Public Function Test()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rsSubCategory As ADODB.Recordset
Dim rsItem As ADODB.Recordset

On Error GoTo Proc_Err
'Open a connection using the MSDataShape Provider but also setting the Data Provider
conn.Open "Provider=MSDataShape;Data " & CurrentProject.Connection

'Three level hierarchical recordset: Category - Subcategory - Item
rs.Open "SHAPE {SELECT * from tblCategory}" & _
            " APPEND ((SHAPE {select * from tblSubCategory} " & _
            " APPEND ({select * from tblItem} AS rsItem " & _
            " RELATE SubCategoryID TO SubCategoryID)) AS rsSubCategory " & _
            " RELATE CategoryID TO CategoryID)", conn

    rs.MoveFirst

    'Loop round the first recordset (Category)
    Do While Not rs.EOF
        Debug.Print rs!Category
          'Set a second recordset to be the SubCategories for this Category and loop
        Set rsSubCategory = rs!rsSubCategory.Value
        rsSubCategory.Sort = "SubCategory Asc"
        Do While Not rsSubCategory.EOF
            Debug.Print , rsSubCategory!SubCategory

            'Set a third recordset to be the Items for this SubCategory and loop
            Set rsItem = rsSubCategory!rsItem.Value
            rsItem.Sort = "Item asc"
            Do While Not rsItem.EOF
                Debug.Print , , rsItem!Item
                rsItem.MoveNext
            Loop
            rsSubCategory.MoveNext
        Loop
        rsSubCategory.Close
        rs.MoveNext
    Loop

Proc_Exit:
On Error Resume Next
'Ensure all the references are closed and cleared
rsItem.Close
Set rsItem = Nothing
rsSubCategory.Close
Set rsSubCategory = Nothing
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Exit Function

Proc_Err:
MsgBox "Err: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Resume Proc_Exit

End Function

There is more I need to play with in DataShaping, for example, using the aggregate statements and using a hierarchical recordset as the source in the MS Hierarchical Flexigrid control that is designed for displaying these sorts of recordsets. If I get a chance to do this I will try to write up my findings.

If you would like to comment on this article (good or bad) please send me an email at the address at the top of the page.