Moving data from one access table to other access table of different access file

[sathyaganapathi]

Hi,
I have a access table with more than 50k lines in it already and the size is growing every day. Every month nearly 5k new records will get added to the table.
Due to this the size of the file is keep growing. Currently the size is over 50mb. Due to this it is taking lot of time to add a record or update a record.
Is there any possibility to archive the data to a different access file so that, the live file will have less data in it and transaction will be faster?
Moving file once the activity is complete ("Lot cleared") is ok or moving data monthly is also ok for me. But, how this can be automised? Can somebody give step by step for this please ?

 

[Joe4]

Access database can grow up to 2 GB in size.

Regarding performance, do you have Indexes on the key fields in your table?
That will help with performance.

Also, it is good to regularly run a "Compact and Repair" on your database, especially if you are deleting data.
That keeps the database running "lean" (as deleting data from the database does not actually remove it or reduce the size of the DB until you compact your DB).

Note that you can also link outside data into your database (it isn't necessary to actually import it).
So the data could be stored in other location, like other Access databases, SQL databases, Excel files, etc.

If there is old data in your database that you really don't need to account for anymore, you could archive the old data into another table (or database), to keep the table you are working with smaller and more efficient (if you end up moving data around like this, make sure you do the "Compact and Repair" step regularly!).

So those are just some different ideas on how to keep your database running efficiently.

 

[sathyaganapathi]

Access database can grow up to 2 GB in size.

Regarding performance, do you have Indexes on the key fields in your table?
That will help with performance.

Also, it is good to regularly run a "Compact and Repair" on your database, especially if you are deleting data.
That keeps the database running "lean" (as deleting data from the database does not actually remove it or reduce the size of the DB until you compact your DB).

Note that you can also link outside data into your database (it isn't necessary to actually import it).
So the data could be stored in other location, like other Access databases, SQL databases, Excel files, etc.

If there is old data in your database that you really don't need to account for anymore, you could archive the old data into another table (or database), to keep the table you are working with smaller and more efficient (if you end up moving data around like this, make sure you do the "Compact and Repair" step regularly!).

So those are just some different ideas on how to keep your database running efficiently.

Hi Joe4,

Thanks for the quick inputs on maintaining database. It is really helpful...
Concerning the Indexing, I didnot do any indexing in the table. Does it help to improve the Data entry / update into table? Now operator is observing that the time taken to enter new data or updating a record is taking time. Please advise..

I keep regularly running the "compact and Repair" to keep the database lean as you mentioned.

On last point on archiving old data, how this can be done based on time period like, monthly, weekly or "completed"? If I can do some macro / rule to do that, that will be helpful.. please advise..

 

[Joe4]

Sometimes, just setting primary keys is all you need to improve performance. Other times, creating indexes will help.
Here are a few articles on the topic (there are many others):

Create and use an index to improve performance - Microsoft Support

Index one or more table fields in Access by using the table designer. Also covers automatic index creation, viewing, and deleting.

support.microsoft.com

MS Access - Indexing

MS Access - Indexing - An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses...

www.tutorialspoint.com

Regarding archiving the data, it should not be too diffcult. You can create queries with dynamic filters, i.e. archive anything more than 90 days old, or whatever (that condition would look something like this):
< Date() - 90
under the date field.

You could then do it in a series of two Action Queries.
1. An Append Query to write the records that meet your criteria from your original table to your archive table
2. A Delete Query to then delete those same records from your original table

You could then add the steps to open/run those queries back-to-back in a simple Macro. So then all you would need to do is to click the Macro to run both of these steps.

 

[sathyaganapathi]

Sometimes, just setting primary keys is all you need to improve performance. Other times, creating indexes will help.
Here are a few articles on the topic (there are many others):

Create and use an index to improve performance - Microsoft Support

Index one or more table fields in Access by using the table designer. Also covers automatic index creation, viewing, and deleting.

support.microsoft.com

MS Access - Indexing

MS Access - Indexing - An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses...

www.tutorialspoint.com

Regarding archiving the data, it should not be too diffcult. You can create queries with dynamic filters, i.e. archive anything more than 90 days old, or whatever (that condition would look something like this):
< Date() - 90
under the date field.

You could then do it in a series of two Action Queries.
1. An Append Query to write the records that meet your criteria from your original table to your archive table
2. A Delete Query to then delete those same records from your original table

You could then add the steps to open/run those queries back-to-back in a simple Macro. So then all you would need to do is to click the Macro to run both of these steps.

Hi Joe4,
Thanks again for the input. I did the indexing for one specific field now, that is the 'primary key' and 'completion date'. Everytime the vba program checks for the open records and displays on the screen. It used to take long time till now. now, I see there is some improvement in the display of the list. This is simply great.. I will check this for some time..
Concerning the archiving, I need to work on that. I will try some condition and try to create append and delete query..
Will update after trial.
Thanks again..