Create and Archive database

[Guest]

Hello,

Does anyone know of a site that would explain in full detail but yet easy
enough to create an archive database that I can dump unused data from the
primary database into with a click of a button?

I have one on a database a developer created by he is no longer available an
I cannot figure out how he did it. But all I have to do is click a Archive
Data button on the primary databases main menu form and it pours the data
into the archive database. I need to do this for another database.

Thanks!!!!

 

[Guest]

Lots of folks just add a field like Active or Archive as a Yes/No fields.
Then in the queries add the field to pull active records.

 

[Guest]

I know, but I don't want these records taken up space in the main database.
I'd rather move them outside to another database within the same folder.

 

[Jeff Boyce]

Why? Do you have an unreasonably large database? What do you consider "too
much"?

Are there performance issues driving your wish to move records?

How will you/your users look up information "across" multiple databases, if
needed?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Why not??? Is there any specific reason why you are against doing this? I'm
not sure I understand. Based on your response, please clarify your reason(s)
other than this is not the way you would handle it. The database is small,
about 35,000 records and maybe 7 or 8 MB. The reason why is just because I
want to learn and know how to do it. This way, I would have accomplished
learning a new task so when I do have an extremely large database I can
create the archive myself which is just more knowledge to gain and expand my
skills. I like to learn and have learn quite a bit through this site and have
appreciated everyones help and cooperation in obtaining that knowledge.

No performance issues, just to do it because I know it can be done. Personal
preference.

The users and myself can open the archive database which will be in the same
folder and can do a search in that database if infomation is to ever be
obtained. This would be in a rare situation which is another reason why I
don't want it in the primary database. Why keep that database cluttered with
information that may be needed once or twice in a year or more. Basically,
it's not much more effort for someone to open the other database once or
twice a year if even that. The one database currently set up this way, we
never looked in the archive since inception which has been over three years.

Thanks!!!!

 

[Jeff Boyce]

Doug

A check through the tablesdbdesign newsgroup will reveal something of a
consensus against removing records from one table (and/or database) and
putting them in another. The two reasons I see for this are that it is
unnecessary work (key word, work), and that it makes "spanning" the data
(looking up across all available data) much more difficult.

It isn't uncommon to find Access databases in the 100's of Mbytes, so a db
with under 10 MB is not particularly large.

If this is an "exercise", by all means, knock yourself out! Be aware that
you'll need to backup the "old" database, build several queries (or the
underlying SQL statements), select the records for archiving, append the
records to the new location, confirm that they've safely arrived, backup the
new database, delete the records from the old database, and, if those
records were in any way related to other tables' records, clean up all the
related table records too.

On the other hand, the commonly-used approach to not wanting to see "old"
records in a "current" database is to simply add a field to the table. If
you don't care when a record was archived, it can be a Yes/No field (e.g.,
[Archived?]). If you want to know when, make it a Date/Time field (e.g.,
[DateArchived]).

Then modify your queries and the forms (reports, etc.) that they feed to
exclude any records with that "archived" field set.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thanks for the info., point well taken. I will back up the database to
insure nothing happens. Do you know of a website that will explain and guide
me through this?

Thanks!!

Doug

A check through the tablesdbdesign newsgroup will reveal something of a
consensus against removing records from one table (and/or database) and
putting them in another. The two reasons I see for this are that it is
unnecessary work (key word, work), and that it makes "spanning" the data
(looking up across all available data) much more difficult.

It isn't uncommon to find Access databases in the 100's of Mbytes, so a db
with under 10 MB is not particularly large.

If this is an "exercise", by all means, knock yourself out! Be aware that
you'll need to backup the "old" database, build several queries (or the
underlying SQL statements), select the records for archiving, append the
records to the new location, confirm that they've safely arrived, backup the
new database, delete the records from the old database, and, if those
records were in any way related to other tables' records, clean up all the
related table records too.

On the other hand, the commonly-used approach to not wanting to see "old"
records in a "current" database is to simply add a field to the table. If
you don't care when a record was archived, it can be a Yes/No field (e.g.,
[Archived?]). If you want to know when, make it a Date/Time field (e.g.,
[DateArchived]).

Then modify your queries and the forms (reports, etc.) that they feed to
exclude any records with that "archived" field set.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Why not??? Is there any specific reason why you are against doing this?
I'm
not sure I understand. Based on your response, please clarify your
reason(s)
other than this is not the way you would handle it. The database is small,
about 35,000 records and maybe 7 or 8 MB. The reason why is just because I
want to learn and know how to do it. This way, I would have accomplished
learning a new task so when I do have an extremely large database I can
create the archive myself which is just more knowledge to gain and expand
my
skills. I like to learn and have learn quite a bit through this site and
have
appreciated everyones help and cooperation in obtaining that knowledge.

No performance issues, just to do it because I know it can be done.
Personal
preference.

The users and myself can open the archive database which will be in the
same
folder and can do a search in that database if infomation is to ever be
obtained. This would be in a rare situation which is another reason why I
don't want it in the primary database. Why keep that database cluttered
with
information that may be needed once or twice in a year or more. Basically,
it's not much more effort for someone to open the other database once or
twice a year if even that. The one database currently set up this way, we
never looked in the archive since inception which has been over three
years.

Thanks!!!!

 

[Jeff Boyce]

Doug

Backing up an Access database can be easily done from Windows Explorer. The
Access .mdb file is just another file -- copy and paste.

If you have a more-recent version of Access, there's a "backup" option off
the File menu as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the info., point well taken. I will back up the database to
insure nothing happens. Do you know of a website that will explain and
guide
me through this?

Thanks!!

Doug

A check through the tablesdbdesign newsgroup will reveal something of a
consensus against removing records from one table (and/or database) and
putting them in another. The two reasons I see for this are that it is
unnecessary work (key word, work), and that it makes "spanning" the data
(looking up across all available data) much more difficult.

It isn't uncommon to find Access databases in the 100's of Mbytes, so a
db
with under 10 MB is not particularly large.

If this is an "exercise", by all means, knock yourself out! Be aware
that
you'll need to backup the "old" database, build several queries (or the
underlying SQL statements), select the records for archiving, append the
records to the new location, confirm that they've safely arrived, backup
the
new database, delete the records from the old database, and, if those
records were in any way related to other tables' records, clean up all
the
related table records too.

On the other hand, the commonly-used approach to not wanting to see "old"
records in a "current" database is to simply add a field to the table.
If
you don't care when a record was archived, it can be a Yes/No field
(e.g.,
[Archived?]). If you want to know when, make it a Date/Time field (e.g.,
[DateArchived]).

Then modify your queries and the forms (reports, etc.) that they feed to
exclude any records with that "archived" field set.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Why not??? Is there any specific reason why you are against doing this?
I'm
not sure I understand. Based on your response, please clarify your
reason(s)
other than this is not the way you would handle it. The database is
small,
about 35,000 records and maybe 7 or 8 MB. The reason why is just
because I
want to learn and know how to do it. This way, I would have
accomplished
learning a new task so when I do have an extremely large database I can
create the archive myself which is just more knowledge to gain and
expand
my
skills. I like to learn and have learn quite a bit through this site
and
have
appreciated everyones help and cooperation in obtaining that knowledge.

No performance issues, just to do it because I know it can be done.
Personal
preference.

The users and myself can open the archive database which will be in the
same
folder and can do a search in that database if infomation is to ever be
obtained. This would be in a rare situation which is another reason why
I
don't want it in the primary database. Why keep that database cluttered
with
information that may be needed once or twice in a year or more.
Basically,
it's not much more effort for someone to open the other database once
or
twice a year if even that. The one database currently set up this way,
we
never looked in the archive since inception which has been over three
years.

Thanks!!!!

:

Why? Do you have an unreasonably large database? What do you
consider
"too
much"?

Are there performance issues driving your wish to move records?

How will you/your users look up information "across" multiple
databases,
if
needed?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I know, but I don't want these records taken up space in the main
database.
I'd rather move them outside to another database within the same
folder.

:

Lots of folks just add a field like Active or Archive as a Yes/No
fields.
Then in the queries add the field to pull active records.

:

Hello,

Does anyone know of a site that would explain in full detail but
yet
easy
enough to create an archive database that I can dump unused data
from
the
primary database into with a click of a button?

I have one on a database a developer created by he is no longer
available an
I cannot figure out how he did it. But all I have to do is click
a
Archive
Data button on the primary databases main menu form and it pours
the
data
into the archive database. I need to do this for another
database.

Thanks!!!!

 

[Guest]

Great thanks!

Doug

Backing up an Access database can be easily done from Windows Explorer. The
Access .mdb file is just another file -- copy and paste.

If you have a more-recent version of Access, there's a "backup" option off
the File menu as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the info., point well taken. I will back up the database to
insure nothing happens. Do you know of a website that will explain and
guide
me through this?

Thanks!!

Doug

A check through the tablesdbdesign newsgroup will reveal something of a
consensus against removing records from one table (and/or database) and
putting them in another. The two reasons I see for this are that it is
unnecessary work (key word, work), and that it makes "spanning" the data
(looking up across all available data) much more difficult.

It isn't uncommon to find Access databases in the 100's of Mbytes, so a
db
with under 10 MB is not particularly large.

If this is an "exercise", by all means, knock yourself out! Be aware
that
you'll need to backup the "old" database, build several queries (or the
underlying SQL statements), select the records for archiving, append the
records to the new location, confirm that they've safely arrived, backup
the
new database, delete the records from the old database, and, if those
records were in any way related to other tables' records, clean up all
the
related table records too.

On the other hand, the commonly-used approach to not wanting to see "old"
records in a "current" database is to simply add a field to the table.
If
you don't care when a record was archived, it can be a Yes/No field
(e.g.,
[Archived?]). If you want to know when, make it a Date/Time field (e.g.,
[DateArchived]).

Then modify your queries and the forms (reports, etc.) that they feed to
exclude any records with that "archived" field set.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Why not??? Is there any specific reason why you are against doing this?
I'm
not sure I understand. Based on your response, please clarify your
reason(s)
other than this is not the way you would handle it. The database is
small,
about 35,000 records and maybe 7 or 8 MB. The reason why is just
because I
want to learn and know how to do it. This way, I would have
accomplished
learning a new task so when I do have an extremely large database I can
create the archive myself which is just more knowledge to gain and
expand
my
skills. I like to learn and have learn quite a bit through this site
and
have
appreciated everyones help and cooperation in obtaining that knowledge.

No performance issues, just to do it because I know it can be done.
Personal
preference.

The users and myself can open the archive database which will be in the
same
folder and can do a search in that database if infomation is to ever be
obtained. This would be in a rare situation which is another reason why
I
don't want it in the primary database. Why keep that database cluttered
with
information that may be needed once or twice in a year or more.
Basically,
it's not much more effort for someone to open the other database once
or
twice a year if even that. The one database currently set up this way,
we
never looked in the archive since inception which has been over three
years.

Thanks!!!!

:

Why? Do you have an unreasonably large database? What do you
consider
"too
much"?

Are there performance issues driving your wish to move records?

How will you/your users look up information "across" multiple
databases,
if
needed?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I know, but I don't want these records taken up space in the main
database.
I'd rather move them outside to another database within the same
folder.

:

Lots of folks just add a field like Active or Archive as a Yes/No
fields.
Then in the queries add the field to pull active records.

:

Hello,

Does anyone know of a site that would explain in full detail but
yet
easy
enough to create an archive database that I can dump unused data
from
the
primary database into with a click of a button?

I have one on a database a developer created by he is no longer
available an
I cannot figure out how he did it. But all I have to do is click
a
Archive
Data button on the primary databases main menu form and it pours
the
data
into the archive database. I need to do this for another
database.

Thanks!!!!