In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.
The resetting of the index after deleting all the records is normal.
I am working in VB 2010 - using a 2003 MS Access database.
Hi all
I'm about to start on a new project - automatically compacting and repairing our databases every evening. I am confident that I can do this (just!), my only issue is deleting the lockfile in case any users have forgotten to log off the databases before they have left.
Obviously I will be unable to carry out the C & R if users are still logged in. Does anyone have any ideas as to the best way to tackle this?
Any help greatly appreciated!
I have a number of tables (I know someone is going to say you don't need a primary field for these tables but I want to use one) that are basically lookup tables for combo boxes. Generally I use an autonumber to identify the ID of each record. There are occasions when all the records need to be changed (i.e. delete all old records) - does anyone know how I can programmatically (or otherwise) reset the autonumber from the last used back to "1" if all records from the table are deleted.
Beeky
I have a primary key field called "Issue Number", with the properties set to auto-number.
After testing, I need to set it back to zero before going live.
I deleted all the records and did a compact and repair, but the first number that comes up is the one after the last one used during testing.
Help????
Thanks
I cannot cause a database to refrain from dropping several table indices during a Compact on Close operation. I am running Access 2010 under Windows xp.I have created a database that merges data in a linked text file with data in a linked database, appending the results in a local table. The database has the Compact on Close option set. The table has 27 indices (because most of the fields either are coded or are potential select or sort fields) and nearly 1 million records.
I run a delete query to clear the contents of the table, I close (and compact) the database, I reopen the database, and I run the append query. The indices in the target table are intact. I close (and compact) the database again; when I reopen the database, indices are missing -- sometimes about 10, other times nearly two dozen!
This table is linked to a larger system, which contains code to run the sequence outlined above. Whether run manually or in code, as stand-alone or as a component, indices are dropped. When I close the database with the table containing no records, the indices are not affected. After appending records, but before closing, the database contains 1.66GB. The drive on which the database resides has 42GB free.As suggested with regard to a number of other unexplainable issues, I have created a new database and imported all objects from the original database. The results persist. The larger system performs accurately without the indices, but the performance against a table this massive is horrible.
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
In my access applications i have used the database split built in functionallity to split my frontend and backend tables.I have created a mail mechanism and when the database grows to that level i recieve a mail, ask users to get out of the database and compact the database. But this is tiding. I have a scheduled job that compacts databases early in the morning before users logged on. But during day work databases grow enormous again. Well is there a way by VBA code to create an auto compact function that on idle will log off all users in the database and compact it?
View 2 Replies View RelatedGoodmorining,
I use Access XP and I have a problem with the Compact and Repair function.
When I start this function via the normal menu's access doesn't seem to respond anymore. I can leave my comp on for hours dut nothing happens.
He does make a copy (db1) with the same volume but then it stops.
Does anybody else has this problem and what can I do about it
Thanks
Kind regards
Rolf
Hi
does anyone know how i would make my database compact & repair itself either when i open or close it?
tried to do it via a macro but it says that it can't compact and repair when a macro is running :(
:confused:
Hi all,
This morning i opened up my database, it worked fine, ie could add new records on to the forms etc but when i went to look at my tables, queries, forms etc nothing was there and an error message about indexes came up.
I had a look through this forum and found a lot of useful information on compacting and repair. I compacted my database and it went from 89KB to creating a new database with only 28KB. The new one which it created works perfect for me and i have deleted my old one, as still no tables, queries etc showed up.
I didn't even have a clue i should have been compacting my database regularly, stupid i know but we all learn. My question is, when looking at threads i have noticed that when people talk about compacting, repair is mentioned too, do i need to run the repair tool now on my database? Or is it ok. Im not entirely sure of its function, i will keep looking though, Thanks for any replys :)
Hi guys,
This is just for discussion really. What does Compact and Repair actually do? If I was to set my database to compact and repair on close, am I in danger of losing any data, formatting on forms or anything? What are the pros and cons?
I would really appreciate some replies. Thank you in advance :)
When I 'compact and repair' on my office computer, two tables' autonumber fields become corrupt (I get a duplicate autonumber)
I took the original .mdb home and 'compact and repaired' it at home and to my surprise the tables didn't become corrupt.
Any ideas why the compaction causes errors in the office but not in my home?
Thanks very much
Hi,
Could anyone explain to me what compact and repair actually does? Made a backup copy of my database and did the c and r and reduced the size greatly! Has it lost data? Just would be good to understand how this works.
Thanks
Sam
I have a macro that will copy the database to a folder upon quiting.
The databse also is enabled from the options to compact and repair upon exit.
Problem is the copied databse will be always not compacted becaue the macro will run 1st before quiting (and hence before compacting).
is there a way around it
I have search the post and I found several articles about compacting and repair the DB. I just installed 2007. So you think that had an issue with compacting. Before I installed 2007 I had no problems at all. I even tried to import tables, forms and etc. into a new DB. I get error messages there as well.
I can say when I was working in 2007. I had to open the 2007 DB in exclusive. As of now I am just fishing for answers.
Bottom line I must have done something while working in 2007.
Any Ideas would help.
PS. I can go into the db with no problem at all, which is really strange. But when I create a new DB I can't import any of the tables or forms.
This is a very general question. so I am not sure if it has been asked before.
I know that compact and repair can sometimes fix problems with the database.
Should it be done only when problems seem to be creeping in? Or should this be done on a regular basis - say once a week or once a month?
Can compact and repair be set up to run either on a specified day of the week (for example every Monday), or day of the month (for example the first of each month)?
Alternatively can I have it run automatically when the database is closed?
If so, how would it be done?
I am using A2003 with Windows XP.
Okay... unlike my last post (although unintentional), this is legitimate.
Whenever I do a compact and repair on my database, access crashes and offers to attempt to repair my db. IF I allow it to do that, it will blow away all of my VBA code and so everything stops working.
If i uncheck the repair box, access will just close. I can reopen my db myself and it's find. I'm not sure about any repairs (never understood, what it's repairing), but it's definately compacted any everything is fine.
So why does it crash and why does it eat my db unless i uncheck the box upon crashing?
I have built a DB in Access 2002 which includes an automated Compact and Repair button. My problem is that the DB, (I have just found out) must run on 97. The code I am using is as follows:
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
This is producing an error in 97 because, I belive, the compact and repair are two seperate headings. Can someone advise me where I'm going wrong or even correct my code for me? Thanks!
I understand how to compact and repair and also the fact that you need to do it so that you can increase the efficiency of your database, but my question is why? I have a database that is used fairly heavily all day by 4 to 5 users. At the beginning of the day the file is about 91 mb, by the end of the day the file size is about 133 mb. Why does it grow so much in just an 8 hour period? What causes access to need to be compacted and repaired?
View 2 Replies View RelatedI have an Access database that's on our in-house server. This database has never been compacted/repaired. Recently our server crashed (but we were able to retrieve the database). Does the fact that the database has not been compacted have anything to do with the server crashing? Could that have affected it in any way?
Also, how often should the database be compacted/repaired?
Thanks for your help!
Hi,
I was wondering if anyone might be able to help me. What i'd like to do is to be able to get the access to trigger compact and repair database tool automatically just before it shuts. Is it feasible? or has to be done manually?
Thank you in advance
Gents I hope you can help me with this issue.
If my BE DB crashes I use compact/repair and it fixes the problem.
If I Just use compact/repair on my BE DB when nothing is wrong with it then it causes the data to disappear in certain fields.
Any Ideas what i am doing wrong. (Besides from telling why do you use this utility when nothing is wrong)
I have a large DB that I want to compact & repair daily. I have the option set to "compact on close"
is there a way to write a batch file that will open the program, then close it again so it will do a compact?
if not a batch file then any type of program that might be able to handle this?
Hi all. I am having some problems with some users, not allowing the database enough time to compact and repair before they switch off their PC or laptop. Although they have been told many times about the function needing time many are just impatient. This has resulted in various db1, db2 etc being created within the folder which are then being opened and used by other members of staff being less then smart.
My question is, is there a way to have some sort of splash screen or something similar run during the compact and repair process that tells the user what is happening. Any other alternatives or methods would be fine this was just my first thought.
Thanks
James
The compact and repair utility is very good.
Since all my record operations are done through menu, I would like to put a command button for this utility also. But I dont know how to do this. Would somebody help? Thanks in advance.