TransferText Method

  • Thread starter Thread starter Peter Hallett
  • Start date Start date
P

Peter Hallett

Help on this method leaves one or two questions unanswered. Firstly, does
the transfer append the source data to the target table or is the latter
overwritten? Is there a choice? (I can't see one in the syntax.) Secondly,
if the target table does not exist, will the method create it? If so, does
this action require manual sanction? If it does, can the associated message
be suppressed.
 
Peter,
does the transfer append the source data to the target table or is the
latter
overwritten? Is there a choice? (I can't see one in the syntax.)
Click to expand...

It appends the source data to the target table. It doesn't overwrite.
There is no choice about overwriting.
if the target table does not exist, will the method create it?
Click to expand...
No it will not create the target table.
If you don't specify the target table, the code fails with an error.

Jeanette Cunningham
 
Well, that is very interesting. The reason I posed the question was that I
have a large number of instances where TransferText is to be used and, for
practical reasons, it would have been much quicker to get a definitive answer
before planning major code changes. The alternative was to write a test
fragment and ‘suck it and see’, but that was obviously going to take some
time. After waiting a while, I took the latter approach in parallel – belt
and braces just in case no one responded – but, rather disturbingly, my
initial results appear to contradict your answers Obviously I will need to
sort this out before I amend the original code. (It’s in almost continuous
use.)

In my test fragment, TransferText does create the target table and, if the
latter already exists, it replaces it. The first characteristic is very
welcome but the second not so. I need to assemble incoming data, contained
in a series of files, into one large table. It would appear that I have to
input the source documents individually into a series of tables (potentially
up to 75) and then write a corresponding number of append queries to
concatenate these tables into a single master. The snag is that, although I
can use the TransferText method in a For…Next loop to import the source files
into a set of tables, e.g. T1, T2, T3…T75, I can’t immediately see a way of
doing something similar with the following append query. Instead of a single
query in a loop, it appears that I will need 75 virtually identical append
queries to do the job. That’s messy and I feel sure there must be an easier,
or at least a more elegant, solution. If you have any suggestions I would be
most grateful.
 
Peter,
In my test fragment, TransferText does create the target table and, if the
latter already exists, it replaces it.
Click to expand...

In my tests, TransferText won't run if you don't specify a table name with
the code (I seem to have answered a different question from the one I
thought you asked).

Sounds as if all your files have the same structure with the same sort of
data.
If that is true, you can set up the import table with the necessary fields.
Use your loop to import the data from all files into the same table, instead
of separate tables.
You can then import into the table without overwriting the existing data.
(Which was what I meant when I answered your question).
TransferText lets you specify the table to import the data into and also
specify the source file to import from.

In your case, it may be possible to link to the tables and use append
queries in a loop to move the data into the table in access. Sometimes this
is easier than importing.

Jeanette Cunningham
 
My initial test suggested that the TransferText method overwrote the target
table. However, your insistence that it was an append, not an overwrite,
action made me re-examine my test code. Foolishly, I had deleted the target
file before running TransferText, making it look like an overwrite, or
replacement. When I reran the test, it did, indeed, append the source file
to the target - and thank goodness for that. It saves an awful lot of
trouble further down the line. Thanks very much for your help.
 
Back
Top