duplicate save query (1 Viewer)

[NickNeville]

Good morning all
I wonder if anyone could tell me please if I can save data to a duplicate table (with a different name)
I have below taken from the Save button and wondered if I could add something to save to the other table at the same time ? My new table is Scores2. Many thanks
Nick

p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; } Private Sub Command52_Click()
On Error GoTo Err_Command52_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command52_Click:
Exit Sub

Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click

End Sub

 

[namliam]

no no no and NO

You should NOT duplicate data unless you have a very very very very very very VERY good reason to do so...

 

[Galaxiom]

namilam's comment was too moderate.

NO, NO, NO and NOOOOOOOOO.


Search the forum for normalization and ensure you understand it before you continue with your database design.

Databases should be designed in a way where all values can be derived from the same foundational records. Once you duplicate a value or transaction in different places there is a risk of losing synchronisation and coming up with different values for the same measurement by different queries.

 

[NickNeville]

Oh well I guess that's a no then ! ha ha.

If I say want I am trying to do then hopefully there will be a solution. Basically
I have a table with info that is deleted at the end of the period (day) but I want to save this data elsewhere where I can retrieve it at a later date.

Thanks for the quicko replies !

Rgds
Nick

 

[Galaxiom]

Are you sure you need to delete the data? New developers often underestimate the capability of databases to efficiently manage millions of records and can be too keen to archive data unnecesarily.

Often a field to flag a record as deleted is sufficient to hide it away by simply including Not Deleted in the query.

 

[namliam]

Or... if you feel you want to delete the data, you can run an append query to copy it from your current table to the archive table... that way you dont have (direct) duplication but your actually "archiving" the data

 

[NickNeville]

Yes that's one one my fears, that access has limits and when one talks of millions of records it seems almost impossible to comprehend. I think I will go down the "append" route as that seems a good way. But can I refer to that append table in a query without fear of the duplicate data upsetting things.?

Many thanks all for your brill help
Nick

 

[Galaxiom]

when one talks of millions of records it seems almost impossible to comprehend.

It blows me away too. I have a table I have let grow to see when the database starts to struggle. Now at 7.65 million records and something like a dozen fields. Still running fine and well short of the 2GB Access size limit.

However one does need to be intelligent about datatypes, indexing and ensuring the queries are sensible in that they don't retrieve them all at once.

But can I refer to that append table in a query without fear of the duplicate data upsetting things.?

If you are wanting to query those records they are really best left where they are. Adding an indexed boolean field for deleted is a very safe and effective way to efficiently supress old records. It is easy to design a form with a "Show Deleted" switch.

If you do go down the archiving path I would strongly discourage you from archiving on a daily basis. Archiving means mass deleting records and that should not be done lightly. It is an administrative task, not something you want to happen unsupervised by some automated code at six o'clock.

Get everyone out of the system. Do a full backup. Do the append query. Check that it succeeded. Then delete your old records and back up again.

 

[NickNeville]

Many many thanks everyone, you have been a great help.

superb...

Bets rgds
Nick