Database Doubles In Size! Help!!
May 1, 2006
I am a novice to db design but I started my db quite a while ago but have only just noticed it keeps doubling in size periodically, even though I have not added many records or changed much. The first time I noticed this phenomena it was at 65Mb (already too big?), it then periodically (and randomly) doubled in size until it was about 480Mb!! I have been told that when db objects are deleted they are not actually deleted and this would explain the size explosions I saw. I have compacted and repaired the db and it now stands at a much more likely 11Mb. However, I really wanted to understand why this occurs so that i could prevent it from reoccuring (I have set my db to compact on close now - which seems to keep it small in size).
I have a whole load more questions aswell but you will see those in the near future!!
thanks in advance.
View Replies
ADVERTISEMENT
Apr 7, 2014
I delete the old 10MB table and import a new 10MB table and now the the DB is 20MB - why is not 10MB - number of records is about the same not double. DB works fine and you can't tell any difference except the size.
View 5 Replies
View Related
Aug 29, 2005
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc
View 3 Replies
View Related
Sep 7, 2011
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
View 3 Replies
View Related
Jan 3, 2006
I have another problem with a query I am doing today. When I run the query it brings up everything I need, but 5 of the Promo#'s are the same, but they have different # of Executed in them. They should not be like this, they should be together in 1 Promo#, not broken into 2. Here is my SQL code:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo AS [Executed Demos], IIf([Promo Executed]![CountOfPromoNo]=0,0,[Promo Executed]![CountOfPromoNo]/[Promo count]![# of Demos]) AS Percentage
FROM [Promo count] INNER JOIN [Promo Executed] ON [Promo count].PromoNo = [Promo Executed].PromoNo
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo;
Its taken from 2 queries Promo count and Promo Executed.
View 6 Replies
View Related
Mar 11, 2007
I have a table which displays playerID, YearID, RosterID, and Games
played. Then I have a query that looks like this
SELECT Positions.playerID, Positions.YearID, Positions.rosterID,
Max(Positions.[Games Played]) AS [MaxOfGames Played]
FROM Positions
GROUP BY Positions.playerID, Positions.YearID, Positions.rosterID;
My problem is that I dont want any doubles so if a player have 2
rosterID's in 1 year I just want to see that roster ID associated with
the MaxofGamesPlayed. How do I do this???
View 3 Replies
View Related
Mar 28, 2007
I'm sure that this issue is simply. I've done it before, but for some reason I'm having problems today. :confused:
I have 4 tables in one database as follows:
1) Sort Table = Sort #
Sort Date
Qty Sorted
Qty Rejected
2) Sorter Table = Sort #
Sorter Clock #
3) Operator Table = Sort #
Operator Clock #
4) Defect Table = Sort #
Defect Type
Defect Qty
When I attempt to query these tables, my totals increase.
EX: I have 500 items sorted; 100 defects; 2 sorters; 2 operators and 100 defects of type 1 and 100 defects of type 2.
My query results are 1000 items sorted with operator 1 having 100 defects of type 1 and 100 defects of type 2 and operator 2 having 100 defects of type 1 and 100 defects of type 2.
My quantity of items is doubled because it took (2) operators to make the items. My defects are doubled because it took (2) operators to make the defects.
Please help.;)
View 1 Replies
View Related
Sep 12, 2014
It doubles some records.
Basicially I have two tables, which are Linked tables and they are linked to two Excel files. Each of the tables has almost the same information: Stock Number, Location and Lot.
The purpose of this query is to check Quantities on both spreadsheets to see if there are any discrepancies. Query works fine if Stock Number, Location and Lot are the same on both spreadsheets but if they differ somehow i.e. Stock Number and Location are the same but Lot is different then Access doubles/triples records.
I have a feeling there is an error in Relationships. I googled it and I may need to set-up many-to-many relationship but the problem is that Access does not alow me to do it. I created a junction table but then I cannot set-up one-to-many relationship between tables, which I think is because tables are Linked.
Code:
SELECT DISTINCT [Query Union].[Stock Code] AS SKU, [Query Union].[Location Id] AS [Location No], [Query Union].[Lot No#] AS Lot, IIf(IsNull([TMP2 SOH].[Physical stock]),0,CDbl([TMP2 SOH].[Physical stock])) AS [TMP2 Qty], IIf(IsNull([TMP1 SOH].[Good Stock]),0,CDbl([TMP1 SOH].[Good Stock])) AS [TMP1 Qty], [TMP1 Qty]-[TMP2 Qty] AS [Variance (TMP1 - TMP2)]
FROM ([Query Union] LEFT JOIN [TMP2 SOH] ON ([Query Union].[Stock Code] = [TMP2 SOH].[Item number]) AND ([Query Union].[Location Id] = [TMP2 SOH].[License Plate Id]) AND ([Query Union].[Lot No#] = [TMP2 SOH].[Lot number])) LEFT JOIN [TMP1 SOH] ON ([Query Union].[Stock Code] = [TMP1 SOH].[Stock Code]) AND ([Query Union].[Location Id] = [TMP1 SOH].[Location Id]) AND ([Query Union].[Lot No#] = [TMP1 SOH].[Lot No#]);
View 5 Replies
View Related
Jun 15, 2005
Hello friends,
I had converted a Access 97 database to Access XP. The original size of database was about 38 MB.Last week I worked with some queries of different types.Now the size is about 135 MB.How should I reduce the size of database again.I really also dont know whether it happened because of queries... :(
View 5 Replies
View Related
Oct 17, 2005
Hey all,
I am not very gud in access, so I need your help. I am facing a strange problem..... I have created a database which consists of four tables....out of which two tables have relationships. When I make my database and create all the tables as described...its initial size is 320 KB around. When I put data in it, its size increases accordingly....I noticed a strange thing that when I entered some data in my Database it reached around 5MB, but when I deleted all the records from all the tables, the database size didn't decrease. It actually remained of the same size 5MB.......
Why so??? Don't you think that the database size should be decreased when I delete my records from all the tables. Is there any option which I need to select to decrease the database directly??? Well just FYI, I am using JAVA as front end .......
Regards,
Vikas Bhandari
View 2 Replies
View Related
Aug 22, 2006
Hi,
I have created two databases to try and speed up some data manipulation. One database is acting a a 'sniffer' and runs make table, delete, append and normal view queries. the append and make table queries will either write to the other database (the receiving database) or to itself. I have therefore managed to reduce a run time for these queries from 10 minutes for all queries, to less than 1 minute (as previously all in one database using forms).
The problem is these databses contain only about 10 tables to which I am adding data (non of them contain more than 5000 records at the moment) and I also have approx 50 ODBC linked tables (about 30 MB worth so I can retrieve data i want to write to the tables).
The databse has run through my 10 queries about 50 times (each time pasting new info to my receiving tables - still no more than 5000 records) however both database have increaed from a few MB to over 900MB - Is there any way I can stop this from happening as this seems to be a rediculous increase in size - i am also having huge problems compacting the databases.
thanks
View 1 Replies
View Related
Aug 6, 2007
I know this topic has been floated around a bit, but I don't see any easy solution.
When developing a new projrct or editing an old one, the Front End size grow and can be compressed.
I was keeping an eye on it with this last new project. My front end code base got to about 12 megs compressed. I thought that wasn't too bad, until...
For shxts and grins, I created a brand new database and imported everything from the 12 meg one. This is a pain to get all the code references reset in the correct order. The resulting file size? About 5-6 megs.
Now why couldn't the repair and compression tool do that?
There is NO data stored. This is a code base front end only. I had the options set to compress on exit.
Does anyone else have this issue with ACCESS 2000 and XP? :confused:
Thanks
Dave
View 1 Replies
View Related
Feb 26, 2013
I recently deleted about 2/3 of the records in my database and notice that my file size did not change. Do I have to run a special procedure to condensed my database?
View 3 Replies
View Related
Sep 30, 2005
Hey guys,
Was wondering, what is the recommended maximum size for Access databases? I've heard that for very large databases that oracle is preferred. Would Access be appropriate for something on the scope of 5000 records of roughly 6 text and 2 number fields? Do people ever notice performance effects on databases of that size?
Also are there any ways to help reduce the size of the database? Do you recommend change the default field sizes where appropriate and would this have a noticeable effect on a database of this size? Any other suggestions to help keep the database manageable, and functioning?
I'm basically trying to figure out if there's much to worry about here, before I start importing my data. Thanks.
View 3 Replies
View Related
Mar 5, 2008
I have searched this site but could find no similar questions. SO..
I am converting an Access 97 database to Access 2003.
I have compacted the 97 database before doing anything else.
The size of the 97 database is 91Mb
I have gone into Access 2003 and performed a database utilities>convert database>To Access 2002-2003 format and it has produced an 03 database with a size of 68Mb
I have also created a blank 03 database and imported all objects from the 97 database, changed all the text/memo fields to Unicode Compression = Yes and compacted the new 03 database and the size is 90Mb
What is the Auto conversion process doing that I aren't doing when importing objects that saves it an extra 22Mb? Plus is there anything I can do to free up that extra 22Mb again?
Thanks. Dan.
View 2 Replies
View Related
Apr 5, 2008
I just got done migrating the tables in my database to a SQL server, I went in and deleted the extra table that was created with the migration manager so all the data in my database is on a SQL Server, For some reason the size of the database is still the same. Why is that?
I even when in and deleted the tables query and forms but the size is still the same, if there some setting that im missing?
Thanks.
View 3 Replies
View Related
Jan 23, 2007
I had a massive Access file of almost a GB in size. and I deleted all records off the tables hoping it would shrink and my purpose was to have the tables alone in their structures. However despite deleting all records, the database file size seems to stay the same. anybody knows why this is so? I don't think we can "commit" changes to databases done in access as it is done in other databases. anybody knows why this is so?
View 3 Replies
View Related
Oct 22, 2013
I have a database (Access 2003 and I am using it with Access 2007 with user level security) on shared folder on internal Dell server and I have created a shortcut to each user's machine to access it. This days I am facing a problem of db size as every 10 days it's increasing upto 2gb but the strange thing is when I am doing cut / pasting same database to my desktop from the server shared folder it will become 80 mb only and I am again placing that db (80mb db) into the server and it will work. What is a reason behind it? why my 80mb db is showing 2gb db on server?
View 1 Replies
View Related
Aug 11, 2015
I have adopted someone elses database. It is a front end with about 100 linked tables/forms/reports/modules. The problem is that the database is really large. I would think by looking at the objects that it should be around 5mb tops, however after compacting it is still 63mb.
Is there a way (vba or otherwise) to look at each object and get its size in KB?
Once I can figure out which object is taking up too much space I can work on making them smaller.
View 6 Replies
View Related
Jul 14, 2006
Hi there
I want to know if anyone has taken a large database (e.g 400,000 records) and had to take a subsection of this so to reduce size. (E.g you randomly would like to take 20,000 records). I need to reduce a database size for a test situation but would like to know what I should consider when reducing the size in this way.
Do I take the base database structure – table organisation with fields linkages etc and load data in?
I have some help in this task but would like to know of any advice that could be provided.
Many thanks
View 1 Replies
View Related
Aug 27, 2004
I have some databases that I am importing data into but the records are so large (over 4 to 6 million) that they are making the file reached the 2 gig maximum.
Questions:
1) I have the imput tables broken down in to thirds of a month (i.e. Day 1 to Day 10, Day 11 to Day 20, Day 21 to Day 31) so what I was thinking is that there might be a way to just like three different databases together or four and have one be the master puller of the other three?
2) Currently, I have reports and queries that pull from one large table. So how would I set up the query reports etc. to pull from all three databases into one reports or query. Or is this not possible.
If you think of a better way to arrange or work around this problem then I am surely open to suggestions.
Thanks,
beachdude
View 5 Replies
View Related
May 13, 2014
We have an Access 2010 database that is accessed by some users from another location. Each user has their own copy of the front end. For some reason two users in our Sydney office enter orders, but after they have entered and saved the order, sometimes for no apparent reason they get an "invalid argument" error message and all the data in the order has been converted to hieroglyphics. I cannot seem replicate this from my server location.
It seems to only occur from our Sydney based server. Could it be that each user's copy of the front end is located on the same virtual drive on our Melbourne based server? Should I move the Sydney user's copies of the front end to the Sydney based server?I think it might be the growing size of the database but I compacting doesn't reduce it by much.
View 3 Replies
View Related
Jul 10, 2007
I am developing a database and testing it with dummy data. With 50,000 records, the query process using SQL command is just OK. But if I double the total records, the query process takes longer time. The query is to make recapitulation using SUM of SQL command based on certain criteria. Any idea to make the query faster?
Thanks.
View 2 Replies
View Related
Feb 13, 2008
Hi
I really need help with this. I have a database that is quite large and is accessed through VPN. When I shrink and compact the database it goes to a respectable size. But over time everytime a report is previewed the size of the database grows. Or every access it grows to double its size in no time at all.
Any ideas... on what would make it grow and how to stop it.
Thanks it is appreciated.
View 11 Replies
View Related
Apr 4, 2008
I'm having a problem with my database filesize getting larger with the more searches i do. At first i couldn't figure out how it was getting so big, but then i tried a few things and came to the conclusion that every time i do a search it increases its filezile.what made me notice this at first is that the file size of the original copy of my db is 13MB, but if i Save As a different name, like Assets_Backup, then the size of the new file is only 2MB, and after i do some searches it increases again.here is a file with just my search form and search query so you can see the code for them. i removed everything else to shrink the file down, which also made the search form not function properly, but i thought the design and code might be enough.is there something that i can change to keep it from remembering every search?thanks for any help.--------------------after searching the topic a bit more i've found that compacting the database will shrink it down to 2MB, but is this something that will have to be done on a regular basis, or is there a way for it to do it on its own?
View 5 Replies
View Related
Aug 23, 2004
hi, everyone.
here is the situation - i have a database in which i originally imbedded a bunch of bmp images. then i figured out another way of presenting my data, and i deleted all those fields with images from my database. the database file size with the images was about 90MB, but after i deleted the images it was still 90MB. why??? at this point the database contains to tables, one 2 by 2 with some text, and the other is 20 by 3 with some text, and the file size is 90MB. i totally dont understand this. can anybody explain to me what's going on?
thanx!
View 1 Replies
View Related
