[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I automate a Make Table?

Posted on 2011-04-26
19
Medium Priority
?
474 Views
Last Modified: 2013-11-27
In order to speed up processing, I thought I should do make tables out of some of my queries at certain points in my code.  I have used Make Table in queries before but how do I do this in VBA?

Thanks for any help.

- Susan
0
Comment
Question by:kobys
  • 6
  • 4
  • 4
  • +3
19 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35469634
Generally, I would recommend against Make-table queries, as these will not have indices and will generally be slow.  Instead, create the table ahead of time, and index the fields as appropriate.  Then, if you feel you need this intermediary step, use a Delete query to delete all of the records from the table, and an Append to add the new records.
0
 
LVL 75
ID: 35469646
"I would recommend against Make-table queries, "
Hummmmm.  Maybe is some situations, but not all.  

mx
0
 

Author Comment

by:kobys
ID: 35469672
I don't have any kind of preference for Make Table queries it's just that I when I do queries on queries on queries everything slows down substantially.  I have found that if I do Make Tables along the way it speeds things up substantially.  Perhaps I am going about things entirely the wrong way?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 75
ID: 35469704
Sample VBA code:

Dim sSQL As String
sSQL = "SELECT Table1.FIELD1, Table1.FIELD2, Table1.FIELD3 INTO tblYourMakeTableName FROM Table1"
CurrentDb.Execute sSQL, dbFailOnError

mx
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35469749
OK, Joe.  I'm sure there are some instances where their use is appropriate, but I cannot think of many.

Yes, having a permanent table that must be cleaned out on a regular basis can be a pain, and can cause you to have to compact your application regularly.  Personally, I prefer to create "table templates" within my front-end application.  Then, when I need these tables I create a temporary external database, use the TransferDatabase method to copy the template table to the temporary mdb/accdb, then link those back into my application.  This way, I never actually populate the "template" and don't see a lot of bloat in my front-end.
0
 
LVL 58
ID: 35469750

  I generally perfer to pre-create the tables as well as when using make table queries, I usually will get burned in some way by data typing issues on fields.

JimD.
0
 
LVL 75
ID: 35469787
"but I cannot think of many."
I have no fear of Make Table queries :-)

mx
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35469841
kobys,

I'm not bashing your need for an intermediary table.  I frequently find that when several reports make use of some of the same queries, that it is beneficial to add this intermediary step.

My point was that it would most likely be quicker, in the long run, to already have the destination table built, and to populate it via an Append query, rather than a Make-table query.  When you create a make-table query, the indices associated with fields in that query are not carried forward, so when you subsequently use those fields in a WHERE or GROUP by clause, they are less efficient than they would be if they had indices defined.

Clean-up (deleting all the records in the intermediate table) for this process should be performed whenever you close the database, and just before populating the table.
0
 

Author Comment

by:kobys
ID: 35470210
That is probably one of my big problems, I'm not using an index.  I will go back and implement this.  

To do the append query, do I have to loop through each record or is there a way to do a "bulk" append.

Thanks.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35470261
Basically, use the query designer just like you would for a "Select" query.  Then click the "append" query button and identify the table that you are appending to.  Finally, map the fields in your Select query to the fields in the table you are dumping the data into.  The SQL will look something like:

INSERT INTO [tempTable] (field1, field2, field3)
SELECT field1, field2, field3
FROM [someOtherTable]

0
 

Author Comment

by:kobys
ID: 35470356
So I create a table, then delete everything from it, then append my new table to that empty table?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 35470508
You can create the table structure, then use the append if you like!

Private Sub Command24_Click()
    ' use the code in your script.
    DoCmd.SetWarnings False
    DoCmd.RunSQL "SELECT tbl1.*  INTO tblx FROM tbl1 Where false"
    DoCmd.SetWarnings True
End Sub
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 35470548
For an existing table that you have created (tblYourTableName)

Dim sSQL As String
sSQL = "DELETE tblYourTableName.* FROM tblYourTableName"

'Delete existing records
CurrentDb.Execute sSQL, dbFailOnError  

'Add new records
sSQL = "INSERT INTO tblYourTableName SELECT Table1.* FROM Table1"
CurrentDb.Execute sSQL, dbFailOnError

mx

0
 

Author Comment

by:kobys
ID: 35475695
The query I want to save as a table is Q_Model_Inputs_All_w_Overwrites and the table I want to save it as is Model_Inputs_All_w_Overwrites.  I tried this code:

 
Private Sub SaveTbl()
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE Model_Inputs_All_w_Overwrites.* FROM Model_Inputs_All_w_Overwrites"
    DoCmd.RunSQL "INSERT INTO [Model_Inputs_All_w_Overwrites] SELECT [Q_Model_Inputs_All_w_Overwrites] FROM [Q_Model_Inputs_All_w_Overwrites]"
    DoCmd.SetWarnings True

End Sub

Open in new window


I'm getting "Run-time error '3417': An action query cannot be used as a row source."

Anyone have an idea what I'm doing wrong?

Thanks!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 35475714
make table queries might not create an index but CREATE INDEX DDL does
0
 

Author Comment

by:kobys
ID: 35475857
Private Sub SaveTbl()
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [Model_Inputs_All_w_Overwrites].* FROM [Model_Inputs_All_w_Overwrites]"
    DoCmd.RunSQL "INSERT INTO [Model_Inputs_All_w_Overwrites] SELECT [Q_Model_Inputs_All_w_Overwrites].* FROM [Q_Model_Inputs_All_w_Overwrites]"
    DoCmd.SetWarnings True

End Sub

Open in new window


I assume the problem is that I can't do a SELECT.. FROM... a query.  This gets me back to my original question which is how do I make a query into a table without looping through every record.

Thanks!
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 1000 total points
ID: 35475993
You can DROP MyTable and then SELECT * INTO MyTable FROM TABLE1 WHERE whatever... its set based no looping
0
 

Author Comment

by:kobys
ID: 35476031
   DoCmd.RunSQL "DROP TABLE Model_Inputs_All_w_Overwrites;"
    DBEngine(0)(0).Execute "Q_Model_Inputs_All_w_Overwrites", dbFailOnError

Seemed to work.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 35476051
SUB MySub
DIM DB AS DAO.Database
SET DB = Currentdb
DB.EXECUTE "DROP MyTable", dbfailonerror
DB.EXECUTE "SELECT * INTO MyTable FROM Table1", dbfailonerror
END SUB
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses
Course of the Month17 days, 22 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question