Microsoft Access - Temporary Tables

Hi Guys,

I'm looking for a bit of advice here.

The current front end application has a form that changes the SourceObject in the on open event, depending on what button the user presses.

This takes time and space (numerous forms in the FE), so I'd like to change this by setting the forms SourceObject to a temporary table.

When the user presses a button, a temporary table is created with using 
  DoCmd.RunSQL "Select * into tblReportData from " & qry

The form opens displaying the data and this is much quicker than changing the SourceObject

My question is this. Will this cause bloating in the Front End? Should I delete the temp table when the form closes, or just write over the previous table using the above method?

Any ideas?

Thanks

Pete

If the table structure is same, instead of dropping table and re-creating again, use the same table.

Hi,

Thanks for the reply,

The table structure could differ depending on what the user needs to display.

As this is the case, would dropping the table help reduce any bloating?

Thanks again

Your query does not seem to indicate that the "tblReportData" is a temporary table. It is more like a staging table. It should not bloat your UI, unless you are not writing more and more records into the same table per request. 

Yes, clearing the staging table in the Form Close events would definetely help. In the close event handler, set the RecordSource to empty and delete the tables. 

'Clear the record source.
Forms!DataEntryForm.RecordSource = ""
'Close the form object.
DoCmd.Close acForm, "DataEntryForm"
'Delete the tables
DoCmd.DeleteObject acTable, "PrimaryInfo"

Hope this helps.

hi,

You're using CREATE as if this is straight SQL, not Access SQL. You'd use a MAKE TABLE to make a table and then on the DB's Close Event, you'd delete that table (DoCmd.DeleteObject).

creating and deleting a table locally:

 
    Dim SQLCreateTempTable As String
 
   Dim SQLDeleteTempTable As String
 
   SQLCreateTempTable = "CREATE TABLE tblTempPartSearch (partnum TEXT(50), partdescription MEMO, " & _
 
        "searchword TEXT(8), typecode TEXT(1), classid TEXT(4), Description TEXT(30))"
 
   SQLDeleteTempTable = "DROP TABLE tblTempPartSearch"
 
   DoCmd.RunSQL SQLDeleteTempTable

Regards,

Have you timed this?  Are you certain it is faster?  Why are you changing the RecordSource?  Could you use a query with criteria that is provided by another form?  For example, select a customerID on formA and then open formB showing the selected customer?  I need to know more about what you are trying to do before offering a firm solution.  I do have a couple of forms where I replace the RecordSource so I can apply criteria.  Filters only work if your form is based on a table or an unqualified query.  Since my apps are usually linked to non-Jet back ends, this is generally poor practice since the form just sits there sucking data over the network until it has downloaded every record in the form's RecordSource.  Therefore, to give the user filtering options, I build SQL strings in code (when the criteria is complicated) and then replace the form's RecordSource.  When the criteria is simpler or only involves a couple of fields, I just use parameters to supply the criteria values at runtime (this is the most efficient method).  In this case, the default RecordSource selects a row with the PK = -1 which returns nothing.  The user selects his filters, the code builds the SQL string and replaces the RecordSource.  BTW, this is the ONLY time I use embedded SQL (which itself causes bloat).  In all other cases I use saved querydefs or queries saved directly in the RecordSource since they are more efficient.

To answer your bloating question,  the answer is yes.  Deleting a table and recreating it or deleting all the rows and appending different ones have similar effects.  Access cannot recover the space used by the previously stored records until the db is compacted.  If you do it a few times while the db is open and there are not a lot of rows involved., you probably won't see much bloat but if you do it a hundred times, you will see the bloat.  You will need to set the FE to compact on close if you use this technique.

When my criteria is compound but simple (multiple optional fields connected by AND), I usually build a querydef that just takes parameters.  Here is a simplistic example.

Select ... From ...
Where (FldA = Forms!myForm!FldA  or Forms!myForm!FldA Is Null) AND (FldB = Forms!myForm!FldB  or Forms!myForm!FldB Is Null) AND (FldC = Forms!myForm!FldC  or Forms!myForm!FldC Is Null) AND (FldD = Forms!myForm!FldD  or Forms!myForm!FldD Is Null) AND ...

Notice how the parentheses are used in the above Where statement.  Since the criteria includes both AND and OR, you MUST use parentheses so that the criteria will be evaluated correctly.  The OR's are used to allow the criteria to be optional so the user can specify (A and D) or C or (B and D), etc.  Whatever.  If you want to require one or more criteria to ensure that the user doesn't select all the records, add a final AND clause similar to the following:
(IIf(IsNull(Forms!myForm!FldA), 0, 1) + IIf(IsNull(Forms!myForm!FldB), 0, 1) + IIf(IsNull(Forms!myForm!FldC), 0, 1) + IIf(IsNull(Forms!myForm!FldD), 0, 1) >= 1)
 

Hi [)ia6l0 iii,

This does help and thanks for the reply.

Cheers

Pete

Hi Kalpana,

Sorry for taking so long to get back, it's been a busy few days.

I'm must admit, SQL is not my strong point and I'm not sure what difference there is between CREATE and MAKE TABLE.

Both the way DoCmd.RunSQL "Select * Into " method followed by the DoCmd.RunSQL "DROP TABLE " method work well, what's the difference between the two methods?

Thanks again

Pete

Hi Pat,

 

This is definitely much quicker.

 

The record source was changing on a subform. The main form displays information about the data whilst the sub form displays the data table. Originally the subform record source changed to a dedicated form for that set of data, and could take anywhere from 7 to 15 seconds to load. Setting the record source in the onLoad event to a dedicated table only takes a couple of seconds max.

The reason I’m asking is because when I originally built the system it was to a tight deadline and my general knowledge of Access, SQL & VBA wasn’t that great. The system works well, but I know it could be lighter and faster. Since then my general knowledge has greatly improved and I'm trying to make the whole thing more efficient whilst applying further updates.

Although I didn’t realise it at the time, most of the bloat occurs because a majority of the queries are built on the fly through VBA. I built it this way because the number of related queries became difficult to manage.

 

The FE is already set to compact on close, and I’ll look at building a querydef for use with some of my queries.

Pete

Your query does not seem to indicate that the "tblReportData" is a temporary table. It is more like a staging table. It should not bloat your UI, unless you are not writing more and more records into the same table per request.

That statement is incorrect.  Deleting a table by dropping it and recreating it again has the same impact on a databas as emptying a table by deleting all rows and then running an append query to add new rows.  The space occupied by the data or table that was deleted remains as unused space (BLOAT) in the database because Access cannot recover the now free area on the fly.   The only way to recover it is by compacting the database.