Exporting to a TextFIle (2000)

[horacio]

I have tree tables (1,2,3) The tables has the same record count. What I need to do is export the three tables on the same text file but the order must be
table 1 record1
table 2 record1
table 3 record1
table 1 record2
table 2 record2
table 3 record2
.....next record

[MarkLiquorman]

I would say this will take some VBA code. You will have to open 3 recordsets in code. Then you will need to read a record from 1 recordset, write it to the file; then read a record from rs#2, write it; then read from rs#3, then write it; then repeat.

See Access Help for Write to see how to write to a sequential file.

[horacio]

I'm trying to use VBA but I have a small problem. It replace the file when I try to copy the data. You have some idea in how to appent inf. to the text file without replace it?

[charlotte]

Post the code you're using to write to the text file. You're going to have to file i/o, and it's hard to guess at what you overlooked without seeing the code.

[TearsInTheRain]

Do you NEED to use VBA. Using my relatively tiny knowledge of Access could you not insert 2 fields into each table.

FIRST field is an index of sorts (Primary key or autonumber?)
SECOND field denotes the table it belongs to (1, 2 or 3?)

Could you then append data from all tables to another table of the same format & then sort by the INDEX field and then the TABLE FROM field.

To my knowledge you should then have all your records in the order you desire, ready to export as a text file.

But like I say, I'm no expert on Access, but I hope this helps.

[MarkLiquorman]

While this technique might work (stress MIGHT), it has some major flaws; plus it just isn't good technique. This "index field" you mentioned is dependent on a sort sequence (which we really don't know). For the merge to work as specified (record from table1, then from #2, then from #3) with your solution would require some common numbering scheme. That is, there needs to be 3 records with the same common "index field" (one from each table). So if a new record is added, or one deleted, you have to renumber the records each time you run the merge.

As for storing a table #, you can easily add this to a query so storing it is superfluous.

[MarkLiquorman]

I'm not sure. After opening the output file, I'd first read thru the records until I got to an EOF. Then try writing to file.

[TearsInTheRain]

I was making the assumption (always a bad thing to do!!) that the tables in their own right were already sorted in the required order.

[charlotte]

Sorts don't hold when you export a table. The records are exported in natural order.

[charlotte]

One of the arguments for the Put statement is record number. If you want to append a record to the end of a text file, you give it the highest record number in the text file plus 1. That means you have to know how many records are already in the text file, and you do that in the Get part of the code. If you don't post your code, all we're doing is guessing at what you might be doing wrong, and that can go on forever.

[MarkLiquorman]

>>I was making the assumption (always a bad thing to do!!) that the tables in their own right were already sorted in the required order.<<

Very bad assumption. Access tables are NEVER sorted. You may see them displayed in a sorted order (Access will often default to ordering the output by the primary key if no other order specified), but the records in the table are not rearranged.