Re: Best way to import text file into existing table on a daily ba

You're welcome!

The formats do not need to be the same in all three data locations (text
file, temp table, and permanent table). I usually find that it's best if the
temp table has mostly Text data types because that will prevent "import
errors" when Jet/ACCESS think the incoming data are "wrong".

If you have a text field in the temp table containing "1" or "2" text string
value, it will be changed to the numeric 1 or 2 value in the permanent
table's field when you run an append query that appends that data item into
the permanent table's numeric field. So long as the data in the temp table
can be converted to the data type in the permanent table, all is well.
Sometimes, I will cast the data using a C... function (e.g., CLng, CInt,
CStr, CDate, etc.) just to be sure, but usually this approach is not needed.

Input Masks will not be "applied" to data being added via an append query or
via an import process -- masks get applied only when you manually enter
data. If you need the input mask's format applied to the data for the
permanent table, you'll need to modify the append query so that it "changes"
the data to the desired format. Not knowing your specific needs here, take a
look at Format function and/or concatenating other characters into the data
as part of the append query.

--

Ken Snell
<MS ACCESS MVP>



"bluesky" <bluesky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:82C44F3C-80C7-427A-BF49-43E389B46B27@xxxxxxxxxxxxxxxx
Hi Ken,

After thinking it over, the order of the data doesn't matter!

Re: new patients and existing patients, I figured out a way around that.
I
can run the report myself and when it asks for how many days in the past,
I
could put whatever time frame I want, so it will be since I last ran it!!
I
don't know why I didn't think of that before. Actually, I didn't realize
I
could change the "default" value of -20 days.

Ok, so, I whould be ok, but I still have some questions, re: formatting, I
hope you don't mind... I wonder if you realize how many jobs you have
saved
by helping us newbies out???

So, I have three files:
Text file
Temporary transfer table
Existing table


My question is, does the field names and data type (long interger, text,
etc) have to be the same in all three files, as well as field size and
other
formatting.

I know I can change the field name and data type in the import
specification.
But I'm running into other problems with some fields, for instance in my
temporary and existing table, I have an input mask for the telephone
number,
and other fields, but that doesn't seem to work now.

For example, "sex" in the text file is M or F, but my temporary table and
my
existing table have it set as numberic, 1=f, 2=m, but it seems that I need
to
keep it all text for it to work properly. I thought I needed "sex"
numberic
for data analysis, but maybe I can change it at that point?

Thanks again,


.

Relevant Pages

  • Date and Time query
    ... files to a temp import table. ... I need to separate the date into the format, yyyymmdd, and the time ... hhnn using an append query to another table I have ... I have tried to use the DatePart in conjunction with the format ...
    (microsoft.public.access.queries)
  • Re: Run two from one cntrl button
    ... On Wed, 02 Mar 2005 02:18:51 GMT, postman wrote: ... > The button at the moment runs an append query from a temp table into a permanent dB table, but does not delete the contents of the Temp table afterwards. ...
    (microsoft.public.access.formscoding)
  • Run two from one cntrl button
    ... The button at the moment runs an append query from a temp table into a permanent dB table, but does not delete the contents of the Temp table afterwards. ... I would prefer to have one button do both queries, ...
    (microsoft.public.access.formscoding)
  • Re: Adding a new file extension for Pipe Delimited files
    ... Dim Temp As String ... Dim FileNumber As Long ... open and handle like CSV files? ... allow me to keep the pipe delimited format. ...
    (microsoft.public.excel.programming)
  • Re: Type Conversion Failure
    ... Allen Browne - Microsoft MVP. ... I have an Append Query that worked fine in Access97 but now fails to ... The field in question is set up as a Long Integer in the target table. ... It will not let me format the ...
    (comp.databases.ms-access)