Moving data from one table to another

[Bumbino]

My form moves multiple records of data to a temporary table. Before exiting,
the user is prompted to save or cancel these entries. If they choose to
save, the data moves from the temp table to the permanent table and the temp
table deletes the records. If they choose to cancel, the temp table simply
should simply delete the entries. I cannot get this to work. Any help or
ideas appreciated.

 

[Jeanette Cunningham]

You can empty the temp table just before you use it next time.
On your form that moves data to the temp table,
just before you insert the data, you have a line of code to delete all the
records from the temp table.
This way you can be sure that nothing went wrong with the delete that you
did when you finished with the temp table last time you used it.

Jeanette Cunningham

 

[Bumbino]

Thanks again Jeanette. You have answered me before. So maybe when the form
loads, I can delete the records? I was unclear in my post. What I meant to
say is that I cannot get ANY of it to work. I have tried using a SQL
statement in my code that will append the temp records into the perm table
and then another SQL statement to delete the temp records. But so far after
2 weeks of trying, I cannot get any of it to work. I have only been coding
for about 4 months so I am still learning. Thanks.

 

[Allen Browne]

I'm not sure that what you are trying to do is simple in a mulit-user
environment like Access.

You have a temp table with the same structure as your real table.
Let's say you succeed in copying all records from the real table to your
temp table whenever you open your form, like this:
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()

strSql = "DELETE FROM MyTempTable;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO MyTempTable SELECT * FROM MyRealTable;"
db.Execute strSql, dbFailOnError

Set db = Nothing
End Sub

(Note that this needs error handling that cancels the opening of the form if
this operation does not succeed completely.)

Now the user fires away, and you need to figure out how to merge the records
back to the real table afterwards, and handle any conflict with other users
who edited them at the same time.

Consider these cases:
a) User adds new records.
You have to identify which are the new records, and execute an Append query
to add them to the real table. This is a problem, because if another user
deleted a record (which is still in this user's temp table), you are about
to add it back. You also have a problem with ensuring that any AutoNumber
fields give different values to different users who are adding records
concurrently.

b) User deletes records.
You have to identify which records are in the original table, but not in the
temp table, and delete them. Here you have a major problem if another user
has added any records to the original table since this user copied hers to
the temp table, because you are about to kill the other users' new records.

c) User edits records.
You now have the problem of identifying which field(s) of which record(s)
were changed by this user, and which field(s) of the same record(s) were
modified by other user(s) concurrently (i.e. since you copied them from the
original table.) You are going to need additional hidden fields to track who
changed what when, and will need to develop sync. routines that give users
the responsibility to make decisions about whose changes are kept and whose
are overwritten.

In practice, this is not going to work.

You could examine replication as an option, but I could not recommend it. MS
has removed replication from A2007, so you are probably heading down a
dead-end path if you go that direction.

 

[Jeff Boyce]

Why? As in "what will having records temporarily stored allow you to do?"
I'm asking about the underlying business need that you are trying to
satisfy, as there may be other approaches...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[.]

dans l'article (e-mail address removed), Bumbino à
(e-mail address removed) a écrit le 23/01/08 22:58 :

 

[Bumbino]

The form is used by auditors for tracking their time worked on projects. In
any given week, an auditor may work on as many as 7 projects. So my form has
6 subforms so that the auditor can enter their time all on 1 form instead of
doing it 7 different times. Before they exit, I give them the opportunity to
verify their input and save or cancel their entries and exit. Since all of
the entries go to the same table, I cannot simply perform an Undo command and
delete their entries so I have their entries first going to a temp table
until the user verifies the info which at that time I am trying to code an
Append Query that will move the records to the permanent table and code a
Delete Query that will empty the temp table for the next user. I hope this
makes sense. I have only been using Access for 6 months and coding for less
than that, so if there is a better way, I would appreciate any ideas. Thanks.

 

[Jeff Boyce]

Here's another possible approach...

Instead of using a temporary table, then having to work out how to move
records and empty the temp table, you might be able to simply add a
"Verified" (Yes/No) field to the permanent table/record. That way, folks
would do their data entry directly into the permanent table, and you'd only
need to work out how to get their verification on their records.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/