Solved: Creating Index File

[FrymanSMU]

I'm trying to create and .txt index file from my table data. The index file is a Generic Index that has a header and then field values on every line. I figure I can create the code for the text then loop through every record but I'm not sure how to create the text file. Any reference suggestions or advice is appreciated.

Thanks,
Rich

[OBP]

Rich, can't you just export the table as a .txt file?

[FrymanSMU]

But I need to transpose the column data into Rows...

Data1 | Data2
Data3 | Data4

into...

Data1
Data2
Data3
Data4
Data5

[OBP]

OK looping through the records is no problem and you use the Open statement to open the file and Print # to write to it
see the Access VBA Editor's help on print # to get an example.

If you need more help let me know, an example database would be nice.

[FrymanSMU]

OBP, You rock as always. That worked fine, I can usually find my way but you guys really help me get going in the right direction. I'll post up the code I used just for others to reference.

Thanks a bunch!

[vba]
Public Sub CreateIndex()
Open "C:\Datafile.txt" For Output As #1
'Create Index Header
Print #1, "COMMENT: OnDemand Generic Index File Format"
Print #1, "COMMENT: Start Header"
Print #1, "CODEPAGE: 923"
Print #1, "COMMENT: "
Print #1, "IGNORE_PREPROCESSING:1"
Print #1, "COMMENT:End Header"
'Open Table with CrossRef Accounts
Dim db As DAO.Database
Dim CR_Rst As DAO.Recordset
Set db = CurrentDb
Set CR_Rst = db.OpenRecordset("CrossRef_Accts")
If CR_Rst.RecordCount > 0 Then
Dim i: i = 1
Do Until CR_Rst.EOF
'Print Individual Records here
Print #1, "COMMENT: Index Set " & i
Print #1, "GROUP_FIELD_NAMEocId"
Print #1, "GROUP_FIELD_VALUE:"
Print #1, "GROUP_FIELD_NAME:RollNumber"
Print #1, "GROUP_FIELD_VALUE:"
Print #1, "GROUP_FIELD_NAME:FrameNumber"
Print #1, "GROUP_FIELD_VALUE:"
Print #1, "GROUP_FIELD_NAME:ClientNumber"
Print #1, "GROUP_FIELD_VALUE:" & CR_Rst!acct
Print #1, "GROUP_FIELD_NAMEocType"
Print #1, "GROUP_FIELD_VALUE:MD2"
Print #1, "GROUP_FIELD_NAME:Barcode"
Print #1, "GROUP_FIELD_VALUE:" & CR_Rst!br & CR_Rst!acct & "MD2"
Print #1, "GROUP_FIELD_NAME:YearMonthDate"
Print #1, "GROUP_FIELD_VALUE:" & Year(Date) & Right$("0" & Month(Date), 2) & Right$("0" & Day(Date), 2)
Print #1, "GROUP_FIELD_NAME:OfficeNo"
Print #1, "GROUP_FIELD_VALUE:" & CR_Rst!br
Print #1, "GROUP_FIELD_NAME:LoadDate"
Print #1, "GROUP_FIELD_VALUE:" & Format(Now, "mm/dd/yy hh:mm")
Print #1, "GROUP_OFFSET:0"
Print #1, "GROUP_LENGTH:0"
Print #1, "GROUP_FILENAMELmorgdall.00092737.ACCTDOC1.ACCTDOC1.01.1.0.ACCTDOC1.ACCTDOC" & i & ".out"
CR_Rst.MoveNext
i = i + 1
Loop
End If
Close #1
End Sub
[/vba]