Null Disquisition

Lots of talk about nothing
 

Export an ADO Recordset as CSV or XML

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 parse-able 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.

Filed under  //   Access Nightmares   data   shootmeintheface   vb   xml  
Posted June 27, 2008