File Length - Compact

[VivDenham]

Hi there

It has occurred to me that the reason my upload is failing is because the size of the zipped file is 78,452kb - is this too big? If so, would it help if I deleted the contents of the Make Tables and then uploaded it? Would you be able to then run the MTQs yourselves? (You need the contents of the Make Tables for the later queries to work as they pick up their data from the MTs).

Let me know if this would help and I will try again. Many thanks.

Viv

[dsatino]

Personally I have no idea what the size limit is, but deleting the contents of the temp tables will definitely reduce the size. I would go ahead and give it a shot and see what happens.

[zmbd]

Yes... Make a backup, clear the make tables, and then do a compact too before re-ziping the file.
-z

[VivDenham]

Hi there

Deleted the Make Tables and this reduced the zipped file size from 78,452kb to 26,748kb. Tried to upload, but again got the Error Message that the Security Token is missing. Am still awaiting an update from Bytes Support team. I will keep trying.

Many thanks for your continued interest.

Viv

[NeoPa]

Viv,

See Attach Database (or other work) for the steps you should follow to attach your work. If you look at the page that opens when you click on {Manage Attachments} you'll see a list of maximum file sizes that pertain to various file types. Clearly, yours should be a ZIP file (if you've followed the instructions above correctly), so the maximum size to upload is 5 MB.

Good luck :-)

[VivDenham]

Hi NeoPa

Thanks for that - I have been doing exactly what I should. However, the problem seems to be this "Security Tokens" issue. I have just tried uploading using IE9, Google Chrome and Mozilla Firefox browsers - and all give me the same message about Security Tokens being missing.

So it looks like I am in the hands of Bytes Support team for them to come up with a solution to this bug.

Many thanks for trying to help.

Viv

[NeoPa]

That may not be as easy as it sounds Viv, as it seems to be a problem specific to you as far as I can tell. I just tested a random database upload and had no trouble with my file which was <2MB in size. What size did your ZIP file turn out to be for interest?

[VivDenham]

Hi - I think this problem may well have been me! I'm not at all up with the various meanings of Kb, Mb, and Gb, so although I was aware of the 5Mb size limit, I thought that my zipped file of 26,748 was under the 5Mb (thinking that 5Mb was 50,000kb). However, when my son returned from a trip away, he told me that I need to get the DB to under 5,000kb.

So I will try deleting more stuff to see if I can get it below the 5Mb (5,000kb) size limit.

As I said, I am a 65-year old beginner, and must have been having a senior moment. Apologies...

Viv

[twinnyfo]

Viv,

As I have been following this thread, now I am getting "curiouser and curiouser" as to the size of your database...... Do you have a lot of photos included in your DB? I have (what I consider) huge database with thousands of records, about a hundred forms, 50-60 reports and probably twice as many queries, that only comes out to 25 MB.

If you do have a lot of pics, we can also give you tips on how to remove the pics from the DB and refer to them in code. This would also save you some space.

I am also interested to see your queries, because I find it fascinating that you could actually calculate out the amount of yarn needed for a piece and estimate its weight.

I am intrigued!! Send us this DB, because us us puzzlers would love to help you figure it out!!

Regards,
twinnyfo

[VivDenham]

Hi there

Yippee! I've finally managed to upload my database for you to look at.

To reduce its size, I've removed all Products except Tops (button on the Welcome Screen). I've also reduced the number of Tops to 6. And I've removed all the Make Tables.

Therefore, to get the queries to work for Knitting Instructions onwards, you will need to go to Step 5 on the menu - Items Knitted - Add New. In that form, if you uncheck the Record Locked Y/N box, and then close the form, this will force the MTQs to run. That should then populate the queries for Step 6 onwards.

Hope it all works. Thank you so much for your time.

Viv

[twinnyfo]

Viv,

First, I am incredibly impressed that you have built this database. It is HUGE and very complex. I am VERY impressed!

This is not an immediate fix to your ballooning database size, but may help you understand why it's doing what it's doing.

From what I understand about the generation of tables in Access, the DB will set aside a certain amount of memory based on the data type--whether all of that memory is needed or not. So, every time you have a memo field, and make a table, and establish records for that table, Access assumes you will be using 5000 characters. I did not look at all your memo fields, but typically, you should only use memo fields for consistently large blocks of text, for this very same reason. For many of your tables, which have "Notes", you may be able to change the data type of these fields to Text, which only assumes 255 characters. If this is too tight of a restraint, you may have to stick with Memo type.

Another, based on the same reasons, is the pictures. It appears that you have the pictures saved as OLE Objects within the tables. May I recommend that you establish a separate Directory under your Database for just your photos. Then, in the Table, instead of an OLE Object field, have a small text field (it could be limited to 30 Characters) with the file name of the photo. If ALL of your photos are in the same format (i.e. .jpg) you only need the file name and not the extension. Then, whenever you need to display any photos, instead of using hte embedded OLE Object, you point to the file in your photos directory and displayi it on the screen. There are some very easy ways to do this.

Although much less important, many of your Primary Keys are set as Long Integers. Although there is nothing wrong with this, and is very common (and required) if you have many, MANY records, since you only have a few records per table, these Keys could be regular Integers, which could save some space, because you have so many tables.

Because of these particular issues with your database, ballooning will continue (not sure why the compact on close would ever want to be turned OFF in your case). These minor (although very time-consuming) mods, might assist with the constant ballooning.

I hope this info helps.....

Still marvelling at the complexity of this DB!!!!

[dsatino]

Oh...my...lord. ..........

First, pleeeeeeaaaase don't take any of this the wrong way.

Ok, now where to begin. I guess the first thing is to honestly congratulate you on getting this far. The fact that you've managed to make this thing work with the structure you have is based on how highly organized and specific it is. Given what you're doing, you definitely made the right choice in creating a database.

That said, and this is the part where I beg you not to take offense, I practically had a brain anuerism when I saw your structure. What you need is to 'normalize' this database. I'm not going to explain what that means because you can just Google it, but normalization would cut down the number of tables you have by probably 80-90%. You defintely have about 60 more forms than you need and probably about 30 or so more queries and modules than you need.


Now, before I even look at automating your calculations, do you want to take a look at the concept of database normalization first? Between what I've seen from your organizational skills and the required skills needed for knitting products, I think you'd understand the normalization concept quite easily.

[VivDenham]

Hi twinnyfo

Thanks for all that info - I'll take a look and see if I can use your tips to save space. I have in the past, tried to use various methods to reduce the size of the pictures and photos. For the Photos, I resample to 5% or 10%, depending which Form it appears in. For the Pictures, these are created in Coreldraw. I experimented with inserting them as bitmaps, jpegs, attachments etc, but the least space was taken by leaving them as Coreldraw files.

I am still intending to split the database but I've got one more field that I want to rename first - when the repercussions of doing that are sorted, then I'll do the split.

I did a "test run" at splitting the DB, and the Back End ended up at 60,000 kb and the Front End was 307,000 kb. At least that way, if I do have to keep compacting, it will only be the temporary tables etc that may get corrupted, not the raw data.

Having learned the hard way, now every time I make even minor changes to any of the tables, queries, forms etc, I close the DB, save it with a Version Number and name, make a copy and then work on that new copy. Some days I end up with 20 new copies, but at least I don't lose my work!!!

Thanks, once again for looking at my database, and for your tips. Tomorrow I shall be back to the drawing board.

Viv

[VivDenham]

Hi dsatino

Thanks for the comments. Could you give me an example of where the DB is not normalised please.

Thanks
Viv

[zmbd]

flat v normalzied

Thanks for the comments. Could you give me an example of where the DB is not normalised please.

Viv, I've only read your last question here and haven't had a chance to review the database (can't wait to get home!)

Without getting too techy: http://www.databasedev.co.uk/flatfile-vs-rdbms.html

Also, revisit: Creating an Access Database - Step 1: Database Design this will help you with the flat (non-normalized) concept.
-z
OK… 10hour trip home with 2 toddlers and a 9yr DD… time for a nap!