Transfer 2 related tables into 1 tab del text file

[Justin Hess]

Hi There,

I've got an annoying problem in that I have more than 255 varaibles, hence
I've had to split and link the various tables. I was wondering if it could be
at all possible to export and append these tables into one tab del test file?
or even csv? as this appears to be my only option to have one file with all
the variables.
Any assistance will be greatly appreciated!
Thanks

J

 

[John Nurick]

Hi Justin,

You can't have more than 255 fields in a query, so there's no
straightforward way of doing this.

One approach would be to create a query that returns all the fields you need
from one table concatenated with tabs into a single long field, e.g.

SELECT Field1 & Chr(9) & Field2 & Chr(9) & ... & FieldN AS TheFields
FROM Table1 ORDER BY FieldX;

Repeat with other tables, so you have two or more queries which between them
return all the fields you need, with the same number of records in the same
order.

Then use VBA code to do something like this:
Open a recordset on each query
Open a text file for output
Do Until Recordset1.EOF
Write Recordset1.Fields(0) & vbTab & Recordset2.Fields(0) to output
file
Recordset1.MoveNext
Recordset2.MoveNext
Loop
Close file
Close recordsets