473,890 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

File Length - Compact

44 New Member
Hi there
I have a MS Access 2007 database which frequently grows to over 2Gb. I want to be warned when the file length gets to, say, 1.5Gb. I have got so far with the following code - please bear in mind that I am an absolute beginner!!! - but don't know where to put this. Do I put it in a Form, or in every form, or on the Start Up form, or where?

Also, ideally, I would like this Module to also Compact the database rather than just reminding me to do so manually.

The code so far is:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Module_Compact()
  3.         If FileLen(CurrentDb.Name) > 150000 Then
  5.         Dim PrintMsgBox As Byte
  6.         PrintMsgBox = MsgBox("File Size getting too big.  Please Compact.", vbOKOnly + vbExclamation, "WARNING - FILE SIZE TOO BIG")
  8.         End If
  10. End Sub
Many thanks to you all for any help you can give me.

Jul 31 '12
37 3963
393 Contributor
Clicked the 'Advanced' button and then from that screen click the 'Manage Attachments' button and upload it through the popup screen
Jul 31 '12 #11
5,501 Recognized Expert Moderator Expert
Hello and Welcome to Bytes!

I must say it sounds like you’ve really started quite the ambitious project!

Before you get too much farther into your project may I respectfully offer a few links that might help you with your current and future projects:

With all of the MTQ being discussed, and the problematic query SQL‘s; I suspect that you may have a need to review how the database is “normalized” and maybe the over all logic in the RDMS design.

As you already have a RDMS set up, let’s start with “Normalization: ” For me, the following tutorial was a good refresher with the concept of "normalizat ion" and it has a really good explanation of the concept for those just starting out: Database Normalization and Table Structures.

IMHO: This is the number one problem in database design and is very much like playing Chess… very easy to learn the basic rules and a #### difficult game when played. Using a lot of MTQ is very symptomatic of either a normalization issue or some other issue… You really shouldn't be running into issues with 2GB. As DSantio points out 2GB isn't a large storage space; however, 2GB should hold something like 1 million pages of unformatted 10pitch/Dbls/1"margn typed text (say around 20 sets of the Encyclopedia Britannica at 25ish-vol/set including some of the pictures). For the average user, that's a lot of space. My Mom (65yrs) has a very old PC with 4GB harddrive (I know) and it has over a 1000 pictures stored on the drive (thankfully, backed up to DVD now - love vacation I.T. work)

As for the other issues, the solution I have is this tutorial covering the basics of relational database management system (RDMS) design. Now I understand that you’ve already dived in and gotten wet (YEA!!! Can‘t swim if you don‘t get in the pool!); however, I’ve found that all too often the textbooks really do a very poor presentation of the actual steps behind setting up a database from scratch. However, I have found a really good step by step tutorial for basic design here: A Tutorial for Access
Although this may sound a tad late for your current project, this tutorial may yet give you some insight to any issues you may run into with it in the near term and should help you with any future projects (may inspire you to start from scratch … no…. don’t blame you! Several DB I use made by others that have way too much data to mess with and… well… I don’t fix them)

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy grid table to do this... just a line by line layout.

Now for one more issue you will run into with any text from Microsoft… lookup fields in tables... I avoid lookup fields at the table level! And here is why: :Are Lookup Fields in Tables Evil? ... despite the fact that these are supported in MSAccess, they are not supported in other databases. More importantly, writing queries based on tables with look-up fields later on will be somewhat problematic. IMHO: The ONLY exception I've seen for this deals with a share-point integration.

There will be other people that argue that look-up fields are just fine at the table level... I side with the MVPs that do this stuff for a living… unless using share point, don’t use them at the table level… only use them in queries, forms, and reports.

Finally, so that you can really get the help you need, and the other experts, mod, and users can follow what you’re asking in the future, the following are really a must read:
How to ask good questions
Posting guidelines

My apologies for the long post… just a lot of ground to cover--- and I tend to ramble... a little...

Most Respectfully
What do you mean it's still 95 outside... it's 3am!>.... oh... time for a nap :)
Aug 1 '12 #12
44 New Member
Hi Zmbd - thanks for your reply - gosh, 3.00 a.m. and you are still awake enough to think properly!!!

I have read the article on Normalisation, and I'm pretty confident that my tables comply with this. In the past, I've tried to get over using MTQs by using SQL Server, but had to give up with that when it came to the calculations.

By calculations I mean that I have an item of knitting, say a Jumper. Before I start knitting, I need to know that I have enough yarn to finish it.

Therefore, I calculate the Knitting Instructions (number of stitches and number of rows). This is the first layer of queries - Knitting Instructions.

Then I calculate the Stitches for every part of the Jumper (i.e. Back, Front, Sleeve, Collar) and for every section of that part (i.e. Welt, Body, Armhole, Neck). This is the 2nd layer of queries - Stitches - using calculations from the queries Knitting Instructions.

Then I calculate the Projected Weight of yarn for each of these sections of knitting within each part of the Jumper, by using the Stitches calculated above and multiplying by the Weight per Stitch from the Tension Square details. This is the 3rd layer of queries.

Then I add all these Projected Weights together to give the total weight for the Jumper - the 4th layer of queries - and here I get the message "Query Too Complex" (and sometimes even at an earlier stage than this).

Hence my son told me about using Make Tables to give Access less work to do running all these cascading queries.

And this works perfectly well, and gives me the results I want. Except that every time I run the MTQs I get bloat and need to Compact. The actual file size, compacted, is 375,000kb. Without all the Make Tables, it would only be half this size, but without them, I wouldn't be able to calculate the Projected Weight of Yarn required.

This probably sounds all very trivial to you, someone who is used to dealing with companies trying to make a success of their businesses. But for someone from the Knitting World, it is vital to know you have enough yarn before you start on a project.

So, once again, thank you for all your (and everyone else's) efforts at Bytes. My son tells me that I am trying to get Access to do something it is not capable of, that I'm using a database to do something it's not meant to do. But I'm determined to get as far as I can with it.

Thanks again

Aug 1 '12 #13
5,501 Recognized Expert Moderator Expert
You've tossed us a Rubik's Cube!
giggle - I suspect that the people here love puzzles

Makes perfect sense as for what you're trying to do. My Wife crochets and I used to know how to do really (really, really (x100)) simple knitted stuff (ok, so they were usually just pot holders, tried a sock once... three needles... Chemist, not Seamstress)

I'm using my Mom and Dad's PC; thus, no MSOffice for the next week; however, I'd love to see your database and tinker with it once I'm home and DSatino seems to be fairly sharp with this too given that D has already offered to take a look when you get a chance you should upload the file. This really sounds like something MSA should be able to do... even within a single query... or better yet a form with a few dropdowns...

(giggle... bring on the puzzle!!!)

Did you figure out DSatino's instructions on how to upload your zipped DB?

Ok... I really need to take a nap before the kids get up and the Wife wants me to help (3yr-Twins(S&D) and a 9yr DD that thinks she's 20... when did that start! :) )
Aug 1 '12 #14
44 New Member
Hi -z

I did try to upload the database, but got an error message that a Security Token was missing. I'm awaiting an update from Bytes Support team. Then I'll try again. If any of you would have any time to look at the database, it will be interesting to know what you feel.

I know I've built in lots of "Catch-Outs" (e.g. I've put a "Locked" field on every form, linked to a Module for every form. I had found that I was clicking on input fields and inadvertently changing the data unintentionally . Now I have every field locked until I click the "Locked" checkbox to unlock them. Long winded I know, but 65-year old idiot-proof!!!

Anyway, as soon as I hear from Support, I'll try to upload the DB again. Watch this space!!!

Aug 1 '12 #15
44 New Member
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.

Aug 1 '12 #16
393 Contributor
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.
Aug 1 '12 #17
5,501 Recognized Expert Moderator Expert
Yes... Make a backup, clear the make tables, and then do a compact too before re-ziping the file.
Aug 1 '12 #18
44 New Member
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.

Aug 1 '12 #19
32,584 Recognized Expert Moderator MVP

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 :-)
Aug 1 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Lonnie Princehouse | last post by:
I've run into some eccentric behavior... It appears that one of my modules is being cut off at exactly 2^14 characters when I try to import it. Has anyone else encountered this? I can't find any mention of such a bug, and stranger yet, other modules that exceed 16384 characters seem to work just fine. In particular, suppose that my module foo.py contains the following as its last line: thing = "goodbye world"
by: Steve | last post by:
Hi, I'm trying to read a binary file into a buffer: std::ifstream ifs(fileName, std::ios::in|std::ios::binary); if (!ifs) return; ifs.seekg(0,std::ios::end); int len = ifs.tellg(); ifs.seekg(0,std::ios::beg);
by: Lilith | last post by:
I've been working on a program in VC++. The basic operation of the system I'm working with is that I create a file in on directory, the system picks it up and generates a report in another directory, which I then read in and to strip out the information I need. The file I'm reading is not named with any correlation to the file name I wrote out so I have to monitor for when a new file appears and check it to make sure it's the one...
by: janhm | last post by:
Hello, using vs.net 2003. i need to encrypt an xml file, then decrypt it when my pocket pc application reads the xml file, and displays its content. please note this is on compact framework. need some advice on how i should go about doing that, thanks,
by: Masahiro Ito | last post by:
Use ICSharpCode.SharpZipLib Can view files in zip library. Files are being created the wrong size when unzipped. Sample code I found in this newsgroup. I thought I could change from s.length to onefile.size, but that doesn't work. Any idea? Thanks. Dim zipfile As New ICSharpCode.SharpZipLib.Zip.ZipFile("S:\Temp\test.zip") Dim onefile As ZipEntry = zipfile.GetEntry("test.txt")
by: Joachim | last post by:
How can I, preferably using C#, find out the length, in time, for an AVI file?
by: kawkabnany | last post by:
i have creating abinary file but iwant to know how to calculate/know the file length because ihad error when i used filelength(f1 )for example?please help me
by: Ron | last post by:
New discovery. If I take a perfectly good database, and "compact/repair" on it with Access 2000 (seems to be at multiple sites--I've tried it with my system here, at another office on an entirely different network), it damages the file somehow. The user's machine that did the compact/repair can see the file fine. But any networked user can't get in. I can double click on a good database file from any user (over the network) and it...
by: DumRat | last post by:
Hi, I'm currently interested in playing a sound in win32 API. I found out that the function PlaySound() does pretty well. However, none of the examples I have seen specify how to use PlaySound with SND_MEMORY, that is, playing a sound that is in the memory. Using the function to open a file and play it is too time consuming (At minimum it took 60ms on my machine.). So, I want to load the sound into memory, and play it using the...
by: nondos | last post by:
Hello I wrote a sub that read stream and then write it to file I try to track the stream by collecting the stream length every time like this: Dim s As StreamWriter = New StreamWriter(_filename , False) response = streamReader.ReadLine()
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.