Import data

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I import data from a disk daily and sometimes I have to
import an updated version of information for the same
date in the afternoon. I currently delete the morning
data and import the afternoon updated version. Is there
away to import without duplicating the information or
have to go through the delete process.
 
Nick,

I suppose there is a date field in the records? Then a quick and dirty way
to do it would be to:
1. Set-up a Delete query that deletes all records where date = today
2. Set-up a simple macro that runs the delete query first, then the Append
one
So you run the macro to update, and if there is already data for the day it
is deleted first.

If there is no date field, you will need to add one.

It gets a little more complicated if you need to do updates for a different
date, in which case you would need a way to "read" the date form the new
record and pass it on to the delete query as a parameter; not difficult,
just an extra step in setting it up.

HTH,
Nikos
 
Nikos,

There is a date field in the record and it would be great
if I could up date any date without checking it. As far
as the delete query it always ask if I want to continue.
1. How do I stop that? 2 How do you set up the read
statement? This sounds complicated. Please, Go step by
step. I am a little slow.
-----Original Message-----
 
Nick,

How you retrieve the date from the file on the disk depends on how you do
the import:

If the filename on the disk is always the same and the file is a linked
table in your database then things are simple; the criterion on the date
field in your Deelete query would be something like:
DFirst("[DateFieldName]","LinkedTableName")

If, on the other hand, you are doing a TransferText, then I can think of
three options:
(a) use code to read the date directly from the text file, so you can use it
in the criterion in the Delete query, or
(b) link the text file even if just for the purpose, so you can use the
DFirst like before, or
(c) do the import in a temporary table (delete everything in it before the
import), then use a DFirst on it for the Delete query, then use an Append
query to transfer the records from the temp table to the final one.

Note, all the above assumes there will ever only be one date's worth of data
on the disk.

In order to suppress warnings / confirmation on action queries (delete,
append, update, make table) in a macro, use a SetWarnings action with
argument False. If you need to restore warnings within the query (so, for
instance, for getting a warning on another action query that is run next)
use another SetWarnings with argument True.

HTH,
Nikos
 
Nikos,
I am transfering Txt file into an Access table. The
Table is identical to what is being imported. I have to
retain the last 30 days of date. The text file does have
a date field in it and because it is a txt file there are
some import filters being used. I'm thinking code must
look at what is being imported and 1. determine if it has
that date in it and if not import the file and 2. if the
date store in the Access database is the same as the file
to be imported and delete that date and replace it with
the update version.
I am using validation rules for the file upload and am
still receiving the warning message. How do I turn them
off?
Your help is much appreciated. I have had to think out
what I realy need. Thanks
-----original Message-----
Nick,

How you retrieve the date from the file on the disk depends on how you do
the import:

If the filename on the disk is always the same and the file is a linked
table in your database then things are simple; the criterion on the date
field in your Deelete query would be something like:
DFirst("[DateFieldName]","LinkedTableName")

If, on the other hand, you are doing a TransferText, then I can think of
three options:
(a) use code to read the date directly from the text file, so you can use it
in the criterion in the Delete query, or
(b) link the text file even if just for the purpose, so you can use the
DFirst like before, or
(c) do the import in a temporary table (delete everything in it before the
import), then use a DFirst on it for the Delete query, then use an Append
query to transfer the records from the temp table to the final one.

Note, all the above assumes there will ever only be one date's worth of data
on the disk.

In order to suppress warnings / confirmation on action queries (delete,
append, update, make table) in a macro, use a SetWarnings action with
argument False. If you need to restore warnings within the query (so, for
instance, for getting a warning on another action query that is run next)
use another SetWarnings with argument True.

HTH,
Nikos



Nikos,

There is a date field in the record and it would be great
if I could up date any date without checking it. As far
as the delete query it always ask if I want to continue.
1. How do I stop that? 2 How do you set up the read
statement? This sounds complicated. Please, Go step by
step. I am a little slow.
-----Original Message----- where
date = today already
data for the day it
Click to expand...


.
Click to expand...
 
Nick,

Personally, I would opt for reading the date directly from the file through
a few lines of code (I understand the name of the file doesn't change). I'll
be happy to give you sample code for that, but I'll need a sample file to do
it; wanna mail me one?

To turn off the warning message: how do you do the import? Macro or code?
Where do you apply the validation rules? What kind of warnings do you get?
The more details I have, the more chances I have to help.

Nikos

Nick said:
Nikos,
I am transfering Txt file into an Access table. The
Table is identical to what is being imported. I have to
retain the last 30 days of date. The text file does have
a date field in it and because it is a txt file there are
some import filters being used. I'm thinking code must
look at what is being imported and 1. determine if it has
that date in it and if not import the file and 2. if the
date store in the Access database is the same as the file
to be imported and delete that date and replace it with
the update version.
I am using validation rules for the file upload and am
still receiving the warning message. How do I turn them
off?
Your help is much appreciated. I have had to think out
what I realy need. Thanks
-----original Message-----
Nick,

How you retrieve the date from the file on the disk depends on how you do
the import:

If the filename on the disk is always the same and the file is a linked
table in your database then things are simple; the criterion on the date
field in your Deelete query would be something like:
DFirst("[DateFieldName]","LinkedTableName")

If, on the other hand, you are doing a TransferText, then I can think of
three options:
(a) use code to read the date directly from the text file, so you can use it
in the criterion in the Delete query, or
(b) link the text file even if just for the purpose, so you can use the
DFirst like before, or
(c) do the import in a temporary table (delete everything in it before the
import), then use a DFirst on it for the Delete query, then use an Append
query to transfer the records from the temp table to the final one.

Note, all the above assumes there will ever only be one date's worth of data
on the disk.

In order to suppress warnings / confirmation on action queries (delete,
append, update, make table) in a macro, use a SetWarnings action with
argument False. If you need to restore warnings within the query (so, for
instance, for getting a warning on another action query that is run next)
use another SetWarnings with argument True.

HTH,
Nikos



Nikos,

There is a date field in the record and it would be great
if I could up date any date without checking it. As far
as the delete query it always ask if I want to continue.
1. How do I stop that? 2 How do you set up the read
statement? This sounds complicated. Please, Go step by
step. I am a little slow.
-----Original Message-----
Nick,

I suppose there is a date field in the records? Then a
quick and dirty way
to do it would be to:
1. Set-up a Delete query that deletes all records where
date = today
2. Set-up a simple macro that runs the delete query
first, then the Append
one
So you run the macro to update, and if there is already
data for the day it
is deleted first.

If there is no date field, you will need to add one.

It gets a little more complicated if you need to do
updates for a different
date, in which case you would need a way to "read" the
date form the new
record and pass it on to the delete query as a
parameter; not difficult,
just an extra step in setting it up.

HTH,
Nikos

I import data from a disk daily and sometimes I have to
import an updated version of information for the same
date in the afternoon. I currently delete the morning
data and import the afternoon updated version. Is there
away to import without duplicating the information or
have to go through the delete process.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top