Results 1 to 8 of 8

Thread: VBA to Make a Table from a Query

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Make a Table from a Query

    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?
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    Board Regular
    Join Date
    Aug 2013
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by pella88; Sep 23rd, 2019 at 04:10 PM.

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    654
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by welshgasman; Sep 23rd, 2019 at 04:18 PM.
    Office 2007
    Access novice. Sometimes trying to give something back

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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"
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  6. #6
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Yes, xenou
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    449
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •