File Header Record

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I need to export a delimited recordset everyday...that's
no problem. But I need to have a header record that
contains the supplier name, today's date, and the number
of records that follow.

Does anyone have any ideas on the best way to do this?
 
Brian said:
I need to export a delimited recordset everyday...that's
no problem. But I need to have a header record that
contains the supplier name, today's date, and the number
of records that follow.

Does anyone have any ideas on the best way to do this?
Click to expand...
If comma delimited add a record that contains the information.
If fixed length fill fields with the information.
Use a calculated field if possible to set that record to zero and all other
records to one in that field.
If not then add a field to the records, make them all one and the header = 0
Sort on it and it's on top.
 
Unfortunately, the rest of the data fields don't match the
header record data fields. That's the problem. If it
could work out with two tables maybe, but I don't know how
I would do that either...
 
Hi Brian,

Sometimes it's possible to use a cunning union query that returns a
dummy first record with the header information, followed by the records
you want to export. Start by modifying the query you're currently
exporting so it returns a single text field containing the line that
needs to go in the textfile, delimiters and all, e.g.

Rec: [ID] & Chr(9) & [LastName] & Chr(9) & [FirstName]

The union query will be something like

SELECT "This is the header information" AS Rec,
0 AS IsData FROM AnyTable
UNION
SELECT, 1 AS IsData Rec FROM TheQuery
ORDER BY IsData;

Then export the union query.

Otherwise, the choice is basically between
a) Create the textfile in the usual way and then prepend the header
record (e.g. by writing the header to another textfile and then using
something like
Shell("COPY " & strHeaderFileSpec & " + " & strDataFileSpec _
& " " strFinishedFileSpec)
to concatenate them)

b) Create the entire file from VBA: first write the header line and then
iterate through the recordset assembling each record's fields into a
string which is written to disk.
 
Yeah...I guess as a last resort I can do choice A. Choice
B sounds a little cleaner, but I'm not sure where to start
in VBA...which command(s) would I use...I don't expect
anyone to write it for me...I'm just pressed for time. I
can try the Union query also.

Appreciate the valuable insight, John.

-----Original Message-----
Hi Brian,

Sometimes it's possible to use a cunning union query that returns a
dummy first record with the header information, followed by the records
you want to export. Start by modifying the query you're currently
exporting so it returns a single text field containing the line that
needs to go in the textfile, delimiters and all, e.g.

Rec: [ID] & Chr(9) & [LastName] & Chr(9) & [FirstName]

The union query will be something like

SELECT "This is the header information" AS Rec,
0 AS IsData FROM AnyTable
UNION
SELECT, 1 AS IsData Rec FROM TheQuery
ORDER BY IsData;

Then export the union query.

Otherwise, the choice is basically between
a) Create the textfile in the usual way and then prepend the header
record (e.g. by writing the header to another textfile and then using
something like
Shell("COPY " & strHeaderFileSpec & " + " & strDataFileSpec _
& " " strFinishedFileSpec)
to concatenate them)

b) Create the entire file from VBA: first write the header line and then
iterate through the recordset assembling each record's fields into a
string which is written to disk.

I need to export a delimited recordset everyday...that's
no problem. But I need to have a header record that
contains the supplier name, today's date, and the number
of records that follow.

Does anyone have any ideas on the best way to do this?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Brian said:
Unfortunately, the rest of the data fields don't match the
header record data fields. That's the problem. If it
could work out with two tables maybe, but I don't know how
I would do that either...
Click to expand...

If you are exporting fixed length to a text file it does not matter.
The supplier name can contain "the supplier name, today's date, and the
number of records that follow."
The program that reads this data can't see it.
If it's delimited then it gets a bit more of a probmlem.
The question is, what format does the table have to be in?
You could also drop to a dos copy command and prepend the header to the rest
of the table.
This could also be done in code. If the export is fairly small it will be
reasonably fast.





 
Hi Brian,

B goes something like this air code:

Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim strLine as String
Const DELIM = vbTab

lngFN = FreeFile()
Open "D:\Folder\File.txt" For Output As lngFN

strLine = "Assemble header field here"
Print #lngFN, strLine

Set rsR = CurrentDB.OpenRecordset("My Query")
With rsR
.MoveFirst
Do Until .EOF
strLine = ""
For Each fldF in .Fields
strLine = strLine & DELIM & Cstr(Nz(fldF.Value, "")
Next
strLine = Mid(strLine, Len(DELIM)+1) 'dump superfluous DELIM
Print #lngFN, strLine
Loop
End With
Close #lngFN
rsR.Close
Set rsR = Nothing


Yeah...I guess as a last resort I can do choice A. Choice
B sounds a little cleaner, but I'm not sure where to start
in VBA...which command(s) would I use...I don't expect
anyone to write it for me...I'm just pressed for time. I
can try the Union query also.

Appreciate the valuable insight, John.

-----Original Message-----
Hi Brian,

Sometimes it's possible to use a cunning union query that returns a
dummy first record with the header information, followed by the records
you want to export. Start by modifying the query you're currently
exporting so it returns a single text field containing the line that
needs to go in the textfile, delimiters and all, e.g.

Rec: [ID] & Chr(9) & [LastName] & Chr(9) & [FirstName]

The union query will be something like

SELECT "This is the header information" AS Rec,
0 AS IsData FROM AnyTable
UNION
SELECT, 1 AS IsData Rec FROM TheQuery
ORDER BY IsData;

Then export the union query.

Otherwise, the choice is basically between
a) Create the textfile in the usual way and then prepend the header
record (e.g. by writing the header to another textfile and then using
something like
Shell("COPY " & strHeaderFileSpec & " + " & strDataFileSpec _
& " " strFinishedFileSpec)
to concatenate them)

b) Create the entire file from VBA: first write the header line and then
iterate through the recordset assembling each record's fields into a
string which is written to disk.

I need to export a delimited recordset everyday...that's
no problem. But I need to have a header record that
contains the supplier name, today's date, and the number
of records that follow.

Does anyone have any ideas on the best way to do this?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top