Advice on temp tables

[Eljefegeneo]

Data Base has a backend and 6 front end users.

I have set up a form that allows the user to extract names from a table to send out an email blast from the database. Since there are two queries that select the data, I created a table into which these names are inserted. The code first deletes all records in a table from the previous user tblEmailBlast. Then it appends all the newly selected data into it. Works fine.

However, I have now learned that some users will be working from remote locations selecting records to include into the table for use in an email blast. Users use LogMeIn to access the DB on their "work" computer, so all have a separate front end. Presently the table is in the backend so this could possibly cause problems if two users decide to send out the email blast at the same time.

I was thinking that perhaps since this is a temporary table, it should be in each front end not the back end. But before I made any changes I wanted to know if I was setting myself up for future problems. I have searched the forum and it seems that temp tables may cause problems.

Just looking for yeas or nays on this before I rearrange the DB.

 

[MarkK]

I don't see why you need a temp table. Let's say you write a query the selects all the data you need. You are done. If you have two queries that return the same field structure, then UNION them into one query. It will always be less headaches to NOT move data into a temp table, and just write exactly the query you need.

UNION query syntax looks like...

SELECT Field1, Field2 FROM Query1
UNION (ALL)
SELECT Field1, Field2 FROM Query2

...and in this example we are even UNIONing two queries together.

hth
Mark

 

[Eljefegeneo]

Thank you, I will try that.

 

[isladogs]

Agree with Mark.

Don't use temp tables where its unnecessary.

However at times they can be very useful.
For example, sometimes an update query isn't possible where the source data is based on a query.
In that case, first create a temp table, then run the update based on the temp table & finally delete the temp table

However creating & deleting tables repeatedly will cause database bloat.
Compacting of course deals with that issue (but backup first)

 

[moke123]

As ridders pointed out database bloat in using temp tables, another option is to use a temp Database. I often create a temp database upon startup and then make tables as needed. In closing the app I then delete the temp database.

 

[Eljefegeneo]

I thank you all for your input. I haven't had a chance to try the union query, but that may be the answer.

However, perhaps I misspoke when using the term temp table. Actually it is a permanent table that merely holds the temporary data. One query selects all names form table1 based on a specific criteria that also says the selected names have an email address. The second query selects almost the same data from a related table with an added criteria that their "position" is only certain categories. The position field is not in table1. The table with the temporary data then permits the user one final edit of the records in the table.

 

[Eljefegeneo]

I tried the union query and it works fine,. will have to thing about what I need to do to make this user friendly.