Temp Tables

[jsic1210]

Hello,
I'm looking into storing query data in temp tables to help my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?

And how bad is it to keep the databases linked at all times?

Any help is appreciated!

 

[namliam]

creating temp tables is hardly a good way to make reports "run better"
If anything it adds overhead and should/will slow down reporting!

 

[jsic1210]

Yes, I know it's not ideal. My reports have actually been running fine. But when I print them to PDF, much of the info is missing in the PDF. I usually print it using an OutputTo command in VBA. But even manually printing to PDF causes the same problem, and manually I receive an error "cannot open any more databases."
So I manually created tables and backed the report with these tables. The problems went away.

 

[smig]

I do not recommend using temp db and temp tables for storing data for reports.

you can use recordsets to save the data into the temp tables in an unlinked db.

Dim Tempdb As DAO.Database
Dim rsTempTable As DAO.Recordset
Dim Tempdb_Path As String

Tempdb_Path = "C:\Fullath\Tempdb .mdb"

Set Tempdb = OpenDatabase(Tempdb_Path , False, False, "MS Access;PWD=")
Set rsTempTable = Tempdb.OpenRecordset("SELECT * FROM [TempTable]")
....
....
....

rsTempTable.Close
Set rsTempTable = Nothing

Tempdb.Close
Set Tempdb = Nothing

 

[jsic1210]

Awesome! I will give this a try. Thanks for your help.

 

[jsic1210]

From here, how do I set the data source of the report? I think I'm still backing the report by the temp table, right? But how do I set the recordset to that specific temp table?
Also, will I run into a problem scrolling through the report in print preview since I'm setting rsTempTable to nothing at the end? (i.e. will the backing data disappear?)

 

[RainLover]

From here, how do I set the data source of the report? I think I'm still backing the report by the temp table, right? But how do I set the recordset to that specific temp table?
Also, will I run into a problem scrolling through the report in print preview since I'm setting rsTempTable to nothing at the end? (i.e. will the backing data disappear?)

First of all why do you set to nothing. There is no reason why you should do so.

Ad I agree with your approach. I think it is called warehousing.

Bloating will happen when you delete data and then replace it. You may do this many times before it causes a problem but the same problem will happen if the Temp table is in the front end or a separate backend.

I would suggest using the front end or if you go the way of a separate Database then place that on the user drive so that it is exclusive to that current user. This is especially important when the data is filtered or calculations are made as it is copied. This will ensure in the case of multi users each has their own dataset which can be different.

able when finished rather that remove the data you should delete just the data.

Also if you run multiple reports then the data is available to you because it is already there for you.

I have done this over a slow network with the result that I would have to wait for the temp table to built but then the reports were as quick as grease lightning.

 

[jsic1210]

Rainlover,
I have a couple questions.
1) Using the code provided by smig, I should remove these lines?

rsTempTable.Close 
Set rsTempTable = Nothing  
Tempdb.Close 
Set Tempdb = Nothing

2) I'm still unsure how to have my report backed by this temp table/recordset. In the code, I don't see where I'm setting a specific table equal to the recordset created. Am I missing something?

Thanks.

 

[RainLover]

Rainlover,
I have a couple questions.
1) Using the code provided by smig, I should remove these lines?

rsTempTable.Close 
Set rsTempTable = Nothing  
Tempdb.Close 
Set Tempdb = Nothing

2) I'm still unsure how to have my report backed by this temp table/recordset. In the code, I don't see where I'm setting a specific table equal to the recordset created. Am I missing something?

Thanks.

Most other players disagree with my suggestion. They say the sky will fall. I have challenged, and the challenge is still open for anyone to prove their point of view by creating an error. But they can't.

So yes delete all four lines.

Am I missing something?

If you create a separate Temp table you treat it the same as any other table.

In the Properties box you can set the Data Source there to equal the Temp table.

Not sure if this helps.

I am off now as it is 2:00 AM here.

 

[jsic1210]

Rainlover,
Thanks for your help, but I have to admit, I'm a little lost. I'm not sure if you're building off smig's approach or scrapping that approach completely.
I'm getting that I back the report with the temp table, but where in the code is the table being created? I see the recordset is created, but how do I move the recordset to this temp table? And if I'm deleting the data from the table each time, why isn't this causing bloating? Or am I completely deleting the table each time?

Sorry for so many questions!

Jeff

 

[RainLover]

I need to add to my previous posts, but I am going out for a bit.

I should be back on line for when you return.

You could investigate for yourself a Make Table Query.

This will build a table for you which you can use as a Query or SQL in Code.

 

[Galaxiom]

and to break the links to avoid bloating of the FE database.

Linked tables do not bloat a database. There is nothing wrong with leaving them linked.

I do not recommend using temp db and temp tables for storing data for reports.

you can use recordsets to save the data into the temp tables in an unlinked db.

I must be missing something here. After saying this you proceed to show code involving a temporary database.

Then you show the creation of a recordset from it. I don't see the point of that in relation to creating a report (the OP's goal) since (unlike a form) a report does not support the use recordset as its data source.

 

[RainLover]

Rainlover,
Thanks for your help, but I have to admit, I am a little lost. I'm not sure if you're building off smig's approach or scrapping that approach completely.
I'm getting that I back the report with the temp table, but where in the code is the table being created? I see the recordset is created, but how do I move the recordset to this temp table? And if I'm deleting the data from the table each time, why isn't this causing bloating? Or am I completely deleting the table each time?

Sorry for so many questions!

Jeff

I am not using anyone's approach except mine.

I move the recordset to this temp table? I do not know what you mean by moving a recordset. Please explain.

Bloating can be a lot or just a very little. I will not go any further into this at this stage. The important point is that it should be prevented. If not, things will slow down and eventually come to a point where it is unusable.

Two main ways that I know of to prevent this.

The first is to create a Temp Database and then create a new Database each time. There is a fair bit of work to do it this way and I see no advantage.

What I do is to create a Temp Table using the query type of Make Table. Each time I need to refresh the contents I delete the Table and create a new one.

I think Galaxiom could have something creative to add to this. He is well versed in this type of thing.

 

[smig]

If you want the temp tables to be used for reports save them as part of the FE.

Do Compact and Repair on Exit to remove unused indexes and compact the db.

 

[RainLover]

Do Compact and Repair on Exit to remove unused indexes and compact the db.

It is advisable to back up before doing a Compact and Repair as sometimes this can crash the Front end.

 

[RainLover]

Hello,
And how bad is it to keep the databases linked at all times?

Any help is appreciated!

Who said that this was bad. Microsoft in North wind does not worry about this.

I do it all the time. Permanent Links that is.

BTW once a front end is linked and a password is used then there is no need to re enter the password. Once it has been accepted it will remain, even after closing down of everything.

 

[jsic1210]

Okay, so what I'm understanding is that when opening the report, the code should first use a make table query, creating a table in the FE database that backs the report. When a user closes out of the report, the temp table is deleted?
My other thought was that I'd keep the table structure in a linked database that users save from a shared drive to their desktop. When the report is opened, all data is deleted from the table, and new data inserted, then the report opens. In this, if the user's linked database ever became bloated, he/she could just save the database in the shared drive over it. The database in the shared drive with the temp tables shouldn't get bloated as data is never being added to it.
Should I go with the first option of make table query?

 

[RainLover]

Okay, so what I'm understanding is that when opening the report, the code should first use a make table query, creating a table in the FE database that backs the report. When a user closes out of the report, the temp table is deleted?
My other thought was that I'd keep the table structure in a linked database that users save from a shared drive to their desktop. When the report is opened, all data is deleted from the table, and new data inserted, then the report opens. In this, if the user's linked database ever became bloated, he/she could just save the database in the shared drive over it. The database in the shared drive with the temp tables shouldn't get bloated as data is never being added to it.
Should I go with the first option of make table query?

Very bad Computer Talk. "Back" The Data is the Source for a Text Box or a Combo Box etc.

Or a Record, Lots of fields, would be the Record Source for a Form or Report. You will see this in the Properties box for each object and will use it frequently when designing. So let's drop the Back.

so what I'm understanding is that when opening the report, the code should first use a make table query, creating a table in the FE database that backs the report. When a user closes out of the report, the temp table is deleted?

Make the table is correct as you stated except for a little oversite. If you have an automatic front end Updater then you will loose the Data in the Temp Table. This is why I would tend to use a separate Database located in the same Folder as the Front End.

It is not so much the adding of data that causes bloat rather than the repeated deletion of the Data and then repopulating. I would delete the whole Table and replace it as required.

Have a go at this as we are very close to each other. Please let me know if you have problems with this approach.

 

[RainLover]

Here is an advantage that we have not mentioned as yet.

If we were not using a Temp Table then the running of your Report could be quite slow. It may have Calculations to do and filter and Sorting.

This is because when we build a Database we use Normalisation.

If you use a Temp Table and as it is not used by anyone else you can denormalise.

You may end up with several more fields, but this is good when running the report.

I have used this in isolated areas where the main server is 100 miles away.

So we design the Temp Table, have a coffee and when complete you can produce reports in a very fast manner.

Just a thought.