Running an Access Make Table Query using an Excel Macro

[kkernohan]

Hi,

I have an excel spreadsheet that is linked to an access database. I currently have to go into access and run a macro that I have created to update all of the tables and then go into excel and refresh all of my pivot tables/data tables.

What I would like to do is make it so that on the front page of my excel spreadsheet I have a button that will update the access tables automatically. I have taken a look around the internet but couldnt find a way to do this. There is only 4 tables to update so if its easier to just run the make tables seperately instead of the macro that works as well.

for example, I have 4 make table queries in Access, Table1, Table2, Table3, Table4.

I would like a macro in excel to run all four of these queries.

Thanks for the help.

 

[Norie]

Why do you need to run these queries to update the data?

Is more data being added?

 

[chuckles1066]

Set db = CreateObject("Access.Application")
db.Visible = False
db.OpenCurrentDatabase (dbloc)
db.DoCmd.RunMacro "mcr_import_data"

This silently opens your database (defined as dbloc which points to it as a Const) and runs whatever queries you have added to the Access macro named mcr_import_data.

Easy-peasy

 

[kkernohan]

Norie, yea it will update the data in the access queries which the pivot tables are run off of.

Chuckles, Thanks for the start! How do I 'select' the access database I want to open? do I have to place the directory/file name in place of dbloc?

thanks for the help

 

[chuckles1066]

Simply declare it:

Const dbloc As String = "J:\Operations\Reporting\chuckles1066\Schedules\db1.mdb"

Obviously replace my path with yours.

 

[Norie]

How will it update the data?

Are the make table queries based on other queries?

 

[kkernohan]

ah yes thanks chuckles. I think I'm almost there. Im getting a vba error saying "Run-Time error 3262: Could not lock table"

Do you know what this would be from?

thanks again for the help

 

[kkernohan]

Norie - The make table queries are based on Oracle tables. Its going to basically just include the new data that has been added since the last time I have ran the update.

thanks

 

[chuckles1066]

ah yes thanks chuckles. I think I'm almost there. Im getting a vba error saying "Run-Time error 3262: Could not lock table"

Do you know what this would be from?

thanks again for the help

When you have the database open, it creates a record locking file in the background and is denoted by the file extension .ldb.

With the database closed have a look in the folder where it lives and see if there is still an .ldb file.

If there is, try deleting it and see if that cures your problem.