CODEWITHSUNDEEP
vbams-accessindexingms-access-2013
mountainclimber11
mountainclimber11

Reputation: 1400

Reducing file size via keys/indices

I'm using MS Access, so file size is a real constraint (2 gigs I think). Am I saving space in the most efficient way?

tbl1: tbl_NamesDescs

tbl2: tbl_HistStatsSettings

So using the two tables above, tbl2 has ~800k records and all of Factor's unique possibilities are listed in ColName (i.e. there is a one to many relationship relationship between ColName and Factor receptively). When I look at the tables in Datasheetview I see all of the names listed (full text) in both Factor and ColName.

Question: Is that the best way to save space? I would think that Factor should be a list of indices (numbers, not text) corresponding to ColName.

In other words, wouldn't it be more file-space efficient to populate Factor with the pid_NamesDescs autonumers since numbers are smaller than text? If that is true, what is the best way to make this happen (either steps in MS Access or VBA is what I am after here)?

EDIT: added table names and pid names as they really exist

Upvotes: 0

Views: 38

Answers (2)

Andre
Andre

Reputation: 108

In addition to normalization, also check all your text fields. The default is 255 characters for short text. When you are storing less than 255 characters in a text field, make sure the field size is set to no more than what you typically store. Upon changing, perform compact and repair to reduce file size. Where possible, use the short text over long text.

Also consider a split database approach where data is on the back end and your UI and VBA in the front end.

Upvotes: 0

OpiesDad
OpiesDad

Reputation: 3435

Yes, putting the FactorID as a number instead of text will save space. I can't really answer whether it's the "best" way, but it will definitely save space.

The easiest way to do this is to run the following query:

Update tbl2 LEFT JOIN tbl1 ON tbl2.Factor = tbl1.ColName
SET tbl2.Factor = CStr(tbl1.PID_tbl1)
WHERE Not IsNull(tbl1.ColName)

Then, in design view change the datatype of "factor" to Long. I'd also then change the name of the Field to "FactorID" and change the name of "ColName" To "Factor." I'd make some other changes to the column/table (although you may be giving fake names) names for clarity.

OR make a helper column (as a long int as you suggested in comments) and update the helper field, and then delete the original field.

Then, go into the relationships table and add a relationship between tbl1.PID_tbl1 and tbl2.FactorID

After this, Compact and Repair the database to reduce the size.

*EDIT to add portion about adding the relationship between the tables.

Upvotes: 1

Related Questions