Exporting to Text Delimiter 1

[johnwiseman]

Hello.

I have twenty four tables in MS Access which I need to export to a text delimited file. I have no problem with exporting to create twenty four seperate files using
DoCmd.TransferText acExportDelim,,"Table1", "Location1.txt"
DoCmd.TransferText acExportDelim,,"Table2", "Location2.txt"
ect.

I need to export the table to just one delimited .txt file where the first record on Table1 would be followed by the first record on Table2 followed by the first record on Table3. When all the first records have been exported the second records would be exported. So it appears like,

Table1 First Record
Table2 First Record
Table3 First Record
Table1 Second Record
Table2 Second Record
Table3 Second Record

The tables all contain different columns but share a relationship key.

Thanks in advance....

 

[CaptainD]

Look into ADO's GetString function. You could create three recordsets off the table data and loop through the recordsets to populate the text file.

You would not be "Exporting" the tables but you would be getting all the data out of them in the order you want.

Keep in mind some tables might hold more records then others so you would need to account for that.

 

[hovercraft]

I'm assuming the tables have the same fields and that each table has a record_number field (if it's autonumber convert it to a string or number)
create a 25th table with a field called "from_table" or something .
Then just do an append query to append each table's records to the new table with the "from_table" field = the table name.

Once you have the new table populated just sort on your recordnumber field and then by "from_table" and export as text delm.

I hope this makes sense. It's not really a VBA solution but it would work.

Hovercraft

 

[johnwiseman]

Hi, Thanks for look into this.

All 25 tables contain different column headers and contain different data, the only thing they share is a key as below, each line being a different table.

1, John, Smith, 28/01/2000, Male
1, 11, Road Avenue, London, UK
1, 0111, 0555555, Director, CompanyA, Sally, Hardens
1, 6ft, 2, Good Health, Brown Eyes, Brown Hair

 

[JerryKlmns]

johnwiseman

I would follow CaptainD's approach. Keep in mind that when a recordset is at the end (.EOF = True), you 'll have to export a dummy line for that table.

hovercraft 's approach, needs a ranking on the records of all tables to be appended to a new one, plus adding dummy records for those tables that have less than the max records in all 25 tables. The new table should have the max number of fields all of Text data type. But what if you have a double number showing like 9,654E+10?

 

[PWise]

this is what i would do
1)create a make table query

SELECT unionQuery.* INTO Textfilename#txt in 'C:\' [Text;HDR=NO;FMT=Delimited;IMEX=0;CharacterSet=437;DATABASE=C:\;TABLE=Textfilename#txt]
FROM (
Select Fi,f2,f3,f4,f5 ,null,null "Name TABLE"
from NameTable
union 
Select Fi,f2,f3,f4,f5 ,null,null "Address TABLE"
from AdressTable
Union
Select Fi,f2,f3,f4,f5 ,f6,f7 "Employment TABLE"
from EmploymentTABLE
......
order by F1
)unionQuery;

 

[CaptainD]

pwise, I've never seen that approach. Have a star

D