VBA to Make a Table from a Query


Well-known Member
I need the data from a query I have, which has fields that are formulas, to be in a table. So I kind of need to do a Make table. My problem is, I need to be able to "refresh" the data in the table each time I run/update my query.

Can you have VBA code that will delete the precious table (tbl_OTD) and then recreate it with the data from a query (qry_OnTimeDelivery_MetricData) All done with a Button using VBA?


Board Regular
Re: Access: VBA to Make a Table from a Query

Hi gheyman,

I don't think there is necessity for VBA. Excel connection to SQL should suffice as it is possible to define a query for SQL, and also define in which format the data will be saved in Excel (PivotTable, Table etc)... Every time you refresh the connection, data will be updated... Not sure the policies on linking stuff on the forum (will actually have to read it up), however, google: Creating Microsoft Excel Connections to SQL databases and you will find it...

Last edited:


Well-known Member
Re: Access: VBA to Make a Table from a Query

Of course, but that will cause your db to bloat.
Better to create the table once (either manually or via VBA) and the delete the records and append them each time.

Just seen this on another forum, so perhaps what I thought was the case is incorrect.?:confused:

BTW, Make table queries are not the solution. They cause the same bloat as the delete/append method.
Last edited:


MrExcel MVP, Moderator
Re: Access: VBA to Make a Table from a Query

Losing track here a little but to answer the original question, if you kind of want a make table then you create a macro that deletes the data from you target table, then re-inserts a new set of records.

That's probably what you mean by "kind of make table". Otherwise, you could use an actual make table query.


Well-known Member
Re: Access: VBA to Make a Table from a Query

I'm not trying do anything in Excel. this is all in Access. Ultimately I want a button that will run some code. The code would refresh a query I have and Make a table out of that data. I would need it to first delete the original table or I would end up with multiple tables each time I ran the VBA.

So I was looking to see if it was possible to have Code
Find a table (tbl_OTD) and delete it.
Open and Refresh a Query (qry_OnTimeDelivery_MetricData)
Make a table from that query and name it "tbl_OTD"

Tom Schreiner

Well-known Member
Re: Access: VBA to Make a Table from a Query

From within Access:

    CurrentDb.Execute "DROP TABLE tbl_OTD"
    CurrentDb.Execute "SELECT * INTO tbl_OTD FROM qry_OnTimeDelivery_MetricData;"
You might want to check for the existence of tbl_OTD before trying to drop it.

Have a nice day!


Active Member
Re: Access: VBA to Make a Table from a Query

@gheyman - why do you need the calculated fields in a table? Can you not use the query?

Does this tie into your previous DSum issues - perhaps there is a better way of going about the problem if you share it in its entirety, it seems that the last few issues you have had have been the result of the previous solution - i.e. fix one problem, encounter another.

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...