Transfer Text Issue

[dakcg]

HI, I need big time help: I have several tables that need to be exported, fixed width, into ONE text file. The big problem is that each table has different specifications that must be used, so I can't query everything and send it out once.

Big Question: Is there a way to combine the transfer text functions, after the specs have been run but before they are sent to the file? I tried running one after the other in a macro, but of course, the second transfer overrode the first one.

A potential, much needed, database job may be riding on figuring this out, so if anyone has any suggestions, please Help Me!

Thanks in advance for your combined brilliance.

dakcg

 

[WayneRyan]

dakcg,

Don't use TransferText, but VBA is easy.

How many tables? More important, how many fields?

Wayne

 

[dakcg]

Give me a couple of minutes and I will get you the details Thanks for answering!!

 

[WayneRyan]

dakcg,

Tedious, but highly effective:

Dim dbs As DAO.Database
Dim rst As DAO.RecordSet

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("YourQuery")

Open "C:\SomeFile.txt" For Output As #1

While Not rst.EOF and Not rst.BOF
   Print #1, rst.Field1 & Space(30 - Len(rst.Field1));
   Print #1, rst.Field2 & Space(20 - Len(rst.Field1));
   Print #1, rst.Field3 & Space(10 - Len(rst.Field1))
   rst.MoveNext
   Wend

Close #1
Set rst = Nothing
set dbs = Nothing

Wayne

 

[WayneRyan]

d,

Upon further review, if you can make a query that joins your tables, you
should be able to use the TransferText with that.

Wayne

 

[dakcg]

The following is the make up of my tables, thanks for any help

First table: Header Record: 8 Fields
Second table: Beneficiary Record: 53 fields
Third table: Dependent Record: 25 fields
Forth table: Coverage Record: 16 fields
Fifth table: Participant Record: 13 fields
Sixth table: Terminator Record: 11 fields

# of tables: 6
# of total fields: 126

Hope this is of help, Do you possible have an idea?

 

[dakcg]

Hi Wayne,

I tried the union query, but because there are fields in all the tables that don't match, you can't union query, or can you?

 

[WayneRyan]

d,

Welcome to the world of VBA!

You should be able to use my first example as a guide.

You will need to have multiple recordsets; rst1, rst2, rst3 ...

You can also open a recordset:

Set rst3 = dbs.OpenRecordSet("Select * From SomeTable Where Key = '" & rst2!Field1 & "'")

Keep me posted, I'll be around for a while.

Wayne

 

[dakcg]

thanks wayne, stay around I will give it a try, thanks again.

 

[dakcg]

Hi wayne, I have not got it to work yet, but I am pretty sure I have figured out the code. So I will work on it more. I just wanted to say thanks for your help. If I get stuck, I will be back. Thanks, d

 

[WayneRyan]

d,

OK, good luck ... you can do it!

Wayne

 

[dakcg]

DB Link for waynes help

Here is the new database with the new module. I am so sorry that I am such an idiot with this, I am great with most other Access objects. Thanks for your help.

 

[WayneRyan]

Doreen,

???

Wayne

 

[dakcg]

sorry, I am an idoit

I will try again.

 

[dakcg]

try again

Her it is, I hope

 

[WayneRyan]

Doreen,

I looked at Module5:

1) The header should just be Sub
2) You needed rst! for the field references
3) You need Tools --> References, then add Microsoft DAO 3.6 library
4) Call it by putting "WriteFile" as the code for a Command Button

Public Sub WriteFile()  <-- This line is different
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("tblCoverage")

Open "C:\Documents and Settings\Doreen Ann Lathrop\Desktop\MyDatabases\tblTest.txt" For Output As #1

While Not rst.EOF And Not rst.BOF
    Print #1, rst!RecordType & Space(1); <-- These lines are different
    Print #1, rst!SsnIQB & Space(11);
    Print #1, rst!EmployeesSSN & Space(11);
    Print #1, rst!PlanID & Space(12);
    Print #1, rst!PlanSequenceNumber & Space(2);
    Print #1, rst!CoverageClassID & Space(3);
    Print #1, rst!PlanBeginDate & Space(8);
    Print #1, rst!PlanEndDate & Space(8);
    Print #1, rst!BeneficiaryRate & Space(7);
    Print #1, rst!BenefitCredit & Space(7);
    Print #1, rst!EmployeesSSN & Space(11);
    Print #1, rst!PolicyAmount & Space(12);
    Print #1, rst!ClientPaidTThroughDate & Space(8);
    Print #1, rst!PlanCreditDescriptionID & Space(12);
    Print #1, rst!Padding & Space(494);
    Print #1, rst!EndOfRecord & Space(1);
    Print #1, rst!EndOfLine & Space(2);
    rst.MoveNext
    Wend
    
    Close #1
    Set rst = Nothing
    Set dbs = Nothing
    
End Sub

Keep me posted,
Wayne

 

[dakcg]

Thank you, I will try the code again, I will let you know. And I really thank you for helping us newbees out. You are Great. Thanks

 

[WayneRyan]

Doreen,

Glad to help, keep me posted.

Wayne

 

[WayneRyan]

Doreen,

In your Subroutine, you needed rst!SomeField - not rst.

You needed a reference to Microsoft DAO 3.6

You needed to call your subroutine from a button

hth,
Wayne

 

[dakcg]

Thank you so very very much. I ran the one txt that you had coded for and it worked perfect. So Thank You!!!. Now I am going to try to add the other recordsets that have to be sent out, one at a time. Wish me luck.

And once again, Wayne, thank you for all your help

Take care
Doreen