Need Recommendation - reduce size of db

[Susan Bricker]

Greetings. My disclaimer first - I am a neophyte where VB and ACCESS
are concerned, but I have been programming for quite a long time. That
said ...

I created a db at work using MS/ACCESS 2000 to assist in our software
project management. The group I work with (online transaction
processing for a major financial institution) works on various projects.
Our programmers are assigned tasks for these projects. Projects are
grouped into a sofware "Release". Releases are installed into
Production.

So the design goes something like

Many Tasks in a Project (tblTasks).
Many Projects in a Release (tblProjects).
Many Releases in the Db (tblReleases).

Also,

There are other records (another table) associated with Tasks (task
notes). There are other records (another table) associated with
Projects (project notes).

As we progress through the years the size of the tables is increasing
and the response time is reducing. When I wrote the db I was a real
beginner and it might not be that efficient. The db resides on a server
and we access the db with a shortcut (icon on the desktop) to the remote
server drive (where the FE and BE reside).

Ok ... now to the QUESTION ...

I want to remove records from the following tables that are associated
with OLD Releases:
tblReleases, tblProjects, tblTasks, tblProjectNotes, tblTaskNotes.

How should I do this?

I am reluctant to just manually go into the BE and delete the records.
I think I want to keep them around for the proverbial "just in case".
Although, there is no reason to ever go back. This db is used by
Release Coordinators to generate STATUS REPORTS during the lifecycle of
a Release. Once the Release has gone into Production there is no reason
to produce any more reports.

All suggestions are welcome.
Thanks much.
Sue
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[kingston via AccessMonster.com]

How about copying the BE and archiving it? Then you can remove records from
the new BE to recover space. Also, I assume you've tried the menu function
Tools -Database Utilities -Compact and Repair Database... How many
records are in the database? It doesn't seem like space should be a problem
with the structure you describe unless you're storing more than just text,
numbers, and dates. Performance is dependent on your use of indexes,
condition of the network, number of concurrent users, etc. So, perhaps you
should test performance based solely on record numbers to see if there is
sufficient improvement. HTH

Susan Bricker wrote:

>Greetings. My disclaimer first - I am a neophyte where VB and ACCESS
are concerned, but I have been programming for quite a long time. That
said ...

I created a db at work using MS/ACCESS 2000 to assist in our software
project management. The group I work with (online transaction
processing for a major financial institution) works on various projects.
Our programmers are assigned tasks for these projects. Projects are
grouped into a sofware "Release". Releases are installed into
Production.

So the design goes something like

Many Tasks in a Project (tblTasks).
Many Projects in a Release (tblProjects).
Many Releases in the Db (tblReleases).

Also,

There are other records (another table) associated with Tasks (task
notes). There are other records (another table) associated with
Projects (project notes).

As we progress through the years the size of the tables is increasing
and the response time is reducing. When I wrote the db I was a real
beginner and it might not be that efficient. The db resides on a server
and we access the db with a shortcut (icon on the desktop) to the remote
server drive (where the FE and BE reside).

Ok ... now to the QUESTION ...

I want to remove records from the following tables that are associated
with OLD Releases:
tblReleases, tblProjects, tblTasks, tblProjectNotes, tblTaskNotes.

How should I do this?

I am reluctant to just manually go into the BE and delete the records.
I think I want to keep them around for the proverbial "just in case".
Although, there is no reason to ever go back. This db is used by
Release Coordinators to generate STATUS REPORTS during the lifecycle of
a Release. Once the Release has gone into Production there is no reason
to produce any more reports.

All suggestions are welcome.
Thanks much.
Sue
Regards,
SueB

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[Kc-Mass]

I would suggest you move the FE to the workstations just like you load
Excel or Word from the workstation to work on data on the server.
If you have data you don't need but want to keep, secure a copy/archive
of the current BE and then flush the old records out of the BE.

If you had the time and inclination you could write a routine that would
either
periodically or based on some project state, transfer the old projects to an
archive db.

Kevin C

"Susan Bricker" <su*************@citigroup.comwrote in message
news:45*********************@news.qwest.net...

Greetings. My disclaimer first - I am a neophyte where VB and ACCESS
are concerned, but I have been programming for quite a long time. That
said ...

I created a db at work using MS/ACCESS 2000 to assist in our software
project management. The group I work with (online transaction
processing for a major financial institution) works on various projects.
Our programmers are assigned tasks for these projects. Projects are
grouped into a sofware "Release". Releases are installed into
Production.

So the design goes something like

Many Tasks in a Project (tblTasks).
Many Projects in a Release (tblProjects).
Many Releases in the Db (tblReleases).

Also,

There are other records (another table) associated with Tasks (task
notes). There are other records (another table) associated with
Projects (project notes).

As we progress through the years the size of the tables is increasing
and the response time is reducing. When I wrote the db I was a real
beginner and it might not be that efficient. The db resides on a server
and we access the db with a shortcut (icon on the desktop) to the remote
server drive (where the FE and BE reside).

Ok ... now to the QUESTION ...

I want to remove records from the following tables that are associated
with OLD Releases:
tblReleases, tblProjects, tblTasks, tblProjectNotes, tblTaskNotes.

How should I do this?

I am reluctant to just manually go into the BE and delete the records.
I think I want to keep them around for the proverbial "just in case".
Although, there is no reason to ever go back. This db is used by
Release Coordinators to generate STATUS REPORTS during the lifecycle of
a Release. Once the Release has gone into Production there is no reason
to produce any more reports.

All suggestions are welcome.
Thanks much.
Sue
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

Kingston,
Thanks for the information. I have the db do a 'compact and repair' on
all exits from the db. The db is a bit "buggy". Every once in awhile
(when entering loads of data ... lots of tasks being entered), it
"freezes". I don't have time to actually fix it right now. So we just
live with it. We don't actually lose records. Just go back in and
start where we left off. As for the number of users at one time ... not
too many; 1 or 2 at a time. Sometimes there could be 3 users at a time,
but not very often. I, actually have a copy of the FE on my system and
link it to the BE on the remote disk. It still gets bad response time,
especially when generating the Release Status Report. I'll start with
copying the BE and then manually removing the records that I don't want
anymore. When I get a free moment (LOL) I'll look into a programmatic
method of removing records and putting them into a separate db. That's
my ultimate goal ... press/click on a button and VOILA!! have it done.

Thanks, again.
Sue

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[kingston via AccessMonster.com]

Are you performing a compact and repair over the network? This might cause
problems unless you have a really reliable network and server. Consider
copying the file to your local computer, compacting it there, and then
replacing it on the server (I've seen problems where data is corrupted over
the network during a compact but the database will seem fine - i.e. you don't
know there's a problem until it's too late). Also, test performance locally
(both BE and FE together on your computer). This will be the application's
top speed so to speak. Hopefully, you can find time to do this.

Finally, at the risk of incurring some vitriolic responses, you might
consider using SQL Server instead of Access MDB if you're hitting limits.

Susan Bricker wrote:

>Kingston,
Thanks for the information. I have the db do a 'compact and repair' on
all exits from the db. The db is a bit "buggy". Every once in awhile
(when entering loads of data ... lots of tasks being entered), it
"freezes". I don't have time to actually fix it right now. So we just
live with it. We don't actually lose records. Just go back in and
start where we left off. As for the number of users at one time ... not
too many; 1 or 2 at a time. Sometimes there could be 3 users at a time,
but not very often. I, actually have a copy of the FE on my system and
link it to the BE on the remote disk. It still gets bad response time,
especially when generating the Release Status Report. I'll start with
copying the BE and then manually removing the records that I don't want
anymore. When I get a free moment (LOL) I'll look into a programmatic
method of removing records and putting them into a separate db. That's
my ultimate goal ... press/click on a button and VOILA!! have it done.

Thanks, again.
Sue

Regards,
SueB

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[Susan Bricker]

Yes, the compact and repair are being performed over the network. I'll
consider copying the file to my local desktop and compacting it there
and copying it back to the remote drive. When I first "turned over"
this db for public use, I did check performance locally vs. remotely (on
the remote drive). It definitly performed better (in fact pretty well)
locally but there was notable slowdown remotely (even with small
tables). I didn't know enough and didn't have any time to work on it.
I was working on this db unofficially, just for fun, to help me as a
sometimes 'Release Coordinator' (when not working on project tasks
myself) produce those nasty status reports that management always want.

SQL Server hey? I'll have to some reading on that. Don't know anything
in that area. Does it have a front end user interface like MS/Access?
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"Susan Bricker" <su*************@citigroup.comwrote

SQL Server hey? I'll have to some reading on that.
Don't know anything in that area. Does it have a
front end user interface like MS/Access?

No, but you can use Access as the client application; you just link the
tables from SQL Server. But, if you do not already have a server database
installed, and a Database Administrator (DBA) to provide it with the Tender
Loving Care it requires, that is a decidedly non-trivial amount of learning,
time, and effort.

Larry Linson
Microsoft Access MVP

[Larry Linson]

I'd suggest you back up the entire database as your "just in case"
insurance.

Then, if you think some "historical data review" might be needed, make a
copy of those essential tables (structure only) in a new "Archive" database.
I'd do this, even if I didn't suspect the "historical data review" might be
needed, because I wouldn't want to answer the question "So why did you throw
away possibly-useful data?" at the future date someone decided such analysis
might be useful.

Link the Archive tables, and use Queries to copy to the Archive those
records that are no longer active, then use Queries with the same criteria
to delete the same records from your production database. Once this is done,
back up your DB, Compact and Repair, then back it up again.

If you don't have relationships defined, referential integrity applied, and
Cascading Delete, you should look at those in Help -- that will make your
fina deletions easier... just delete the parent record (the "one" side) and
the child records (the "many" side) will be removed along with it.

Larry Linson
Microsoft Access MVP

"Susan Bricker" <su*************@citigroup.comwrote in message
news:45*********************@news.qwest.net...

Greetings. My disclaimer first - I am a neophyte where VB and ACCESS
are concerned, but I have been programming for quite a long time. That
said ...

I created a db at work using MS/ACCESS 2000 to assist in our software
project management. The group I work with (online transaction
processing for a major financial institution) works on various projects.
Our programmers are assigned tasks for these projects. Projects are
grouped into a sofware "Release". Releases are installed into
Production.

So the design goes something like

Many Tasks in a Project (tblTasks).
Many Projects in a Release (tblProjects).
Many Releases in the Db (tblReleases).

Also,

There are other records (another table) associated with Tasks (task
notes). There are other records (another table) associated with
Projects (project notes).

As we progress through the years the size of the tables is increasing
and the response time is reducing. When I wrote the db I was a real
beginner and it might not be that efficient. The db resides on a server
and we access the db with a shortcut (icon on the desktop) to the remote
server drive (where the FE and BE reside).

Ok ... now to the QUESTION ...

I want to remove records from the following tables that are associated
with OLD Releases:
tblReleases, tblProjects, tblTasks, tblProjectNotes, tblTaskNotes.

How should I do this?

I am reluctant to just manually go into the BE and delete the records.
I think I want to keep them around for the proverbial "just in case".
Although, there is no reason to ever go back. This db is used by
Release Coordinators to generate STATUS REPORTS during the lifecycle of
a Release. Once the Release has gone into Production there is no reason
to produce any more reports.

All suggestions are welcome.
Thanks much.
Sue
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[kingston via AccessMonster.com]

Actually, you could keep using Access as the front end. SQL server would
just manage the tables which would be linked into the Access or VB front end
(little to no re-coding required). Again, at the risk of incurring some
nasty responses, it sounds like you don't need SQL server, especially since
it isn't free. However, you're the only one who can determine whether Access
can continue to work for you.

Susan Bricker wrote:

>Yes, the compact and repair are being performed over the network. I'll
consider copying the file to my local desktop and compacting it there
and copying it back to the remote drive. When I first "turned over"
this db for public use, I did check performance locally vs. remotely (on
the remote drive). It definitly performed better (in fact pretty well)
locally but there was notable slowdown remotely (even with small
tables). I didn't know enough and didn't have any time to work on it.
I was working on this db unofficially, just for fun, to help me as a
sometimes 'Release Coordinator' (when not working on project tasks
myself) produce those nasty status reports that management always want.

SQL Server hey? I'll have to some reading on that. Don't know anything
in that area. Does it have a front end user interface like MS/Access?

Regards,
SueB

--
Message posted via http://www.accessmonster.com

[Bri]

Susan Bricker wrote:

Kingston,
Thanks for the information. I have the db do a 'compact and repair' on
all exits from the db.

When you set this option it is only the FE that is compacted, not the
BE. Unless you have written code to do the BE compact, you need to open
it and compact it, regularly.

As Kingston mentioned, the size of the tables has a lot less to do with
performance than using Indexes and Relationships correctly. Make sure
that there is an index set on all fields that are used in Joins and
Criteria (with the exception of Boolean fields).

--
Bri