How do I automate a Make Table?

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
kobysAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
 
Dale FyeCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I would recommend against Make-table queries, "
Hummmmm.  Maybe is some situations, but not all.  

mx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kobysAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Dale FyeCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"but I cannot think of many."
I have no fear of Make Table queries :-)

mx
0
 
Dale FyeCommented:
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
 
kobysAuthor Commented:
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
 
Dale FyeCommented:
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
 
kobysAuthor Commented:
So I create a table, then delete everything from it, then append my new table to that empty table?
0
 
hnasrCommented:
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
 
kobysAuthor Commented:
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
 
jmoss111Commented:
make table queries might not create an index but CREATE INDEX DDL does
0
 
kobysAuthor Commented:
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
 
jmoss111Connect With a Mentor Commented:
You can DROP MyTable and then SELECT * INTO MyTable FROM TABLE1 WHERE whatever... its set based no looping
0
 
kobysAuthor Commented:
   DoCmd.RunSQL "DROP TABLE Model_Inputs_All_w_Overwrites;"
    DBEngine(0)(0).Execute "Q_Model_Inputs_All_w_Overwrites", dbFailOnError

Seemed to work.
0
 
jmoss111Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.