Retrieve data from flatfile to table plus auto update table and re

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I’m doing a small project using Access but I do not know how to approach the
situation.
I’m trying to create a table which retrieves data from a flat file (txt.
file) and automatically updates itself everyday.
At the moment, I manage to manually update the table by overwriting the
imported data however it creates duplicates instead of overwriting existing
data which leads to errors in the table.
Once this predicament is unravelled, I need to include a couple of queries
and then create a report which automatically updates.

I could use any help i can get!!!
 
Without going into any further detail you could run a delete query to remove
the data prior to the import.

DELETE BusinessContact.*
FROM BusinessContact;

SQL such as the above will delete all records in the Table BusinessContact,
change the name to your table a run before importing the data.
 
Hi Jay,

First you need to define what is meant by a duplicate. If it's something
like the combination of three fields, or better yet just one field, defines a
unique record, then you have hope.

First you must manually rid the table of duplicates.
Then open your table in design view.
Go to View, Indexes. A dialog box will open.
In the first open row, make up a simple name, with no spaces, in the Index
Name column.
In the column next to the name, select a Field Name that is one of the
unique fields.
Drop straight down and pick another Field Name. No index name needed.
Repeat until all the fields that make the record unique are included.
Next click on the index name that you just created then go down towards the
bottom of the dialog box to the Index Properties.
Set Unique to Yes and then save the table design changes.
If Access complains chances are that you still have some records that
violate the uniqueness.
After you get that mess straightened out, the next time that you append
records to the table Access will not allow in records which would become
duplicates.

We'll work on the rest after you get the unique record situation fixed.
 
Hi Jay,

Instead of importing the data every day, I'd use a linked table
(File|Get External Data|Link). If you copy each day's new textfile to
the same location with the same name, the linked table will
automatically get its data from the new version.
 
Thanks guys,

I tried all 3 suggestions and found linking the tables to be the best option
for my situation.

If anybody knows how to auto update tables and reports from an external
source by either opening Access or if theres a feature similar to this i
would appreciate it.
 
Thanks guys,

I tried all 3 suggestions and found out linking the tables was the best
option for my situation.

If anyone knows how to auto update tables and reports from an external
source either by opening Access or some other similar feature i would
appreciate it.
 
Back
Top