Making a Clean Dataabase

[Programmer - wannaB]

Access 97 database created by general user, and grown over the years, now it
is used by 8 - 10 users and needs to be cleaned, and upsized.

The First phase of this upsize project will be to convert it to 2003, TEST
CONVERTED CLEANLY.
Then clean it out, removing all the temp queries and stuff that was started
and never finished.

Is there any difference between removing modules, tables, queries,
forms..... from a copy of the original, AND creating a NEW DB1.MDB and
copying only those objects into the new database???

I wonder if this will have any effect on the system tables? Can someone
enlighten me?
Thank you.

 

['69 Camaro]

Hi.

Is there any difference between removing modules, tables, queries,
forms..... from a copy of the original, AND creating a NEW DB1.MDB and
copying only those objects into the new database???

Removing database objects from a copy of the original database leaves the
system tables intact, as well as any database bloat or undetected
corruption. The new database with only the necessary database objects
imported into it will have a new set of system tables, and its own sets of
Database Properties and individual Database options. The new database will
tend to be a smaller file, sometimes significantly so.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Programmer - wannaB]

FIRST I MUST RESPOND TO THE USER NAME>> 69 Camaro
"69 T/A" would be my Preference !!

If Objects are moved into a new DB what will be lost besides any relations
that might be a part of the DB, and I think rights, if there were any would
also be lost, yes??
Thanks for you time, I have not yet read the links, will do that soon.

 

['69 Camaro]

Hi.

"69 T/A" would be my Preference !!

Good luck finding one! They didn't make very many of them.

If Objects are moved into a new DB what will be lost besides any relations
that might be a part of the DB

Relations won't be lost, as long as the option to include Relationships is
also included in the import. There's a free tool available to copy the
Relationships diagram from one database to another (as long as they have
sustantially identical table structures). I'll see if I can find the URL
for you and post it later.

and I think rights, if there were any would
also be lost, yes??

Yes. If User-Level Security is implemented on the original database, then
the permissions would be lost in the new database, and whichever user did
the import would own the database objects in the new database. But there's
a free tool to copy security permissions from one database to another, too.
I'll see if I can find the URL for you and post it later.

So, with the right tools and a little planning, the Relationships and
security permissions aren't really issues for importing objects into the new
database.

I have not yet read the links, will do that soon.

Oh, it's not required. But you may find some interesting Access tips
and links if you have time to check them.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[BruceM]

Here is a link to the Transfer Permissions Add-In (it is the dbUtilities
2000 Addin link):
http://www.daiglenet.com/MSAccess.htm
I don't recall if this is transfer's the relationships diagram, but I can
attest it works well for transferring permissions.
Here are some more add-ins:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Addins
I can say from experience that CSD Tools is quite helpful. I expect the
others are too, but I have tried only the two I mentioned.

 

[Programmer - wannaB]

HI, not sure that this is the right place to go on too far about this, so if
you'ld like we can chat directly email me (drbobb1 at hotmail) about how
there were 697 T/A's made in 69, 8 convertibles and 4 of those were Big Block
400 CI. I don't need to look past my garage to find one, but it is not one
of the eight.

Thanks for the info about the relational and security tools, This DB does
not have any relations, or security, so I should need those.
======================

 

['69 Camaro]

Hi, Bruce.

Here is a link to the Transfer Permissions Add-In (it is the dbUtilities
2000 Addin link):
http://www.daiglenet.com/MSAccess.htm

That Web page isn't going to be up much longer. She moved that stuff to
AccessMVP.com. Bookmark the following Web page for future reference:

http://www.accessmvp.com/SDaigle/index.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

Even though you've stated elsethread that you don't need the relationships
diagram to be copied intact from one database file to another, others may
find the tool helpful. If so, please see the following Web page on Access
MVP Stephen Lebans' Web site:

http://www.lebans.com/saverelationshipview.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

HI, not sure that this is the right place to go on too far about this

You're right. It isn't.

so if
you'ld like we can chat directly email me

I'm kinda swamped at the moment, but I'll contact you sometime later this
week.

Thanks for the info about the relational and security tools

You're welcome.

This DB does
not have any relations, or security, so I should need those.

I can understand not having security, but no relations, either? That makes
it sound more like a bunch of imported spreadsheets which just happen to be
stored in the same database file.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[WANNABE]

My Statement of no relations was misunderstood.
The are no enforced relationships in this database, they are independent
relationships managed through queries, forms and reports. As I said this was
designed adhoc, OJT, by an accountant.
Thank you for taking the time to post the tool.
==================================
message Hi.

HI, not sure that this is the right place to go on too far about this

You're right. It isn't.

so if
you'ld like we can chat directly email me

I'm kinda swamped at the moment, but I'll contact you sometime later this
week.

Thanks for the info about the relational and security tools

You're welcome.

This DB does
not have any relations, or security, so I should need those.

I can understand not having security, but no relations, either? That makes
it sound more like a bunch of imported spreadsheets which just happen to be
stored in the same database file.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[BruceM]

Thanks for the notice. Bookmark changed.

Hi, Bruce.


That Web page isn't going to be up much longer. She moved that stuff to
AccessMVP.com. Bookmark the following Web page for future reference:

http://www.accessmvp.com/SDaigle/index.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

The are no enforced relationships in this database, they are independent
relationships managed through queries, forms and reports.

Be sure to check for orphan records. Relationships which aren't enforced
produce orphan records eventually.

Thank you for taking the time to post the tool.

You're welcome. Good luck.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.