Temp table used to update table keeps being sent to "assigned" when run

[gojets1721]

Sorry if the title is confusing. I've got queries and code written to update a customer name table. I use a "make table" query to dump the new name changes into a temp table and then an update query takes those changes in the temp table to update the actual table.

Everytime I run the VBA for this, the temp table gets dropped into the "unassigned objects" folder. Not a huge deal but I prefer every thing saying tidy on the sidebar. Any way to fix that? Here's the VBA Code I use to run all the queries.

Private Sub CommandNames_Click()

Dim wdShell As Object

On Error GoTo ImportIt_Err
              
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "qryNamesFixes"
    DoCmd.OpenQuery "qryNamesChanges"
    DoCmd.OpenQuery "qryCapsFix"
    StrResponse = MsgBox("Process Complete!")

ImportIt_Exit:
    Exit Sub

ImportIt_Err:
    MsgBox Error$
    Resume ImportIt_Exit

End Sub

 

[Cronk]

Why not update the "actual" table directly?

 

[bastanu]

Instead of one make-table (probably the qryNamesFixes) use a combination of delete\append (qryDeleteTmpNameFixes and qryAppendToTblNameFixes); another advantage of doing this is that it allows you to add unique indexes to the temp table which should speed up the consequent update query.

Cheers,

 

[Pat Hartman]

Temp tables cause database bloat and so should be avoided at all costs. It doesn't matter whether you use make table queries to make them or leave the table structure and just delete/append. Both bloat the database. Much of the time, the query itself can substitute without actually having to physically save the data. In my apps, temp tables are usually a resut of data that comes from a different application where they will not allow us direct access to the other application's tables.

To avoid the bloat when I need temp tables, I create a template database with only the table definitions for the temp tables. I make sure the tables are empty and then C&R. Then I save the template to a network folder. The final step in testing is to copy the template to the location where I will want to use it day to day. I open the app and link the tables.

Then I add code that when the process starts, copies the template to the location where I linked the original version. This overlays the existing copy. There is no linking required so your existing queries and navigation pain (sic) will not be impacted.

 

[Cronk]

I agree that temp tables should be avoided and when used, have them in another db. On the rare occasions I have need to resort to temp tables, they have been for complex reports involving a lot of denormalization. I've given users their own temp db in the same folder as the front end and rather than C&R, deleted and then recreated the temp db with temp tables after a user has logged into the system so many times.