1

I've got a CSV file that refreshes every 60 seconds with live data from the internet. I want to automatically update my Access database (on a 60 second or so interval) with the new rows that get downloaded, however I can't simply link the DB to the CSV.

The CSV comes with exactly 365 days of data, so when another day ticks over, a day of data drops off. If i was to link to the CSV my DB would only ever have those 365 days of data, whereas i want to append the existing database with the new data added.

Any help with this would be appreciated. Thanks.

Improve this question
3
  • 3
    I've not tried importing from a CSV, but you could link to it and then use a query to pull any new data into a separate table. I take it the CSV has some kind of key field? Date & Time probably. Commented Apr 11, 2018 at 13:31
  • 3
    You should link it anyway and then create some queries to update master table from linked one and then execute some scheduled task, which is able to run these queries from command line. Maybe you need to create some executable yourself - I haven't seen command-line MS Access client, but I have not searched for it either. Commented Apr 11, 2018 at 13:31
  • ^What they wrote. Does the CSV have 1 record per day, or 1 record per minute? If the latter, it would probably be wise to link a shortened file that only has the newest records, not the original one with 500k rows. Commented Apr 11, 2018 at 17:43

1 Answer 1

Reset to default
1

As per the comments the first step is to link your CSV to the database. Not as your main table but as a secondary table that will be used to update your main table.

Once you do that you have two problems to solve:

Identify the new records

I assume there is a way to do so by timestamp or ID, so all you have to do is hold on to the last ID or timestamp imported (that will require an additional mini-table to hold the value persistently).

Make it happen every 60 seconds. To get that update on a regular interval you have two options:

A form's 'OnTimer' event is the easy way but requires very specific conditions. You have to make sure the form that triggers the event is only open once. This is possible even in a multi-user environment with some smart tracking.

If having an Access form open to do the updating is not workable, then you have to work with Windows scheduled tasks. You can set up an Access Macro to run as a Windows scheduled task.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.