CODEWITHSUNDEEP
databasevbams-access
Jerram Lindsay
Jerram Lindsay

Reputation: 33

Auto updating access database (can't be linked)

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.

Upvotes: 1

Views: 49

Answers (1)

SunKnight0
SunKnight0

Reputation: 3351

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.

Upvotes: 1

Related Questions