How do we historicize some rows in data tables? (2 Viewers)

[Ken Sheridan]

CJ's question is valid and I didn't originally take that interpretation. Are you storing the actual document in the DB file or merely linking to it as an external file?

Absolutely, the same had occurred to me. If large numbers of documents are being stored as attachments, then it's not surprising they have problems. Storing the documents in a folder and using the database as the means of retrieval by storing the paths as text would be the obvious solution if this is the case.

 

[amorosik]

Moving rows to a separate file should only be a method of last resort. If the size of the back end is pushing the limits of Access, it's time to move to SQL server or another client server database as the back end.

I didn't specify the type of database used
And there's no need to do so.Because the problem is identical regardless of the database type used
It could be SQL Server, Oracle, DB2, Postgresl, ... it doesn't change anything
I categorically rule out the possibility of using an mdb/accdb file to maintain reliable data.

 

[amorosik]

Absolutely, the same had occurred to me. If large numbers of documents are being stored as attachments, then it's not surprising they have problems. Storing the documents in a folder and using the database as the means of retrieval by storing the paths as text would be the obvious solution if this is the case.

No attachments are stored in the main database

 

[isladogs]

Almost 20 years ago I made the single biggest mistake of my coding career.
In my largest schools database, I decided to move the data for all student leavers (those transferring to another school) into separate tables rather than mark them as leavers.
I also decided to create archived tables (and therefore archived leaver tables) for all students who left after their final exams.
The tables were in SQL Server so it wasn't done for reasons of database size. It was mostly done to keep the tables containing data for the currently active students a 'manageable size'. Big mistake.

That decision initially required about 60 student tables to be re-created as archived tables, leaver tables and leaver archived tables, transferring and then deleting the data in the original tables. That meant 4 sets of each table.
When data was required for those student leavers or archived leavers, it could easily be retrieved.
So far so good.

However, any structural changes subsequently made to the main tables, also had to be applied to each of the other tables as occasionally students returned to the school at a later date. This happened several times each year.
If I had just marked the students as leavers, none of that would have been necessary.

It sounds like you are considering making a similar mistake.
I would strongly advise against doing what you suggest. Few (if any) benefits. Many disadvantages.

 

[KitaYama]

"..Data doesn't take up too much space on disk.." this has no bearing on the request.

My bad. I had forgotten that you prefer replies to be limited to exactly what you ask, rather than suggesting a better method, even if the root of the problem is that your approach itself may not be the best database practice.