UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dealing With Temp Tables, Access 2010    
 
   
SemiAuto40
post Apr 9 2019, 04:06 PM
Post#1



Posts: 692
Joined: 3-April 12
From: L.A. (lower Alabama)


It seems to work out best for me if I use a local temp table in the front end of my DB. I believe I am having some kind of timing issue that does not remake my temp table when I want it done on the form load. The delete object removes them but I don't see them being remade. I am supposed to have some more code and define the table before doing the make table query code? See the code below.
CODE
Private Sub MyForm_OnLoad
   Dim DB As DAO.Database
   Dim strSQL As String
        
       'DELETE TEMP TABLE FIRST.
       DoCmd.DeleteObject acTable, "tbl_TempChemicalsList"

       'BUILT IN DELAY APPARENTLY NECESSARY.
       WaitSeconds (2)
       'REMAKE TABLES WITH MAKE TABLE QUERIES.
       Set DB = CurrentDb()
       strSQL = "SELECT tbl_Chemicals.Chemical_ID, tbl_Chemicals.ChemicalName, tbl_Chemicals.CASnumber " & _
                "INTO tbl_TempChemicalsList FROM tbl_Chemicals " & _
                "ORDER BY ChemicalName ASC;"
       DB.Execute strSQL
       Set DB = Nothing


Thanks in advance.
This post has been edited by SemiAuto40: Apr 9 2019, 04:07 PM
Go to the top of the page
 
theDBguy
post Apr 9 2019, 04:11 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,527
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you’re having issues with this approach, why not just empty out the temp table using a DELETE query and repopulate it again using an APPEND query?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Apr 9 2019, 04:12 PM
Post#3



Posts: 5,956
Joined: 11-November 10
From: SoCal, USA


i'm wondering why you're deleting the table object and then recreating it, instead of just deleting all records and then appending new records. either process will bloat the database.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
DanielPineault
post Apr 9 2019, 05:38 PM
Post#4


UtterAccess VIP
Posts: 6,656
Joined: 30-June 11



I agree with Tina.
Also, to avoid bloating, you might want to consider having temp tables in a separate be that gets flushed at the end of a session.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
SemiAuto40
post Apr 10 2019, 08:47 AM
Post#5



Posts: 692
Joined: 3-April 12
From: L.A. (lower Alabama)


I had the impression that deleting the tables caused little bloat vs the delete/append approach. I would appreciate more detail on this. If they are similar then I will go with whichever is recommended. I'm trying the auto compact when the front end closes.

After I remake the tables I am not seeing them where the other tables are listed initially, which makes me not think they are there ready to use. After I do some operations or maybe just a function of time - the tables appear in the list. I'm confused why Access does this. My users are not able to view the tables list or other objects in the Access Objects Panel, however I'm not convinced the new temp tables are getting remade right away for users fast on the mouse.

Thanks.
This post has been edited by SemiAuto40: Apr 10 2019, 08:58 AM
Go to the top of the page
 
ADezii
post Apr 10 2019, 09:06 AM
Post#6



Posts: 2,373
Joined: 4-February 07
From: USA, Florida, Delray Beach


It may be as simple as the DB Window needing a Refresh after creating the Table (last line in Code):
CODE
Dim DB As DAO.Database
Dim strSQL As String
        
'DELETE TEMP TABLE FIRST.
DoCmd.DeleteObject acTable, "tbl_TempChemicalsList"

'BUILT IN DELAY APPARENTLY NECESSARY.
WaitSeconds (2)

'REMAKE TABLES WITH MAKE TABLE QUERIES.
Set DB = CurrentDb()
strSQL = "SELECT tbl_Chemicals.Chemical_ID, tbl_Chemicals.ChemicalName, tbl_Chemicals.CASnumber " & _
         "INTO tbl_TempChemicalsList FROM tbl_Chemicals " & _
         "ORDER BY ChemicalName ASC;"
DB.Execute strSQL
Set DB = Nothing

RefreshDatabaseWindow
Go to the top of the page
 
Minty
post Apr 10 2019, 09:09 AM
Post#7



Posts: 309
Joined: 5-July 16
From: UK - Wiltshire


If you are looking for the remade tables in the navigation panel, then often new ones appear at the bottom rather than being correctly sorted, (whichever sort you might have applied).

The table should be available instantly after creation.
I use temp tables for importing external data all the time and delete / re-import, run some updates then open the results all in one process without any need to pause.
Go to the top of the page
 
theDBguy
post Apr 10 2019, 10:09 AM
Post#8


Access Wiki and Forums Moderator
Posts: 75,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

QUOTE
I would appreciate more details on this.

Take a look at this blog. Unfortunately, the link to the demo download is currently broken. However, an updated version is available on another site.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
SemiAuto40
post Apr 10 2019, 10:28 AM
Post#9



Posts: 692
Joined: 3-April 12
From: L.A. (lower Alabama)


Thank you DBguy hat_tip.gif I remember seeing this blog of yours before, and that is why I started by using Make Table instead of Delete/Append of a temporary table (I just couldn't remember why I chose this first... it was because you showed it to be quicker).
So now this site calls the Make Table query lazy -> http://www.fmsinc.com/microsoftAccess/quer...pend-query.html . I just need fast and safe (don't we all).
Go to the top of the page
 
theDBguy
post Apr 10 2019, 10:55 AM
Post#10


Access Wiki and Forums Moderator
Posts: 75,527
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just one person's opinion but a "lazy" query sounds to me like a perfect fit for a "quick and easy" situation like a temporary table. It really depends on your particular situation. All the caveats mentioned for using a Make-Table query are valid. So, if they apply to your situation, then you need to make a decision.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post Apr 10 2019, 12:15 PM
Post#11


UtterAccess Editor
Posts: 10,006
Joined: 7-December 09
From: St Augustine, FL


Regarding the separate BE approach (which is my preference as well: keeps everything out of the frontend file), something like this perhaps:

Create a database in the same directory as your application, called sideload.accdb (or whatever you want, sideload is what I use).

Create the temp tables you want in there, link them up in your FE database.

Adopt a naming convention for your sideload tables. I use a suffix of either Temp or Cache, depending on the purpose (e.g., EmployeeBonusCalcTemp, EmployeeCache, etc).

When your application starts, relink your sideload (see code fig 1)

When your application starts (or stops, or both: start is easier to catch), clear the Temp/Cache tables (see code fig 2)


(aircode)
fig 1
CODE
Public Sub RelinkSideload()
Dim db As DAO.Database, td AS DAO.TableDef
Set db = CurrentDB()

For Each td In db.TableDefs
  If Left(td.Name, 4) = "Temp" Then
    td.Connect = CurrentProject.Path & "\sideload.accdb"
    td.RefreshLink
  End If
Next td
End Sub


fig 2
CODE
Public Sub ClearAllTempAndCacheTables()
Dim db As DAO.Database, td AS DAO.TableDef
Set db = CurrentDB()

For Each td In db.TableDefs
  If Left(td.Name, 4) = "Temp" Then
     CurrentDb.Execute("DELETE FROM " & td.Name & ";", dbFailOnError
  End If
Next td
End Sub

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 07:02 AM