Export to file from Access2K

  • Thread starter Thread starter A E
  • Start date Start date
A

A E

Hi,

I'm trying to run a For each statement, I end up in the debugger with
Run-time error '3251' Operation is not supported for this type of object.
What I'm trying to do is to put the Fieldnames in top of the exported file.

This is as far as I've reached:

Dim rsCurr As DAO.Recordset
Dim fldCurr As DAO.Field
Dim intFile As Integer
Dim curTotal As Currency
Dim strFile As String
Dim strOutput As String
Dim varData As Variant

strFile = "C:\Output.txt"
intFile = FreeFile()
sngTotal = 0#

Set dbCurr = CurrentDb()
Set rsCurr = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbForwardOnly)

If rsCurr.EOF = False Then
Open strFile For Output As #intFile
varData = ""
'"This is the part that doesn't work"
For Each fldCurr In rsCurr
varData = varData & fldCurr.Name & ","
Next fldCurr


Best regards, AE
 
Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub
 
Back
Top