your post is not completely clear, but I will give it a shot. I
understand that you want to write data from 10 tables to 1 text file.
What is not clear is if the tables all have 255 fields or not. The
other issue is that data contained in each field. You want a comma
delimited text file. If there is any punctuation in any of the fields,
and that punctuation happens to be a comma, it will throw off your
delimiter (the comma) because you will have 2 commas for that field.
Lets say all the tables have 255 fields and there are no commas in any
of the fields (actually, it doesn't really matter if the tables all have
the same number of fields for writing to a text file - but reading from
the text file to a table might make a difference). Then you can do this
using VBA. The following code assumes you are writing data from an mdb
file:
Sub WriteDataToTextFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, strPath As String, arrTbls As Variant
Dim i As Integer, j As Integer
strPath = CurrentProject.Path
arrTbls = Array("tbl1", "tbl2", "tbl3", ..., "tbl10")
Set DB = CurrentDB
Close #1
Open strPath & "\yourTxtFile.txt" For Append As #1
For i = 0 to Ubound(arrTbls)
Set RS = DB.OpenRecordset(arrTbls(i))
Do While Not RS.EOF
str1 = ""
For j = 0 to RS.Fields.Count - 2
str1 = str1 & RS(j) & ","
Next
str1 = str1 & RS(j)
Print #1, str1
RS.MoveNext
Loop
RS.Close
Next
Close #1
End Sub
So what is going on here is that I am placing all the table names in an
array called arrTbls and looping through that array. Then I use DAO
code to loop through the records in each table. The part that writes to
the textfile is
Open strPath & "\yourTxtFile.txt" For Append As #1
and in the Do Loop I collect the values of all the fields in a string
variable called str1 using yet another For Loop, and then I use the
Print function to print that row to the text file, then move on to the
next record.
You can copy/paste this code in a standard code module. Then you can
place your mouse cursor over some of the expressions like Open (from the
Open strpath line) and press the F1 key to bring up a Help file that
explains how the Open statemenet works. You can place your mouse cursor
over most VBA objects (not the variables that you declare) and the
properties or methods and press the F1 key to bring up a Help file that
provides some explanation on how the object works.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***