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,

 

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