I am attempting to create 2 ASCII (fixed length) files from 2 tables in my database. The code isn't difficult, but it isn't working. My code looks like this:
    MyFileName = "c:\NBDBR.TXT"
    MyFileName2 = "c:\NBDBO.TXT"
    Call DoCmd.TransferText(acExportFixed, "ExportReportsNBDB", "Reports", MyFileName)
    Call DoCmd.TransferText(acExportFixed, "ExportOrdersNBDB", "Orders", MyFileName2)

This code works in the "immediate" window. It works when I step through in debug mode. However, when I run the module, I end up with 2 files of zero size. I am guessing it is not having time to complete the commands when running real-time. Can someone help me out with a timing loop or something? Or if I'm totally off-base, can you head me in the right direction? Thanks.

Lori

Hi Lori,

I don't know how big are your tables. There is a limit within ACCESS but this is not the problem i think.

try to code this

DoCmd.OutputTo acTable, "mytablename", "MicrosoftExcel(*.xls)", "c:/my-tables/mytable.xls", False, ""

or DoCmd.TransferText acExportDelim, "", "mytablename", "c:\my-tables\mytable.txt", True, ""

you may create also a macro to do the same transfer.

Kind regards,
Bassam

Bassam Sater

The tables vary in size; currently they contain 48 & 77 records.

If I understand your code correctly, it will produce either an Excel worksheet, or a comma-delimited ascii file. Unfortunately, neither of those will fit the bill. This is being FTP'd to an archaic system, and it will only accept the fixed-length format.

Lori

Sorry then, i never tried such a file transfer.

Bassam

Bassam

Have you tried using the Print # method?
Grab a recordset of your table and loop through it, printing out to your file.
You get each line exactly as you want it then.

Janine

Janine, you're brilliant. That worked much better than what I was trying. Thanks.

Lori

Lori,

I have a process for uploading information to a mainframe through ftp using Access97 on a Windows 2000 machine. Following is what I have to write the text files:

DoCmd.TransferText acExportFixed, "CPCUpload Export Specification", "Upload", "I:\CPCUpload.txt", False, ""

Ron

Ron, How do you upload it to the mainframe? I am interested on more info on this.



Sal