Schedule daily overnight import or refresh with ODBC connection

[kagoodwin13]

I am attempting to use Access as a back-end to Tableau, a business intelligence reporting tool.

I am able to connect my data sources to Access through an ODBC connection. I can either do a one-time import or set up a linked table. However, what I would really like is to set up a scheduled refresh or import of data. There is too much data to query directly through linked tables - it would be too slow to be convenient to use. It would be nice if it could refresh overnight (every night at 9 pm for example) so that we can have fresh data daily.

If I am thinking about this correctly, I would have to do the following:

1. Create linked tables (refresh each time they are opened)
2. Create corresponding static tables
3. Write a VBA command to update static tables from linked tables
4. Include some sort of scheduling component inside the VBA command

Does anyone know a good place to get started with the fourth step? Or am I thinking about the process all wrong to begin with?

[June7]

I use a VBScript file that has code to open db and run a procedure in a general module.

Windows Task Scheduler has item to run the VBScript on a set schedule.

Example code:

Dim accessApp
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("your file path to db\databasename.accdb")
accessApp.Run "your procedure name"
accessApp.Quit
set accessApp = nothing

Use Notepad to create the text file. Rename it to change file extension from txt to vbs.

[kagoodwin13]

That's a great solution! I'm assuming I'll have to leave my computer running overnight to complete the task?

[June7]

I think so for the Task Scheduler to work and certainly if the db file path is on your computer. I never shut down.

[pbaldy]

It sounds like this is more of a Tableau question, unless you're saying you can link to Tableau from Access and want to push the data from Access. If so, it shouldn't be hard. I'd create a separate db with a VBA function that performed your export, call that function from an autoexec macro, and run the db from Windows Scheduled Tasks.

[pbaldy]

Geez, this is what happens when you get distracted while typing. Sorry.

[kagoodwin13]

Any tips for finding or naming the procedure? I've done lots of Google searching to no avail. I am assuming it is not "Command0" or "Command0_Click" because those have not worked in the vbs script.

[pbaldy]

You'd create a public function in a standard module (they can't have the same name). You'd use the name of the function. If you're unfamiliar:

http://www.baldyweb.com/Function.htm

[kagoodwin13]

Success!

Recap of process:

1. Create linked tables
2. Create corresponding static tables
3. Set primary key in static tables
4. Write a public function inside a separate module (with different name than the function) with SQL append command to copy linked tables to static tables. Primary key in static tables will prevent duplication. See code below.
5. Use VBS script to call VBA. See code below.
6. Use Windows Task Scheduler to run VBS script at desired time/frequency

Module code to run SQL append command

Option Compare Database

Public Function ODBCRefresh()

DoCmd.SetWarnings False

    DoCmd.RunSQL "DELETE * FROM StaticTable"
    DoCmd.RunSQL "INSERT INTO StaticTable ( Field1, Field2, Field3 ) SELECT LinkedTable.Field1, LinkedTable.Field2, LinkedTable.Field3 FROM LinkedTable;"

DoCmd.SetWarnings True


End Function

VBS script

Dim accessApp
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("filepath\DatabaseName.accdb")
accessApp.Run "ODBCRefresh"
accessApp.Quit
set accessApp = nothing

[pbaldy]

Would you also want to empty the static table first?

[June7]

Probably not significant, but the procedure does not have to be a Function, it can be a Sub.

[kagoodwin13]

Yes, it would be best to empty the static table first.