Hi

I am hoping someone maybe able to help with this. I currently have a make table set up in access and I am trying to run it from excel. I can pull data from the access database via excel but I cant figure out how to run the make table from excel. If indeed this is possible. Any ideas?

Cheers
DeadDuck

You can link the tables if the data changes in ecxel and you want that to always be your data in table. Or you can import from a excel file.

File --> Get external data

Hi Jay,

thanks for the reply but I have managed to run the make table query by using the following code

Set Db = DAO.DBEngine.OpenDatabase("db Name")
Db.Execute "Qry_MakeTableName"

I have changed this now to run a delete query and then an append query to first clear the table and then write the new data to it using the following code:

Set Db = DAO.DBEngine.OpenDatabase("db Name")
Db.Execute "Delete Query Name"
Db.Execute "Append Query Name"

Unfortunately I am now having trouble passing parameters to the Append query to stop it appending all the data. For example I am trying to limit the data by Country so that it only appends all of Austria's data into the table rather than the whole worlds. I have a cell in excel which has the country name in that I want to limit by, hopefully I will be able to work this out. Unfortunately I have to use excel as the front end and hide access.

thanks for your feedback
DeadDuck

If you use the docmd.openquery "Qry_MakeTableName" you don't need to delete the table first. The docmd.openquery automatically deletes the table if it exists while the Db.Execute command returns an error if the table exists. Also, try using the docmd.openquery instead of Db.Execute in your other commands as the docmd.openquery is more adaptable for changing parameters verses the Db.Execute. The Db.Execute is more designed for "static" and unchanging parameter queries. See if that works.

Why are you making a table in the first place?

Couldn't you just use a normal select query to return the required data?