VBA to Make a Table from a Query

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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...


Br
pella88
 
Last edited:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
Re: Access: VBA to Make a Table from a Query

Yes, xenou
 
Upvote 0
Re: Access: VBA to Make a Table from a Query

From within Access:

Code:
    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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top