Update Access Table Every Hour

[koolwaters]

I have a split database with an OBDC connection to four tables. I have a query that pulls the results of these four tables together. This query holds the main data being used by the database but when using any form or report that uses the data from this query, they run extremely slow.

I created a table (tblLinkedData) matching the fields in the query and established relationships with this table and the other tables in the database, and this runs smoothly.

My issue is that the data in the linked tables are often updated and records are also added.

Is there anyway to have access delete all the records from my table (tblLinkedData) and update with all of the records from the query every hour? This way, there is access to the updated records as well as the new records.

 

[Dhathaway]

Hi Koolwaters,

You may use a VBScript or VBA module to programatically run subroutines, macros, and query-objects. Next, you will want to use the Windows Task Scheduler to activate whichever object you created. Running a VBA module with Task Scheduler is more difficult, so I would recommend using a VBScript.

Here are two examples of VBScripts - one for executing subroutines, and the other for queries. Add either example to a text file, and save it with the extension ".vbs". The subroutine method will function for .mdb-formatted databases, but I'm not sure that DoCMD.OpenQuery exists in that earlier model.

'Execute a VBA subroutine within Access
Dim AcApp
Set AcApp = CreateObject("Access.Application")
AcApp.OpenCurrentDatabase("C:\users\me\desktop\test.accdb")
AcApp.Run "ModuleNameHere"
AcApp.Quit
Set AcApp = Nothing

'Execute a query within Access
Dim AcApp
Set AcApp = CreateObject("Access.Application")
AcApp.OpenCurrentDatabase("C:\users\me\desktop\test.accdb")
AcApp.DoCmd.OpenQuery("QueryNameHere")
AcApp.Quit
Set AcApp = Nothing

Good luck.

David

 

[koolwaters]

Hi David,

I was unable to accomplish it using your method, however I was able to get it done another way from other posts I read.

I added the code I wanted to run to a module and then created a macro to run the module.

I created a task in task scheduler that first started Access and then opened the database, run the macro and then quit.

Thanks for your feedback.

Cheers!

 

[hasseb1]

My issue is that the data in 74-409 the linked tables are often updated and records are also added.
Is there anyway to have access delete all the records from my table (tblLinkedData) and update with all of the records from the query every hour? This way, there is access to the updated records as well as the new records.

 

[koolwaters]

I run a few SQL statements.

First, a delete statement is used to delete all of the records in tblLinkedData.
Then an insert statement to tblLinkedData
Then an insert and update into the table where the data actually belongs

I created a public function to run the SQL statements and then created a macro to call that function.

Once that was completed, it was easy to use Task Scheduler to schedule a task to run the macro at specific times.