Tables :: Compact On Close Drops Several Indexes

Oct 5, 2012

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.

View Replies


ADVERTISEMENT

General :: How To Prevent Resetting Of Auto Number Indexes On Compact And Repair

Dec 3, 2012

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.

View 4 Replies View Related

Compact On Close

Apr 19, 2007

Hi,

I apply Compact On Close (Tools->Option->General tab). So, every time, it will compact the database when closing the file.
However, will it do repair the database automatically? It seems just do compacting.

View 1 Replies View Related

General :: Switch Off Compact On Close

Dec 25, 2014

I am using Access 2010.

Under options, current database I have DESELECTED "compact on close"

Still all my databases are compacting on close. How can I prevent Access to compact on close?

Problem is that we work under Citrix and sometimes things happens with Citrix that they have to reboot all the servers.

The databases tries to close and compact and don't get enough time, so I get mdb copies.

Besides I recently had only the compacted database1.mdb while my file Name.accdb was gone, so again the compact was wrong.

View 3 Replies View Related

General :: Compact On Close - Back End?

Apr 3, 2015

I currently manually run a compact and repair on the backend of a database at work, but was wondering if it might be sensible to set it to compact on close so that (in theory) it is done at least daily and therefore shouldn't take much time?

The back end resides on a network server, which is backed up continuously, so in theory it should be easy enough to roll back to a backup copy should anything untoward happen.

View 6 Replies View Related

Can You Compact On Close In Access 2000 Runtime?

Jan 4, 2006

Can you compact on close in an access 2000 runtime enviorment?

Also, if i select the option to compact on close will it work on every computer i copy this database to? Or is it a property of that computer?

View 1 Replies View Related

General :: How To Use Access 2010 Setting Compact On Close

Apr 17, 2013

How would I use the Access 2010 setting, 'Compact on close'?We have a back end on a server and many users with front ends on their computers. So would I set 'Compact on close' on the back end? And then it would compact when the last user logged out?

View 3 Replies View Related

Modules & VBA :: Compact And Close Database With Delay - Access 2007

Feb 12, 2014

"how to COMPACT the DB by introducing delay of 10 seconds and then close the DB".In the Database, I'm able to accomplish the "Compact" the database using the function below.

Function Compact()
SendKeys "%(FMC)", False
End Function

As my DB is quite huge, the Compact action takes around 10 seconds to complete.Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.

Function Compact()
SendKeys "%(FMC)", False
DoCmd.Quit
End Function

how to introduce this delay of 10 seconds and then close the DB.

View 3 Replies View Related

Forms :: List Drops But Won't Allow Selection

Jul 10, 2013

I've been working on this application for a long time, and my combo box always behaved well before, but today I click on the combo box, the list drops down, but I can't select anything. I've also tried typing the item on the list into the combo, which normally works, but I'm not being allowed to type anything there.

View 6 Replies View Related

Indexes

Dec 11, 2006

I am a beginner and can someone please help me with creating indexes( I think this is what I want to do)

I have a table for entering realtors

the primary key is realtorID (AutoNumber)

is there anyway to find if the realtor has been entered twice or prevent the realtor from being entered twice



thanks

Josh

View 1 Replies View Related

Too Many Relationships / Indexes

Apr 21, 2006

Hello
Sorry to add to the number of 'relationship' posts but a thorough search has failed to turn up anything similar to my problem.

I have a table tblSite
Each geographical site (defined by a PK 'SiteNo') in tblSite can contain multiple occurrences of a large number of types of geographical features (ie multiple 1-many relationships).

Eg each Site can contain
100 trees
10 ponds
5 streams
2 springs and so on.

I could have anything up to 50 or so different types of feature that are related on a 1-many basis to the site, linked by 'SiteNo'

Access prevents any more than 32 indexes per table so in practice that means that you can't have more than 32 relationships with referential integrity enforced on any one table. I thought that I was correct in having a separate table for each feature type and creating 1-many relationships to the central table. The fact that I can't have more than a certain number of relationships suggests that my structure is wrong.

I thought about having one large table with a 'feature type' field plus all the other fields required for each feature type but the info recorded about each feature type is different, leading to a large table with many fields, many of which would be blank. This seems to be un-normalized to me.

So, my question is twofold really.
Have I got my structure right? Is it one of those special cases (we all want to be a special case?) and if so, how do I get round it?
If not, where have I gone wrong?
I thought I was comfortable with my design but coming up against this limit has thrown me a bit and I'm not sure how best to proceed.

regards
Neil

View 2 Replies View Related

Too Many Indexes To Link

Sep 17, 2004

I am trying to link a Progress table into Access. However, the Progress database table has 41 indexes. I have even tried to import and get the same error - Too many indexes - . Is there a way to get around this? Currently I am importing to Excel then linking Excel to Access. The table is time sensitive so a live link is the best. Any ideas? Thanks

View 2 Replies View Related

Too Many Indexes Error Message

Nov 30, 2005

I am a beginner when it comes to access. The project that I am working on is tracking Travel claims. The claims are submitted ever month for 4 years so I have created 48 tables for each month and tried to link them via the SSN. The member can also submit other claims during this period, ie supplementals (for reimbursements that were missed in the monthly) and Concurrent duty claims (for the member going to additional sites then where they are assign).

I have a

MBR tbl - that holds all the MBR's info (SSN, name, unit, city, state, POC, POC email and Remarks)

(48) Partial tbl - linked by the SSN.

(5) Suppplemental tbl - Linked by the SSN ( I need more of these but I started with 5)

(5) Concurrent tbl - linked by the SSN ( I will need more of these)

These are all one-one relationship.

How can I get this to work. I can upload a blank copy of this database.

Thanks for any help
Craig

View 2 Replies View Related

How To Add Indexes For My Customer Table?

Jun 18, 2005

Hi guys. I got unbounded form that I want to add searching functionality by utilizing indexes. I would like to know how to add indexes for my customer table so that I can use it for searching records. I be happy if some one help with make indexes. Thanks


http://i5.photobucket.com/albums/y1...tabledesign.jpg


==> pic of my customer table in design view

View 1 Replies View Related

Modules & VBA :: How To Consider Numbers As Indexes

Jul 15, 2015

I have sheet names stored in a text field.

sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name)
How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.

How to consider the numbers as indexes?

View 3 Replies View Related

Access 2007 / Indexes In A Table?

Mar 18, 2014

I am trying to consolidate a few tables I made to make them less numerous and hopefully easier to handle. At one point I received an error message that said I had exceeded the maximum number of indexes in a table (32) and thus it could not be saved. When I scroll through my table in design mode every box that has "Indexed" as an option is marked at "no". Many boxes do not have that option and thus I am not sure if those are automatically indexed(?), not indexed(?) or that indexing is not relevant for those values(?). I definitely do not have any, let alone 32+ boxes, that have "indexed" checked to "yes" though. They are all "no".

How do I figure out which columns in my table are indexed otherwise so that I can reduce those numbers and get more columns on my tables (by the way, I am no where near the limit of 255 columns on any of my tables)

View 2 Replies View Related

Update Queries Vs Append Queries, Indexes?

Aug 7, 2007

This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:

View 3 Replies View Related

Compact Once A Day...

Dec 27, 2005

Hi everyone,

hopefully you'll be able to help me out with this one, been searching but can't find the exact answer.

I am running a database with multiple users, max of 10, currently the database autocompacts when it is closed, however this takes time every time anyone closes it.

what i am looking to do is have the database automatically compact but only once a day. Any ideas???

many thanks

View 1 Replies View Related

Right Time To Compact

Oct 21, 2005

Hi All,

Is there any way to determine the right time for compacting a database?

I mean say for example - is there any way (thru a property/system table) which I can refer to see how many records are marked for deletion?

This way - I can compact the database ONLY if there are large number of records marked for deletion?

I have searched all the places I could but could not get the answer I want. :confused:

Thank you,
Dev.
:)

View 5 Replies View Related

Compact And Repair

Jan 13, 2006

Goodmorining,
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

View 1 Replies View Related

Compact & Repair

Jan 17, 2006

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:

View 14 Replies View Related

Cannot Compact A Database

Mar 2, 2006

I know this has been discussed before but I have not been able to find a solution for my problem.

I have a database with linked tables from the back end database. The normal size is about 4.5 mb but after doing some work on a fairly simple form and a function with Select Case statements and simple SQL (the SQL was here before that change) the size has risen to 33Mb. This actually doubled to 66Mb earlier today after a very minor change but did compact back to 33Mb.

I have the Jet Compact Utility to no aval. I have tried creating a new database and imported all the objects from the original but again to no avail.

Help please???

Kevin

View 2 Replies View Related

Compact And Repair

May 23, 2006

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 :)

View 11 Replies View Related

Compact And Repair

Jul 25, 2006

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 :)

View 6 Replies View Related

Database Won't Compact

Aug 10, 2006

I have an access 2000 database, which we copied from it's original location to another computer. I now am trying to modify/update the file, and have discovered an unusual problem. The filesize is roughly 75 megs! It is not a huge database, the biggest table has 2200 entries x maybe 8 fields.

There apparently had been corruption in the 2200 line table, as when I first looked into it, it had something like 275,000 rows. So I deleted back to the 2200 "good" rows of data. Figured I had the filesize problem fixed. But it won't shrink. Tried compact/repair and it didnt help. Crunched data for a long time, seemed to be working, but the filesize turns out nearly the same.

I then created a new database, and imported all of the info from the "big" one. Filesize = 1.5 meg. Great! not really. I cant get any of the code to copy over and I am not a VBA guy. All of the form buttons return error messages, and when I look into the event procedures there is no code. I tried copying all that code over, and it appears to have copied, but it doesnt work.

Maybe someone out there can offer suggestions? Thanks, Harry Manvel

View 1 Replies View Related

Compact And Repair

Dec 28, 2006

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved