View Single Post
  #2 (permalink)  
Old December 24th, 2005, 02:24 AM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mike,

I did this in ADO, though if you're just writing out Jet tables DAO would be the recommended choice (per MS). The conversion would be super simple.

Also, I just provided the nuts and bolts using a few BASIC I/O functions. There are a few things you'd probably want to add to make the code more robust, based on what you need and what your data looks like. You'd want some error handling to see if the file you're writing to already exists, then either kill it, or open it for append. Also some error handling to see if the recordset is empty or not, etc. You may want to scrub the output a bit also, replacing single quotes in your data steam with double quotes, that sort of thing.

I do have the code check for null fields, and just insert a ";" if it finds any.

The only argument you need for the function is the name and path of the file you want to create, like:

ExportRecordset("C:\CSV.txt")

The function returns a long to indicate success or failure. I usually just return a 0 in the body of the function for success, and a 1 in an error handler for failure. Anyway, here's the function:

Code:
Function ExportRecordset(strFileName As String) As Long

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field

   Dim strDelimiter As String
   Dim intFileNumber As Integer
   Dim intCount As Integer
   Dim intNumberofFields As Integer
   Dim intIndex As Integer

   Set cnn = CurrentProject.Connection

   Set rst = New ADODB.Recordset
   rst.Open "tblData", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

   strDelimiter = ";"

   '--------------------------
   '-- Build header string --
   '--------------------------
   For intCount = 0 To rst.Fields.Count - 1
      strHeader = strHeader & strDelimiter & rst.Fields(intCount).Name
   Next intCount
   strHeader = Right$(strHeader, Len(strHeader) - Len(strDelimiter))

   intFileNumber = FreeFile
   Open strFileName For Output As intFileNumber

   '----------------------
   '-- Write out header --
   '----------------------
   Print #intFileNumber, strHeader

   '------------------------
   '-- Write out records --
   '------------------------
   intNumberofFields = rst.Fields.Count - 1

   rst.MoveFirst
   Do Until rst.EOF
      For intIndex = 0 To intNumberofFields
         If (IsNull(rst.Fields(intIndex))) Then
            Print #intFileNumber, strDelimiter;
         Else
            If intIndex = intNumberofFields Then
               Print #intFileNumber, Trim$(CStr(rst.Fields(intIndex)));
            Else
               Print #intFileNumber, Trim$(CStr(rst.Fields(intIndex))); strDelimiter;
            End If
         End If
      Next
      Print #intFileNumber,
      rst.MoveNext
      DoEvents
   Loop

   Close #intFileNumber

End Function
HTH,

Bob



Reply With Quote