Exporting Table to Fixed Length .dat file

[tinkertoy]

Query is built, all fields set up with formatting, etc. All fields are now text. Query is a make table query. The new table is then to be exported to a fixed length .dat file. This is for an existing Organizational Chart program, until we can complete the OrgChart from our new PeopleSoft HRMS program. I can do this manually everyday, but, I would rather not. I am writing a bat file, and code in a module that will do the following:

Batch file:
1. Open database

Module - opens first in db
1. Run query - delete/make table - tblEmployee
2. Open table - export all records to employee.dat file on the server
3. close db

GREAT!! Problem in #2 though. The file has to be a fixed length for all fields, there are 24 data fields, and 6 "filler" fields. All fields have to be a fixed length, already in place from the original program settings. There are also, font and font size settings that have to be defined.

I originally was doing an append query, where the table would open, all records would delete, close, then run query, close, open table, then export to a .dat file. I did this because of the field size properties that I set up in the table. It really didn't work. Because, I lost the field lengths, and got all kinds of 'crud' in the file. I see many suggestions in the forum, but none that totally fit my situation. I would appreciate any assistance I can get.

FYI - If I don't reply back to your reply/solution soon, sorry, I am on vacation starting Friday, coming back the Monday after the new year holiday.



Happy New Years!

tinkertoy

res07pyp@verizon.net

 

[PWise]

see my answer in thread705-434030

 

[befree]

try using the TransferText command:

DoCmd.TransferText acExportFixed, ExportSpec, WrkTbl, ExportFile

ExportSpec : is the saved export specification you have done on the table or querry (Manually), here you define your specs fixed, with separators or not,...

WrkTbl : is the tablename or querry name
ExportFile : is the destination and file name.

Be aware that text transfert is valid only for extension (txt, csv, tab, asc). If you want to use "DAT" extension you need:
use one of the preset extensions, or (carefully) use regedit to edit the appropriate registry entry
HKey_Local_Machine\Software\Microsoft\Jet\4.0\Engines\text

Befree,