I have a strange issue. First off I have a front end in Access 2000 as a MDE file linked to a mdb data file.
Issue number 1: The MDE file almost doubles in size daily. Yes, the mde file not mdb (it grows slowly and expectedly as data is added and deleted) . I expected the MDE file to stay a static size. Is this normal, what causes this?
Issue number 2: I had on 3 occasions a query in the mde file suddenly pop up with a ”cannot repeat table name ‘Nameoftable’ in FROM clause”. I go into the mde file, SQL query view and delete the extra table name and all is fine for awhile. After the second time I did away with that quey and rewrote the query for my form a different way. It’s come back once again now for anther table. Has anyone heard of Access doing this?
I also note that the modified date of the file keeps changing. I am wondering if someone is tampering. My client that has this software is very naïve.
The MS site has no information regarding either behaviour.
Hi, i am new to access. Anyone know why my .mdb file size Growing. Even when i delete the data from table and done with the compact and repair. i am doing my first small project with one table and 10 querys and 5 repots. each time i edit the query or report the file the size goes on increasing. i have not more then 100 records in my database. i only edit my records when ever i edit my records .mdb file size grows so any one could help me to make it constant. plz help
I have an MS Access database. It contains tables linked to tables on SQL Server. Therefore MS Access is really only acting as a front end to the SQL Server database.
I converted the MS Access database to a .mde file.
I noticed over the past few days that the .mde file is growing in size. I don't understand why this is the case. The data is being stored in SQL Server and the database only contains links to the tables so I wouldn't have expected the size of the .mde file to change.
Does anyone know why the .mde file is growing? Is there anyway I can stop this of growing?
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?
on a form i have 2 fields one for import a file with the filedialog 2e for the size of that file how can i have te size from the file in that field can someone help me with that code
Hi, I was recently searching on the site for information leading to the compiling of an .exe file from the Access database. What I come to realize is that this is not possible. I was led to the MS Visual Studio 2005 for MS office studio and the MS Office Access 2003 Developer Extensions for it to use the Access runtime (As I came to understand) without having to install Access. If I am at all wrong up to this point please let me know. I was just wondering about that for clarification, my real concern is regarding the size of my database and the problem making it into an MDE.
As of now, my program is at roughly 30 mb, which I did not think was too incredibly large. However, Access will not allow me to create the MDE file. Where I think my problem is lying is that I have images scanned into the DB and then text boxes on top allowing me to essentially type a form and print it out.
Is there a better, more size efficient way of doing this? I have a hunch that it is the images that are not allowing for the MDE export, but I did not think they were all too large in size. If anyone has any comments that may help I would be very appreciative. Thanks in advance.
I have developed a Access program which import excel tables into the the database and queries some data and writes it into an excel file...
The problem I am having is that after the program is loaded... the file size of my mdb file increase by more than 3 mb..
I tried deleting all the objects in the file.. and the size doesnt reduce at all, When I import tables, I delete the previous one.. so I don't think it would be that...
The only thing I think the problem mite be is the life line of the variables..
Is it that after I run the program... the Variables stays there and ocupies space...??
afaik, the variables "dies" after the program is finished loading.... if not, is there a way to "kill" the variable at the end of the program? so far, at the end of the program, I have set my objects = nothing.. to release them... but doesnt seem to help at all
Yesterday my DB was 5MB. Today its 50MB. I haven't embeded any images etc... I can't see any reason why the DB would expand like this. The only thing I can think of is that perhaps there is a variable somewhere that allocates memory. What should I be looking for??
I have created a db and am having serious problems, the size of the complete db is 3mb, and when split its 500k, the trouble I am having is that when putting the db on the network server and you try and open it based from home it takes so long for both the full db and the split.
The main db has one page that all staff need to see and have a quick response when opening on VPN client. The form I want to open has a combo box that lets the employee select the company and all the information updates on the form.
I tried making a page in acces to do the same thing but having problems
Is this possible what I am trying to do or is there an easy faster option.
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?
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.
I am trying to send a "EMP" table in an excel format using "SendObject". User clicks the "Send Mail" button to email the table in excel file. The user then clicks on the "Exit" button to exit. This wipes out the data in the table "EMP" as was expected. Every thing goes fine..... but the size of my database increases by more than 100 MB. (Note that the table in the data has been deleted & the data is around 59 MB).
Any suggestions on what could be causing this increase in file size.
Is it possible (using the menu or VBA code) to find out the size of the objects in an Access database file?For example if the .accdb file is 300Mb, is there any way of comparing the size of the numerous tables/forms/reports/queries etc, that make up the database?(I know that if I created a new access file and imported the objects from the working database that could give an indication but that would be quite a repetitive process).
when I open a pdf through access weather it is by hyperlink or attachment it always opens the full size of my monitor, if I open a pdf outside access then they open ok , how do I set the size that access opens the pdf the size I want.
I'd read a few places that embedding pics wasnt the greatest of ideas, but I thought that 1 JPG embedded onto 2 forms would be ok and would prevent the pic from being deleted or moved. (Theyre always screwing with the server here.) But it's making my DB huge.
No pics embedded Size: 808kB
1 Pic Embedded into 2 forms Pic Size: 156k JPG DB Size: 135 MB
Is this normal? I could understand a 156k pic adding somewhere around 156k to my DB, but 134 megs? Anyone have any insight?
I'm having a problem with mdb file size. I'm importing a large amount of data from a number of tab delimited text files via a simple transfertext function. The process goes: empty the tables in the database, then import the data into the tables.
All this works fine, but the file size rockets to over 1.5Gb. When I then compact and repair, it goes down to 420Mb. I'm not deleting and recreating the tables, and at no point is there 1.5Gb worth of real data, so what's causing this?
N.B. I realise I can call compact and repair following the import, but this is going to take too long as they are user-initiated imports.
When I bring back my access 2003 project from work back home, i open the project at home, I am using access 2002 or 2000 format.
Then I notice if I save a copy of my work using access 2002, while initially using an access 2003 database, the file size decresaseas dramatically, from like 3mb to 800 kb?
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!
I have a field in my database (I'm using Access 2007) configured as Attachment under the DataType column on DesignView.
I was expecting to find an option to limit the attachment size (max 250Kb) and more importantly to limit the filetype you can actually upload. In my specific case only jpg should be attached.
I have an Access 2010 database .. and will be adding about 25,000 records a day to it. So it will get pretty big fast .. so 9 million a year. Is this too big for Access ?? Or is it more a issue of file size .. I know that Access 2010 can go up to 2 GB. I am interested in how I can maximize my performance and not have the database.The data is as follows .. One table. The other tables will be small, just this one will increase in size really.
Id .. Autonumber (primary key) When .. datetime Radio .. integer (indexed .. w duplicates) Group .. integer (indexed .. w duplicates) Type .. text 4 TransType .. text 2
I am considering a purge of old records if needed .. like only keeping the most previous 5 million records or some reasonable number. Like .. then I would think I should try to number my records backwards (using long int rather than autonumber and number records backwards on import) .. ie record 1 would be the most recent one and only keep the last X number of them for performance reasons.
Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.
Text file example (text.txt):
R111 WC 8/21/2012 7:00 Doe, John doej 10110110
First Question? Y Second Question? N Third Question? Y ... Seventeenth Question? 10
As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.
I would like to have this data imported as shown in the attachment. Example.zip
Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.
I have a problems which seems simple, but it's made me doubt everything I have learned about this; I suddenly find myself unable to grasp what I belive is a very small and easy solution, but I cannot!! (Either that or I have stumbled upon a real conseptual problem without realising it :) )
This is what I had working nicely
Supplier supplyer_ID : ID and PK of a supplyer more boring attributes
Ingredient ingredient_ID: ID and PK of ingredient Name Supplyer_ID: FK to who the supplyer is
Recipe Recipe_ID: PK and ID Recipe_unit_weight: How big each unit should be other stuff
When making a recipe it is obvious that there can be many ingredients and that an ingredient can be used in many recipes, thus it's a many-many. I therefore created a junction table:
Recipe_Ingredients Recipe_Ingredient_ID: PK (I was not completely sure if this one would come in handy, but it has not so far caused any problems (that I can see) Recipe_ID: FK to which recipe it is for ingredient_ID: FK to which ingredient Receipe_ingredient_amount: How much of the ingredient Receipe_Ingredient_order: In which order to put into recipe
I had a nice form where the Recipe was the parent and the subform contained the ingredients. The subform was driven by a query that had the columns from the Recipe_Ingredient table plus the name of the supplier as well as the version of the recipe (as there might be many). Using a combo to get the name of the Ingredient and binding on the Ingredient_ID I didn't need to also bring in the name of the Ingedient in the query.
Now enter problem: For professional people this lacks one thing; Being able to use another Recipe as an ingredient. After a lot of thinking the solution I envisioned was;
Add a column in the Recipe to flag that is a base recipe (true/false). Then adding a new column to the Recipe_Ingredient table, namely Base_ID, which is the Recipe_ID for which recipe to use. As there is already a Recipe_ID referring to which the ingredient belongs, I needed a different name. Additionally I had to change the relationships in the subform query to be left-joins (always include all columns in the Recipe_Ingredient and join in whatever you can find in the other two.
This works for diplay purposes (when I hand edit the join table). The problems I get is when adding a new recipe as ingredient in the subform;
When adding a base (recipe) at the point of saving the record my Recipe table validation kicks in saying you need to have Recipe_unit_weight > 0!!! I am not planning on adding any recipes here. This does however not always happen, as another error also kicks in: Microsoft Jet database engine cannot find a record in the table 'Supplier' with the key matching field(s) ''. As it's a recipe there is no supplier and I'm assuming it is trying to find a supplier.
If I strip away the extra columns from the query powering the subquey (like supplier and base (recipe) version) it works like a charm. In effect it means that the query only consists of the junction table.
It seems that having a two-way junction table with left-join with both "parents" is causing problems. Am I doing something fundamentally wrong here or is it Access that don't get the picture (and how do I tell it).
Okay,This will probably sound confusing, but I'm going to try my best to hone down the issue.First:There is an imported DBF table that I have to perform calculations on, primarily of the string variety, but also with a few type conversions (such as: String "06" = CDate("1/1/" & "06") -> into a Date/Time field)Okay, given that, I was doing many things all at once, but found by doing it in several stages allowed me the ability to: Narrow down the actual amount of items in the DBF that I will import Speed up some of the calculation in the TransferSo I have two tables:tbl_OriginalTape_ID: Text(10) - {Primary}Title: Text(50) - {Primary}Title_Rem: Text(50)Category: Text(3)TitleLen: NumberActionQuery on Tbl_Original: INSERT INTO tbl_Original (tape_id, title, title_rem, category, TitleLen) SELECT qry_dbf.tape_id, qry_dbf.title, qry_dbf.title_rem, qry_dbf.category, Len(RTrim([qry_dbr.title])) as TitleLen) FROM qry_dbf;tbl_CalcTitlesTapeID: Text(10)MovieTitle: Text(100)AltTitle: Text(100)Remarks: Text(50)Paren: Text(20)Bracket: Text(30)Orig_Id: Text(10)OrigTitle: Text(50)Now to take the data populated into tbl_Original, there are many rules that I have to follow. Example: if Original.Title = {text}>>{text} the the left of the double ">" signs, is the MovieTitle, to the Right is the Alt Title. If in the MovieTitle there is a (*) {parenthetical} the parenthetical is removed and the text insde the () is instered into Paren. Similar rules apply for Bracket, just with [] square brackets instead of Parentheses (). Also, if the Trimmed Title Length from the Original insert =40 (the maximum for the DBF field, AND the Original Category field = "SEE" then the title_rem field is appended to the AltTitle field of tbl_CalcTitles, and the the tbl_CalcTitles.Remarks field is set to "" (emtpy string), otherwise the title_rem field is populated into tbl_CalcTitles.Remarks. Also for attempted relationships for later use, I directly transfer the tbl_Original.title and tape_id fields to the Orig_Id and OrigTitle fields of tbl_CalcTitles.Yes, This is very annoying, and i've spent some time writing all the little funcitons to do exactly this, and be accurate every time I run my action query. (It is pointless to post here, since it involves way too much vb back end code to post it all.)qry_dbf: 33099 recordstbl_Original: 33098 recordstbl_CalcTitles: 33098 recordsNow, given this setup, we know that in my insert into tbl_Original, 33098 records of qry_dbf were unique in the paired tape_id and title fields. So now I want to link qry_DBF to the tbl_CalcTitles, so that I get exactly 33098 records from qry_dbf that match up witht he Orig_id and OrigTitle in tbl_CalcTitles. Yet, no matter what i do, I do not get this result. My last attempt was a LEFT JOIN from tbl_Original to qry_DBF and I got 33138 records returns. How did grow extra records. if I ONLY have 33098 in tbl_Original, and 33099 in qry_dbf, where are the 40 extra coming from?So I tried to link tbl_CalcFields to qry_dbf on an INNER join on the same fields, and now, this is purely mind-boggling:Each Time I alter the number of fields DISPLAYED from qry_dbf, the total number of records return alters. Example:tbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title: 33092 recordstbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title, qry_dbf.category, qry_dbf.format: 33072 recordsNow this is completely beyond what one would expect. I already know that there are 33098 unique records in qry_dbf, on the pairing of fields, Tape_ID and Title, of with all 33098 records are contained within tbl_CalcTitles with the EXACT replica of those two fields. so If I am doing an Inner Joing of tbl_CalcTitles and qry_Dbf on those two fields which produced 33098 unique records, why would altering the number of DISPLAYED fields affect that outcome. Why am I not getting all 33098 records from qry_dbf that are contained in tbl_calcFields?Thanks a lot, this has been extermely nerve-wracking.Jaeden "Sifo Dyas" al'Raec RuinerPS - qry_DBF is SQL source defined as:SELECT tap001.*FROM tap001 IN 'tap001.dbf'[ODBC;DSN=Visual FoxPro Database;SourceType=DBF;SourceDB=D:Quarterly]ORDER BY tap001.tape_id, tap001.title;
Hello Everyone, Why does my database grow so large during the development stage? After compacting the database it is 64 megs, but only after a small number of changes it grows to 500 plus and more if I don’t re-compact it.
