 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / September 2003What is the best method of handing Temp Tables?
I have a Front End and Back End, both of which are on the network in the same directory. The Front End contains all the temp tables used. Questions: 1. Should TempTables be in FE, BE, separate .MDB? 2. Should they be deleted immediately? Or is it appropriate to let them be written over the next time it's used? 3. Should the use of TempTables be avoided if possible, or does it not make a difference? 4. Do TempTables affect performance in any way? 5. I have found that performance is better with the FE located on the network as opposed to locally. It seems like this shouldn't be the case. Any ideas? Thanks! Diane >I have a Front End and Back End, both of which are on the >network in the same directory. Each user should have thier own copy of the FE. See the Auto FE Updater downloads page at my website to make this relatively painless.. It also supports Terminal Server/Citrix quite nicely. ?The Front End contains >all the temp tables used. > >Questions: >1. Should TempTables be in FE, BE, separate .MDB? Separate but somewhere on each users local drive. >2. Should they be deleted immediately? Or is it >appropriate to let them be written over the next time >it's used? <shrug> I create the temp MDB, and tables when I need them and delete them as soon as I'm done. Creating an MDB and tables via code is very quick. >3. Should the use of TempTables be avoided if possible, >or does it not make a difference? Where appropriate they make a lot of sense. I use them in a number of places. One area is when importing complex data that needs to be manually verified before appending to current data. >4. Do TempTables affect performance in any way? No. Also see the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. >5. I have found that performance is better with the FE >located on the network as opposed to locally. It seems >like this shouldn't be the case. Any ideas? The three most common performance problems in Access 2000 are: - LDB locking which a persistent recordset connection or an always open bound form corrects (multiple users) - sub datasheet Name property set to [Auto] should be [None] - Track name AutoCorrect should be off For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at http://www.granite.ab.ca/access/performancefaq.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Hi Tony, Thanks for the wealth of information, I did spend quite a bit of time on your website. Regarding TempTables, I do want to make sure that we're on the same page. I make extensive use of "make-table" queries (which I refer to as TempTables) that are used as record sources for reports, some drop down boxes, etc. I do this mainly because of the complexity of the queries and to minimize the number of "embedded" subqueries that otherwise would be required. With this scenario, is it being suggested that I first create these tables in a separate .mdb, link to them from the FE, append the data (as opposed to a make table into the FE), delete the link, the temp.mdb, and do this again for the next report? I not trying to be stupid about this, but it seems like a lot of work, and I think I'm conceptually misunderstanding the process. Let me know what you think. Thanks for all your assistance and expertise! >-----Original Message----- > [quoted text clipped - 57 lines] >http://www.granite.ab.ca/accsmstr.htm >. Diane, First, I agree with Tony that the front-end app should reside on the client machine. This avoids lots of issues, especially if you dynamically create temp tables. His front-end updater, or even a simple batch file xcopy implementation, can facilitate client updating. Second, I agree with you that creating a separate db for temp tables seems like a lot of unnecessary work. I, too, utilize temp tables, but do so in the client app. I see no compelling need to use a separate database. Sure, the front-end app will bloat in size, but does that matter? If you simply delete the contents of the temp tables, or the tables themselves, then a db compact & repair will easily solve this if needed. I'd be interested to know why the temp tables should be stored externally. Reid Kell > Hi Tony, > [quoted text clipped - 21 lines] > Let me know what you think. Thanks for all your > assistance and expertise! >Second, I agree with you that creating a separate db for temp tables seems >like a lot of unnecessary work. I, too, utilize temp tables, but do so in [quoted text clipped - 3 lines] >compact & repair will easily solve this if needed. I'd be interested to know >why the temp tables should be stored externally. To each thier own. <smile> I feel that the little bit of extra work, now that I have some code I can use quite generically, is worth it compared to having an FE MDB/MDE that can bloat a number of Mbs over months and months. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm It's not a lot of work and it reduces or eliminates the need to frequently compact and repair, which saves a lot of work. No, you create a temporary database, use CopyObject to create the tables from empty "template tables" in your database, link to them, use them, and then when you are done, unlink, and use the Kill statement to delete the whole temporary database. Larry Linson Microsoft Access MVP > Hi Tony, > [quoted text clipped - 100 lines] > >http://www.granite.ab.ca/accsmstr.htm > >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.