Reduce the Size

[shariefoo]

Hi all,

My problem is that My access is HUGE. i am working on a project where data called SAM files is imported from the server. This has over 290,000 fields. not to metnion other files with aroun 20,000 feilds.

The size of my access is 600 MBs and is extremly slow when opening a report.

Any ideas of what i can do ??

Ohh yea and i tried to delete the data in the feilds but it stayed the same size . dose Access have some kind of a cache or something ?

 

[WayneRyan]

Shariefoo,

Look under Tools --> Database Utilities --> Compact/Repair

This essentially opens up a new, empty database, copies
all of your objects into it, and if successfull renames the
new file to your database name.

It is normal for an Access database to grow over time even
if you don't add any records. Do a search here on "bloat"
or "Compact" for more info.

Wayne

 

[shariefoo]

Thanx Wayne

 

[Oldsoftboss]

If you have over 290,000 fields, then there is a fair chance the Db structure is nowhere near correct. In A2000 there is a table analyzer TOOLS - ANALYZE - TABLE. This will recommend a better table structure. If you are talking about 290,000 records, then Compact & Repair is your best option.

Another thought. Does your Db store images as imbedded? If so, there is your problem. Always link images.

Dave

 

[shariefoo]

Oh no there are no images.. The 290,000 records are actually correct..

The reason of this number is the following:

The SAM file includes all usernames tha log into the system, details of when they last changed their passwords, the groups they belong to.. etc..etc..etc..

Our organization has 17000 employees (approximatly). the reason behind the 290,000 records is that because every user belongs to 10 to 20 groups. multiply that with 17,000 and you will get the 290,000 employees..

Now you will suggest that i put the groups in a different table and connect them to the user table.

NOT POSSIBLE.. reason ? Becuase when impoting the SAM file i cant automatically create another table and dump the groups in that and i cannot create automatically a mid table that connects both tables (becaause of the Many-to-Many relation)

 

[Oldsoftboss]

Excuse my ignorance, but what is a SAM file. How often is it (are they) imported ?

 

[shariefoo]

Its ok.. Actually The SAM file is not really common among people..Its used only by Network Admins..

Anyways the SAM file is important usually once every month. Its just to create some managerial reports for top managment to see how their network in the matter of security

However, if a certain manager demans a report about his department like how many of them changed their passwords in the last X days, or how many computers are locked, or..or..or.. the SAM file is imported and a report is created