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 RepliesI 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.
I saw a demo that the user could copy and paste a JPG format file physically to ACCESS, and then something happened, and ACCESS could read the JPG length and width size (e.g. 1.3 meter length and 1.15 meter width) into 2 individual ACCESS cells which is acted the same as user input to that 2 clells.
View 14 Replies View RelatedI want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
I'm having problems importing a spreadsheet from Excel for a client.
They are using Access 2003 and Excel 2007, 3.5GB RAM
It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.
The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.
It returns error 3274: "external table is not in the expected format."
There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.
The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.
if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this.
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 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.
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???
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.;)
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#]);
I used to generate PDFs in A2003 with Leban's code, and they came out at about 10K.
in A2010 these same PDFs are coming out at about 250K - whether i use docmd.outputto, or still use the previous code
is there any way to reduce the size of the pdfs?
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 RelatedI 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.
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).
View 4 Replies View Relatedwhen 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.
View 2 Replies View RelatedWe 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.
I have a large database. I want to identify which objects take up the most space. Any way of identifying a list of each object with their relative size?
I can do this in sql server, but no in Access?
I have (from SPSS) many multiple regression analyses, and each one of them has about 7-8 independent variables. I need to find the effect size-in particular I want to have effect size above 1% and statistical significance p<0.001. I am familiar with p, but I cannot understand whether effect size is b, beta or R square and how to interpret it.
View 1 Replies View RelatedI am using Access 2007 database in work environment. When one of my colleagues tried to update some records in a table, it created a backup of the database.
The issue is that the size of the actual database is much smaller than the size of the backup. However all the data are present and I don't think any data has been deleted.
But I don't know why there is a difference in size?
Some of the tables in the database are linked from MS SQL Server.
The column in my list-box did not show all the text i save in the table field. is there any limited size for list-box's column to show the data ?
View 2 Replies View RelatedI want to set my paper size to user defined, but it not working,
<<<<<<<<<<<<<<<<<<<<<<<<<<
Set Application.Printer = Application.Printers("P1121E")
Application.Printer.PaperSize = acPRPSUser
Application.Printer.ItemSizeHeight = 9.35 Application.Printer.ItemSizeWidth = 26.9
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Showing "Run time error "2595"
"Cannot set this property when defaultsize property is set to true"
I try add "Application.Printer.Defaultsize = true or false" also not working
I have subform (Datasheet) is there some code e.g. after update, which can adjust size cell to lenght of data?
View 11 Replies View RelatedWill 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
unfortunately I am making a form using more than 255 fields which is the max for access 97. I tried using more than 1 table but still no help.
what can I do? will upgrading help?
thanks
andrewm
There is probably a real simple way to do this but a couple of us are here racking our brains. :o Is there a way to tell how big a table is size wise? example 1.2mb
Thanks,
-Sandy :)
Hi all,
this could well be a bit of a weird request so bare with me.
I would like to view the table size (in KB, not records) and I would have thought the Detail view in the Table tab would take care of this for me (i'm sure it used to, i'm using Access XP now) but I cannot get that particular view column to show. I want to see the file size for both Access tables and SQL tables that are linked in.
Anyone have any ideas ?
Thanks in advance,
Mitch....