Null Disquisition

In lieu of my typical witty banter I’m just going to post some code. Way too tired to put any effort forth.

Tried for days to get Access to export my ADO Recordset as something, anything. I tired:

• Loading it into a table - nope
• Making a temporary table on the database server and linking to it - nope
• Tried using getRows and manually creating a CSV - nope
• Conjured up the ancient spirits of BASIC - nope

I mean really. And the internet was of course no help. Turns out there’s a few built-ins that do the trick - go figure.

Problem: Need to export the data… why oh, why can’t I export the data

XML Solution:

Dim ADOrs As ADODB.Recordset
Set ADOrs = Me.RecordsetClone
ADOrs.Save "export.xml", adPersistXML
ADOrs.Close

Shoot me in the face that was easy.

CSV Solution:

Dim ADOrs As ADODB.Recordset
Set ADOrs = Me.RecordsetClone

Dim csv As String
csv = ADOrs.GetString(, , """,""", """" _
  & vbCrLf & """", "")
csv = Left$(csv , Len(csv) - 1)

Dim h As String ' Header row
For X = 1 To ADOrs.fields.Count - 1
  h = h & """" & ADOrs.fields(X).Name & """"
  If X < ADOrs.fields.Count - 1 Then
    h = h & ","
  End If
Next X
Open "export.csv" For Output As #1
Print #1, h
Print #1, """", csv
Close 1

Edit: An interesting note about the ADO Recordset XML export. It uses a special namespace (actually a few). It’s readable enough, and just as easily parsible as any other XML document. A nice feature about this XML spec is that you can load it back into Access as an ADO recordset - so in theory this could be used for long-term caching of large chunks of data.

The title is rather representative of the conceit of this post. Suppose you have a form with several subforms that you arrange in a tabular manner (I think Access calls them pages). Now you probably don’t want to show every tab unless there is some content within that tab worth seeing, so we write a routine which (upon loading the parent form) will quickly check the subforms’ recordsets and see if they have any data in them.

Problem: Automatically check for content within subforms arranged in a tabular manner, and do something as a result of content/no content.

Solution: Well, keep reading.

Basically, what we’ll do is create a collection for the subforms and a collection for the pages. It is necessary to make sure these collections “line up” - meaning item 1 in the form collection corresponds with item 1 in the pages collection. VB doesn’t really have a good data structure for this sort of thing - well, I suppose you could make a 2d collection. You implement it how you see fit.

Code:

Dim mySubForms As New Collection
mySubForms.Add Me.sfrmCasing
mySubForms.Add Me.sfrmFormation
mySubForms.Add Me.sfrmGPLog

Dim myPages As New Collection
myPages.Add Me.pgCasing
myPages.Add Me.pgFormation
myPages.Add Me.pgGPLog

Dim rs As Recordset
Dim i As Integer
For i = 1 To mySubForms.Count
Set rs = mySubForms.Item(i).Form.RecordsetClone
If (rs.RecordCount = 0) Then
myPages.Item(i).Visible = False
Else
myPages.Item(i).Visible = True
End If
Next i

Pretty straightforward. Loop through each subform, see if there’s any data there, if so set the visibility of the corresponding page to True, otherwise set it to False. The same basic idea could be used to embolden a page title when there’s data - instead of showing/hiding, you would change the font style of the page caption.

Using all this Access jargon is making me nauseous - I’m going to go write some Python.

-David

So here’s a fun fact - when you are browsing data displayed in a Continuous Form or a Datasheet, Access takes it upon itself to requery the database a gagillion times to make sure you have the latest token of data.

After a little experimenting, I found that: clicking into a field, mouseing over a button, or scrolling through results causes Access to query the database. I confirmed this using Windows’ network manager, as well as monitoring the database itself. Since I am going to be utilizing continuous forms quite often, I needed to find a fix for this.

For you impatient types, the short answer is ADODB. Read on for explaination.

Problem: Need to cache recordsets so Access won’t kill the server when using a continuous form or datasheet.

Solution: Manually set the recordset for my sub-form using an ADODB connection and recordset.

My first attempt at this led me to the fillCache method for a recordset. Microsoft’s documentation is less than inspiring (http://msdn.microsoft.com/en-us/library/bb221030.aspx), but the basic idea is you set the cursor location (bookmark) and the number of records you want to cache, and the Jet engine will handle the rest. Well, this is not really all that true. I was suprised to see very little documentation, or discussion about the fillCache method on the interwebs, but I can only assume that’s because it doesn’t really work.

I’ll spare you the 2 hour journey through google search results and books off my shelf.

Code:

Open the Connection

Dim con AS ADODB.Connection
Dim rs AS ADODB.Recordset
Set con = New ADODB.Connection
con.open "Data Source=MyDataSource;" _
& "Initial Catalog=MyDB;" _
& "User ID=joepublic;" _
& "Password=foobar"

Create the load the recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = dbUseClient
rs.Open "SELECT fields FROM table WHERE id = " _
& Me.id_from_parent_form, con


Clean up and update the recordset of the form

Set rs.ActiveConnection = Nothing
con.Close
Set Me.sfrmSomeSubForm.Form.Recordset = rs
rs.Close
Set rs = Nothing


Now, the ADODB connection and recordsets are closed and gone, but the data remains in memory. Huzzah! N.B., there are several options for the Open method of the ADODB Recordset which should not be overlooked. You can find more info on that in the VB help files if you search for “ado recordset open”.

Cheers