Compacting And Repairing A Different Database Using Code
Dec 6, 2007
Hello
I am aware that there are a lot of existing threads relating to compacting and repairing but I cant seem to find what I am looking for.
Basically, I developed a complaints database for my employers and it is used by around 45 people at any one time. The database was one of firt I built so it is not the best and is serious bloatware. Our server management team gave me an ear bashing the other day because the BE was 145MB. I did a compact and repair which took it to 45MB. The problem is that I need to do this daily but it involves kicking everyone out of their linked FE so the BE can be compacted/repaired. As an interim measure before I rebuild the whole thing I am going to schedule a database to open that will compact and repair the Complaints Database BE. Can anyone tell me the code to use to compact another database other than the one I am using. I have been looking at the code in other posts but I cant get it to work. The code I was looking at is the DBEngine.CompactDatabase... but I dont understand what comes after this and why??
I had someone write some code to access an Access database. The code works with the database as is, however if I choose to compact & repair the database to minimize its size, the code no longer works. What could be different about the database once it's been minimized using the database utilities?
Thanks, Rick
P.S. Does compacting the database make it more responsive to users on the web that query the database, or doesn't it much matter?
i am currently making a database for my company which will take care of the invoicing ordering and any other aspects.. i was making good progress until my manager mentioned to me that theres a special ordering number system that they want to keep and want me to incorporate into my database.. seeing as this is my first db i decided to use autonumbers at the start of the project now i need to re-design my backbone the OrderID system which most of my forms run off..
Basically my company operate in two different locations and when one is selected in the order menu i need the prefix of the OrderID (ItemCode) to change. Ive put the tables and code in but i cant get the new system to work..
I searched for ways of creating a manual version of the autonumber and found something which i tried to replace my current autonumber system with, however ive kinda messed things up and im not entirely sure how to fix it.
please reply if you would be willing to help me i would greatly appreciate it, i dont think it would take too long for some1 who knew what they were doing.
I have a .mdb password protected with all types of objects (tables, queries, forms, reports, codes, functions, modules, etc) they too are password protected in VB, the .mdb works fine but when I tried to repair and compact the database from the menu tools option, very recently it started giving me the message of database has been corrupted and requires tobe backedup before proceeding with the repair options and the log report of this corrupted .mdb file is to be sent to microsoft for the review.
In short i am not able to compact the Database though it works fine - but the moment I try to compact it gives me this problem - I am worried and concerned as its looks good but what happening in the background can anyone in here sugguest the remedy or the precautionary measures.
This file is on the network shared by min 2 person at the same time.
At work we have a database that is about 60mb and whenever we try to compact and repair it access crashes. Anyone know why it does this or whether it even matters?Thank you! tim
the following error message appears when trying to access my database. I can't seem to get into it at all, not even by trying to import the data tables over into a new database. the following message appears:
"The database has been converted from a prior version of Microsoft Access by using the DAO Compact Database method instead of Convert Database Command on the Tools Menu. This has left the database in a partially converted state. If you have a copy of the original format use the convert Database on the Tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database applications can't be recorded''..
the copy and the back up wont let me in either, this just suddenly happened.
Lets say you have a table with an AutoNumber field.You populate it with 100 records.You delete the last 10 records (91-100)Now, I certainly know you can refresh the AutoNumber sequencing by compacting the database so the next record entered is 91.
My question is about NOT refreshing the AutoNumber sequencing.Lets say you DO NOT COMPACT and DO NOT refresh the AutoNumber Sequence.So, in that scenario, after deleting records 91-100 from the table, is there any programmatic way to determine that the next actual AutoNumber for that table will be 101?
I have a Ms Access split database where in the backend database grows up very fast and needs to be compressed everyday. I have written a batch script to compact the same and have scheduled it to be run every night. But the script is unable to compress the backend every time since the users tend to leave the application open at the end of the day. Ideally i am looking for a command line script to forcefully close the back end database if open before attempting to compact it.
I searched on web but could not find a command line script for forcefully closing the back end database.
I'm just about done with my database and will be putting it on the user's computer shortly. How often, generally, should we compact and repair the DB? Is this something I should set as an event everytime the file is closed? Is there any disadvantage to doing it too often?
I have a database that will not compact (using 97) the error message I get is the microsoft jet engine stopped the process because you and another user are attempting to change the data at the same time.
I copied this database on my desk top, and absolutely no one is using it. I can 'repair" it, but not compact it.
Hi, I have a problem compacting a database. i used to do this all the time at my old job, yet here at my new company, all I get is the usual blank Access screen, the sandtimer mouse icon, and if I select any other program in Windows and the switch back to Access, it crashes "MS Access (not responding)
I appear to be having problems with the Compact/Repair feature of MS Access. When I compact/repair an .mdb file, even if it is empty (no tables, no queries, no forms, etc) access will make a db1.mdb file, but not follow up with the rename. I have had no troubles with a large multiuser database for the last 5 years and just recently noticed the db1.mdb, db2.mdb, etc appearing.
When compacting a database on a network an extra database is created and left called db1.mdb. I don't see this problem when compacting locally. Any suggestions?
Since we have installed the latest Windows critical security updates, upon closing an Access database, the database gets corrupted. Error code is c000020c.
The database that I currently work with has the option chosen to compact and repair on close so that if I am not present when there is trouble the users can close the db and the problem is usually corrected. I currently have a close button on the Main switchboard that closes the db. Is there a way to make a button or another way that will close the db that will by pass the compact and repair choice? I know that I can use task manager, but that usually creates db1, db2 and so on. Thanks,
I store this database on a flash drive and when I tried to compact and repair it, it said there wasn't enough room, so I got rid of some files on the drive and tried again, and now I just keep getting the message: Table 'TempMSysAccessObjects' already exists. But I don't find that table anywhere!!!
After compacting my Access2000 database it usually is impossible to add new records. The automatic counter does not continue with a new value but seems to have "forgotten" some of the recently added records and tries to use a value that is already allocated. After pressing <ESC> the next value is applied and so - after adding and escaping the appropriate times - it finally reaches a valid new value and the record is accepted. Ugly workaround. Any known solution?
We have a split access2010 application in which the BE is 1.6Gb compacted. It reached 1.8Gb recently, then I compacted it and it returned to 1.6Gb-ish, but some of the FE functionality then virtually ceased to run. Some (very stressful) hours later I discovered that some of the table indices were missing, and when I re-added them the FE functionality returned to normal.
Is it a known thing that compacting can have that effect? Obviously I can see the logic of removing indices to free up space, but with such potentially significant performance consequences I'm amazed that compacting doesn't include an explicit warning about the possibility.
I've done every search I could think of, but haven't come up with a solution. I'm having a problem with file system security settings of windows 2000. I have a FE/BE database setup, the BE is stored on a network drive that all users have access to. The FE is stored in the same spot on every workstation (C:dataXXXXXX.mdb). The FEs demonstrate considerable bloat and I understand that the solution is frequent Compact and Repairs.
However, each physical workstation is used by more than one user (different shifts) and the compact process removes the Everyone file system access from the database and replaces it with Administrators, and the current user; thereby preventing anyone other than the compacting individual from accessing the FE on that computer.
Where would I access the event properties for when the entire database closes? Something like the On_Close event for a form, but for the whole database.
I want to have Access prompt to back up the database upon exit, so when someone clicks the close button, a dialog box opens and asks "Do you want to back up?" with an Yes or No option. Yes would obviously run the backup code, and No would simply exit.
I was wandering if it is possible to implicitly execute code upon the opening of a database? If so how do I do this? I have code to convert the page settings of a report from a command click but wanted this to be done automatically...
Does anyone have a US zip code - time zone table or tables? I have been unable to find this data online without paying 150.00. I need this for a web application that i am doing.
