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
> Temporary Tables, When And Why To Use Them, Access 2010    
 
   
SemiAuto40
post Feb 15 2018, 10:22 AM
Post#1



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


I have a method editing concept where when an existing method is edited the original method data is preserved and the new data has an incremented revision number. The complication is that I have 2 list boxes on this edit form which interact with a table of chemical names. Obviously I don't want to alter any original method data or chemicals - just the new revision.
What is the right way to go about this - because certainly I have not found it but am causing more grey hairs!

Thanks.
Go to the top of the page
 
LPurvis
post Feb 15 2018, 12:51 PM
Post#2


UtterAccess Editor
Posts: 16,295
Joined: 27-June 06
From: England (North East / South Yorks)


Hi.

First of all, to clarify, is your data relating to some object called a Method? (i.e the forms and table deals with Methods of something or other.)

Next, you don't want to allow edits to the records. Any edit is to be a subsequent row, effectively a copy ofthe original. There is some sort of foreign key I presume, to tie the oriignal and edited versions together (as being related in some way)?
When you make a new copy, it is held locally in a temporary (work) table and then committed?
That is working for you it seems.
However, you also have some other related Chemicals data, which is displayed via some list boxes. Presumably, if the edits are to maintain their own set of chemicals, then you're making copies of those too and inserting a foreign key to the new method record instead of the previous version?

If that's your requirement, I don't seemuch alternative.
A local (work) table to hold the data being edited, and then when a user commits that record, you create the copies in the live tables?
Grey hairs or not, sounds about right.
You could conceivably do the data holding all in memory, and avoid the work tabes, but they would only likely result in more grey hairs. :-s

Cheers
Go to the top of the page
 
projecttoday
post Feb 15 2018, 07:15 PM
Post#3


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


If you're just adding new methods to a table be aware that you don't have to delete the old methods. So as far as the question as to whether or not you need temporary tables for this, well, maybe not. Temporary tables are often used for complicated data retrieval. Some more information would help.
Go to the top of the page
 
SemiAuto40
post Feb 16 2018, 03:43 PM
Post#4



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


A new copy of an existing method must be created in the method table so that I can then SQL to find out what that new primary key number is. I need that number because of the 2 list boxes. When they are manipulated I have to use that new method primary key to store information for that new method copy. At the end of the form is the Cancel button and OK button. If the Cancel is clicked then I have to delete the new copy so that the original is still the current method version. My bet is that if someone opens method edit form then they likely will be making a change. The OK button on the method edit form tidies up a detail like check box that the old method is archived.
I was wondering if somehow doing all of this with temporary tables then at the edit form OK button executing all of the SQL at one time. I'm trying to be safe and not corrupt data - but this has been a bear!
Go to the top of the page
 
projecttoday
post Feb 16 2018, 03:54 PM
Post#5


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


So what is the actual process? Describe what you want the computer to help you with.
Go to the top of the page
 
SemiAuto40
post Feb 16 2018, 04:55 PM
Post#6



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


This is a question of efficacy. If it is safer to do all of this on a multi user database the way I am going about it - or should I try to work it so temporary tables are doing all of the new method copy, and list boxes manipulation, and once the user gets to the OK button then SQL around the data in tbl_Methods and list boxes which select data for tbl_MethodChemicals? Like waiting until all fields are edited and the OK is selected before committing everything to their respective tables. The time when I make a copy of a method but have not yet completed the editing, and perhaps another user edits another different method at a remote site. Presently if the edits are Canceled then I have to go and SQL delete the changes from the tbl_MethodChemicals and SQL delete the method copy from tbl_Methods. Just seems improper, but since I am an amateur I don't really know what is proper.

Thanks.
Go to the top of the page
 
projecttoday
post Feb 16 2018, 06:08 PM
Post#7


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


Is this a response to my question?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2018 - 09:52 PM