MS Access- File Size increasing dramatically (1 Viewer)

[Todd Hochard]

All,

I set up a simple database using Access 97. It's a couple of tables, a few forms, and some queries. Upon completion, entry of about 60 records, and compacting, the file size was 312KB.
I attempt to add ONE record, with a total of maybe 500 BYTES of characters, and file size jumped to 500+KB.

The reason I bring this up, is that the user added about 5 records, maybe 1500 characters total, and the file size is up to 1708KB. This wouldn't have anything to do with Field Size limits that I put into the tables, would it.

I haven't had a chance to get into it yet, but I thought I'd put the feelers out. Any ideas?

Todd

 

[Shayne Lebrun]

Compact it. Tools->Database Utilities->Compact and Repair, IIRC, in Access2K. Dig around and you'll find it.

 

[Todd Hochard]

Well, I figured that Compact would fix it, and it did. The 1708KB file dropped to 468KB with compacting.

Which is all fine, BUT-

This doesn't help me much. The DB was supposed to be plug and play, switchboard-driven, so that the user didn't have to be burdened with this (as she's not very computer savvy). So, is there something in the way I've constructed the file to cause this?

I've searched the web, but not much found, other than the decompile function. I may try that.

Todd

 

[Chu Gai]

myself i'm an access 97 user but i believe the latest incarnations of access allow for compacting to be performed by the database in a user transparent fashion. now if you have any pictures in the database, that will accelerate the database bloat.
forget about searching the web for information. the best places to post questions such as you have are on the newsgroups.
for example one has:
comp.databases.ms-access
microsoft.public.access.forms
microsoft.public.access.macros
you get the idea. while newsgroups can be accessed off of the web or through Outlook or Outlook Express, I've found it preferable to use Newsgroup Reader programs. Gravity and Agent come to mind. There's a fully functional and quite good, free version called aptly enough, Free Agent...that can be found at www.forteinc.com i believe.

 

[Shayne Lebrun]

Access is just doing it's job.

For example, when you 'delete' a record, Access is probably simply marking it as 'deleted.' And so on, and so forth.

All ye need do is script the compact to happen as appropriate.

 

[Todd Hochard]

Access is just doing it's job.

Allocating 200K+ of space for one additional record containing

 

[Shayne Lebrun]

Ok, you're the boss. I'm not going to argue with you. Lord knows in my five or six years of database and network administration, I've not picked up a thing or two.

 

[Todd Hochard]

Shayne,
I don't doubt, for a second, that you are far more knowledgeable than I re: databases. This is still the realm of "hobby" for me.
HOWEVER, "that's just the way it is" just doesn't float. As I said, of the five databases I've constructed, this is the only one that insta-bloats when I put in ONE record (again, 500 characters of text, total). It just doesn't make any sense.
The others are similarly constructed (but larger, with thousands of records, versus 65 or so), with similar (but more complex) switchboards, queries, forms, etc.
As a professional troubleshooter, regardless of database knowledge, I can recognize when "one of these things is not like the other..." I must be missing something...
The big trouble is that with Access2000 (on my machine), I don't see the same issue. And, I won't be able to get to the other machine until late in the week.
(Just to be clear, it bloats every time a record is added, not just prior to the first compact.)
Todd

 

[Shayne Lebrun]

Hrm. Is this database something I could take a look at? Sounds like you're interested in the 'why the hell?'s as opposed to the 'is this my fault's.

If I can take a look at it, I might be able to render a better answer for you. Otherwise, all I can do is start reeling off possibilities.

 

[Todd Hochard]

Sounds like you're interested in the 'why the hell?'s as opposed to the 'is this my fault's

Is this a jab?
Again, as a troubleshooter, I am always interested in the "why." It's highly likely that I did something wrong (I call it the "shitfinger" concept)- I'm certainly not trying to disown the problem. I've done the same thing with the others- that's why it's vexing.
If you're genuinely interested, then, sure, I'll send it. If you're simply interested in belittling me, forget it.
Todd

 

[Kolya]

I attempt to add ONE record, with a total of maybe 500 BYTES of characters, and file size jumped to 500+KB.

So, what's the schema like? Could it benefit from some normalization? How are you storing your char data?

 

[Shayne Lebrun]

No, it's not a jab at all. It's a simple observation. In my experience, there's two kinds of people, when it comes to something not working how they expect it to. The first kind says 'What the hell?' and basically want to be reassured that it's not their 'fault' or doing or whatever, and then are happy.

The second kind says 'Why the hell....?' as in 'why the hell would it do that?' and wants to know the deeper darker secrets. That would seem to be where you are at the moment, so I offered to take a look at your specific database to see if I could offer some insights that are specific to your situation.

I apologize if you took offence to something I said, for none was offered.

 

[Todd Hochard]

Then, I apologize for jumping the gun.
I definitely fall into the 2nd category. I like to get to the bottom of everything.
New problem- I can't get it to bloat anymore. The file size increases about 14K on my Access2000/WinXP setup at home after adding a few records. Hardly a problem. I'll try it at work on our Win98/Access97 machine, and see if I can duplicate the symptoms.
I think the real problem is that my user has an e-Machine. I HATE those computers!
Todd