Export File

[DS]

I'm using the following to Export a table into a text file. Can I
export more than one file? If so how would I do it. Also what is the
limit on the number of tables that I can export?

Thanks
DS

DoCmd.TransferText acExportDelim, , "Table1", "c:\Access\export.txt"

 

[John Vinson]

I'm using the following to Export a table into a text file. Can I
export more than one file? If so how would I do it. Also what is the
limit on the number of tables that I can export?

Thanks
DS

DoCmd.TransferText acExportDelim, , "Table1", "c:\Access\export.txt"

Each file would require a different line, with a different Tablename
and a different filename. If you have 1250 tables, you can do 1250
exports, I'd suppose!

John W. Vinson[MVP]

 

[John Nurick]

Assuming that the number and types of field in the tables are
compatible, you can create a union query and export that, e.g.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
....
SELECT * FROM TableN
;

I think there's a limit on the number of UNION clauses but it varies
with different database engins and Ican't remember what it is.

The alternative is to export each table individually and then use the
Windows COPY or XCOPY command to concatenate them into a single file.

 

[DS]

Each file would require a different line, with a different Tablename
and a different filename. If you have 1250 tables, you can do 1250
exports, I'd suppose!

John W. Vinson[MVP]

Good News Bad News. The good new is that I have only sixty tables, the
bad news is one line for each, I was hoping for some kind of Global
command with a variable or something.
X=table ???
Thank You for your reply, John.
DS

 

[DS]

Assuming that the number and types of field in the tables are
compatible, you can create a union query and export that, e.g.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
...
SELECT * FROM TableN
;

I think there's a limit on the number of UNION clauses but it varies
with different database engins and Ican't remember what it is.

The alternative is to export each table individually and then use the
Windows COPY or XCOPY command to concatenate them into a single file.

John if I do use the SQL how would I write that to a text file. On the
other end I need to reintroduce them into a their proper tables.
Thanks
DS

 

[John Vinson]

Good News Bad News. The good new is that I have only sixty tables, the
bad news is one line for each, I was hoping for some kind of Global
command with a variable or something.
X=table ???

Well, you could write a loop through the TableDefs collection...

Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim strFile As String
Set db = CurrentDb
For Each tdf In db.Tabledefs
If Left(tdf.Name, 4) <> "MSYS" Then ' skip system tables
strFile = "K:\SomePath\" & tdf.Name & ".txt"
DoCmd.TransferText acExportDelim, , tdf.Name, strFile
Next tdf

Step back a bit here. What are you trying to ACCOMPLISH? There may
well be a better way!

John W. Vinson[MVP]

 

[DS]

Well, you could write a loop through the TableDefs collection...

Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim strFile As String
Set db = CurrentDb
For Each tdf In db.Tabledefs
If Left(tdf.Name, 4) <> "MSYS" Then ' skip system tables
strFile = "K:\SomePath\" & tdf.Name & ".txt"
DoCmd.TransferText acExportDelim, , tdf.Name, strFile
Next tdf

Step back a bit here. What are you trying to ACCOMPLISH? There may
well be a better way!

John W. Vinson[MVP]

Ok John,
I have the same database at different locations. What I'm doing is
this: I export a table to a textfile, that file then gets E-Mailed to a
main office. The main office opens the E-Mail and the attachment gets
imported back into the table at the other site. I have 60 tables. This
works fine, (I'm open to anything better!) with a couple of tables, I
was just trying to automate it without having to write 60 lines of
Import/export code.
Thanks
DS

 

[John Nurick]

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John if I do use the SQL how would I write that to a text file. On the
other end I need to reintroduce them into a their proper tables.

I think I misread your question. John Vinson's response makes more
sense.

 

[John Vinson]

Ok John,
I have the same database at different locations. What I'm doing is
this: I export a table to a textfile, that file then gets E-Mailed to a
main office. The main office opens the E-Mail and the attachment gets
imported back into the table at the other site. I have 60 tables. This
works fine, (I'm open to anything better!) with a couple of tables, I
was just trying to automate it without having to write 60 lines of
Import/export code.
Thanks
DS

You should look into Replication. It's fairly complex to set up but
it's designed for exactly this situation. See

http://support.microsoft.com/kb/282977/en-us

On the other hand, if you're transmitting all of the tables and
basically rebuilding the database at the other end, why not compact
and Zip the database and just email the whole thing??

John W. Vinson[MVP]

 

[DS]

You should look into Replication. It's fairly complex to set up but
it's designed for exactly this situation. See

http://support.microsoft.com/kb/282977/en-us

On the other hand, if you're transmitting all of the tables and
basically rebuilding the database at the other end, why not compact
and Zip the database and just email the whole thing??

John W. Vinson[MVP]

Thanks John, The problem with zipping and sending is that I have several
databases coming in and the all have to be married together to review
the information. Also they have to remain apart to manipulate the
information. Is there a way of emailing juat the tables from each
location and then importing them into another database to do what I have
to do?
Thanks
DS

 

[John Vinson]

Is there a way of emailing juat the tables from each
location and then importing them into another database to do what I have
to do?

Only by exporting them to text, dBase, or Excel format, I fear.

John W. Vinson[MVP]

 

[DS]

Only by exporting them to text, dBase, or Excel format, I fear.

John W. Vinson[MVP]

Thanks John, the Text files seem to work fine, it's just 60 tables, I
wish I could combine all 60 tables into one text file, then break the
text file apart at the other end into seperate tables, Thanks
DS

 

[John Vinson]

Thanks John, the Text files seem to work fine, it's just 60 tables, I
wish I could combine all 60 tables into one text file, then break the
text file apart at the other end into seperate tables, Thanks
DS

Well, you could; you'll need to put some sort of separator string into
the file after each table: some string which you can count on NEVER
appearing in the table.

It would be a bit of a hassle unbundling them on the other side
though!

I haven't done so, but here's an idea: write the files into a Zip
folder, and email it that way. One file, compacted, containing all the
text files.

John W. Vinson[MVP]

 

[DS]

Well, you could; you'll need to put some sort of separator string into
the file after each table: some string which you can count on NEVER
appearing in the table.

It would be a bit of a hassle unbundling them on the other side
though!

I haven't done so, but here's an idea: write the files into a Zip
folder, and email it that way. One file, compacted, containing all the
text files.

John W. Vinson[MVP]

Thanks John, Thats a good start! Simple but effective, now...what are
the commands in VBA to Zip the files, I guess I would zip the folder
that they are in, then the command to unzip is? Once again thank you.
DS

 

[John Vinson]

Thanks John, Thats a good start! Simple but effective, now...what are
the commands in VBA to Zip the files, I guess I would zip the folder
that they are in, then the command to unzip is? Once again thank you.
DS

I haven't done this but my friend Doug Steele posted this suggestion a
while back:

If you've already got WinZip, one option is to get the free Command
Line Support add-in from http://www.winzip.com/downcl.htm You'll then
be able to use the Shell command to get WinZip to do what you want.
Another option is to take a look at the various compression controls
Tony Toews documents at

http://www.granite.ab.ca/access/compression.htm


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



John W. Vinson[MVP]

 

[DS]

I haven't done this but my friend Doug Steele posted this suggestion a
while back:

If you've already got WinZip, one option is to get the free Command
Line Support add-in from http://www.winzip.com/downcl.htm You'll then
be able to use the Shell command to get WinZip to do what you want.
Another option is to take a look at the various compression controls
Tony Toews documents at

http://www.granite.ab.ca/access/compression.htm

Thanks John, I'll give 'em a try.
DS