VBA to Make a Table from a Query

gheyman

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?
 

pella88

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


Br
pella88
 
Last edited:

welshgasman

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:

xenou

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.
 

gheyman

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:

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!
 

stumac

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

  • 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...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • 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...
  • 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#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top