Writing out a fixed length file

[tkhouk]

Probably a noob question... Given a set of tables that I have, how is the best way to write out a fixed lenght text file? Do I need to create an empty table that has the fields defined and transfer the data into it?

Thanks,
Kevin

[NeoPa]

If you use the wizard to export a table, it gives you the option of fixed length or separator characters.
If you go into the Advanced section, it also gives you the option of saving any definition.
If you want to output the data later using code - you can, using the Text layout definition saved then.

[MMcCarthy]

Probably a noob question... Given a set of tables that I have, how is the best way to write out a fixed lenght text file? Do I need to create an empty table that has the fields defined and transfer the data into it?

Thanks,
Kevin

It can be done using VBA code but it's pretty complicated if you're not that familiar with VBA. If you are come back on this and I'll give you the code.

Otherwise if the tables have the same structure (BTW: If they do they shouldn't be separate tables in a proper database structure), you can do a union query. To do this you have to be in the sql view of the query design.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3;

You can then export the query results.

[Killer42]

...Otherwise if the tables have the same structure (BTW: If they do they shouldn't be separate tables in a proper database structure) ...

That's assuming, of course, that the silly database software can handle the size of the data. :(

[NeoPa]

That's assuming, of course, that the silly database software can handle the size of the data. :(

lol (+padding)

[MMcCarthy]

lol (+padding)

Which is why the data should not be in separate tables...

If tkhouk would like some help on structures please post your current structures and we'll do what we can to help.

[Killer42]

Which is why the data should not be in separate tables...

If tkhouk would like some help on structures please post your current structures and we'll do what we can to help.

Sorry, I was thinking of separate databases. My goof.

[NeoPa]

lol - because Killer's data is so large it has to be stored in multiple databases - even though the tables are of the same form.

On that point Killer, did you check the db sizes in your 2K dbs?
Did you also check that all text fields had 'Unicode Compression' set?

[Killer42]

lol - because Killer's data is so large it has to be stored in multiple databases - even though the tables are of the same form.

Yeah :(
Around 7GB so far, and increasing.
In fact, my memory is finally starting to kick into gear - the size limit used to be 1GB, and then increased to 2GB - a huge relief at the time. I'm almost certain I tried creating an Access 2003 format database in Access 2003, and still had the same limit.

However, if a PK allows the UNION to produce decent results, then it will certainly be much more convenient to work with smaller files.

On that point Killer, did you check the db sizes in your 2K dbs?

Sorry, I'm not sure what you mean. Do you mean the file size of my Access 2000 format databases? If so, yes, they're 2GB as stated.

Did you also check that all text fields had 'Unicode Compression' set?

Yes. (I always make sure I use the unicode compression option.)

[NeoPa]

Killer,

You should seriously consider porting your back end database to SQL Server 2005.
I believe the free version is now much more flexible than the 2K MSDE.
It will handle your databases (/tables) without strain.
Unfortunately, the SQL Server forums are not as active as these here in the Access section (No MM of course).

PS. Sorry about the 2GB limit - I thought it had gone from 2GB to 4GB. It must have been from 1GB to 2GB.