VBA to Make a Table from a Query

[gheyman]

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?

 

[pella88]

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

 

[welshgasman]

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.?

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

 

[xenou]

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.

 

[gheyman]

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"

 

[gheyman]

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

Yes, xenou

 

[Legacy 98055]

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!

 

[stumac]

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.