Tables :: Compacting Remove Indices?

May 19, 2014

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.

View Replies


ADVERTISEMENT

Remove Redundent Tables And Queries

Mar 9, 2007

i have just taken over

I have just taken over some database which have a load of tables and queries that I suspect are no longer requirer. Is there anyway to see when a table or query was last USED in a database??

Any help is appreciated

View 1 Replies View Related

Remove Navigation From Bottom Of Tables

Dec 2, 2011

I have imported a table into a form and would like to know how to remove the navigation options from the bottom of the table, I know you can do it for the form but can you do it for a table contained in a form.

View 10 Replies View Related

Tables :: Remove Duplicate Table In Relationships

Feb 10, 2014

I am using Access 2007, and I mistakenly created a duplicate table in Relationships. And, I have not been able to removed it or delete it.

View 6 Replies View Related

Combine Tables - Remove Duplicates And Add Fields

Sep 28, 2015

I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.

i.e.

I have a

Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....

-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.

Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?

View 9 Replies View Related

Queries :: Find Duplicates From 2 Tables And Remove From 1 Table

Jun 29, 2014

I have a report with 2 access tables (1 Master table and another a daily feed table)

The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)

The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)

When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.

When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?

This is my code to find duplicates:

SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));

View 9 Replies View Related

Modules & VBA :: Code To Consolidate Two Tables And Remove Duplicate Entries

Oct 3, 2013

I have two tables in my Access database. Table 1 has three columns, First Name, Last Name and Phone Number. Table 2 has just 2 columns, First Name and Last Name. What I would like to do is write a VBA macro to consolidate Table 2 into Table 1 and insure any duplicate First Name-Last Name combination is removed.

Table1

Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333

Table2

Code:
F.Name L.Name
Albert Black
Larry Miller
John Bush

Table1 Post-Macro

Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Albert Black
John Bush

View 1 Replies View Related

Compacting !!

Jun 7, 2005

Hi...
I've got a new problem

when I am exiting from my database its automatically starts compacting..
I want to stop this

any idea?

View 2 Replies View Related

Compacting

Apr 4, 2007

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?

View 3 Replies View Related

Tables :: How To Remove HTML Tags Displayed In Table When Copy / Paste Data

Jan 19, 2015

When I copy data from Outlook or MS Word that contains bullets or other HTML formatted text, into MS Access text control, the HTML tags are displayed in the tables.

The memo field in the table is set to Rich Text and so is the text control on the form.

Below is an example of the data I'm copying from Outlook email:

Fire Alarm Activation

Actual/Smoke Present
False
Planned Drill/Testing
Evacuation

2. Utilities/Power/Communication Failure

Now, below is what it looks like in my table or output in a report:

<ol>
<ol>
<li><strong>Fire Alarm Activation </strong></li>
</ol>
</ol>
<ul>

[Code] ....

How can I copy and paste and remove the html tags?

View 1 Replies View Related

Compacting Problems

Nov 8, 2005

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.

View 4 Replies View Related

Compacting Database?

Dec 6, 2006

I have a file size limit of 2meg but my database is pushing well over 4?

I have added images that were quite big but took them out when I relised how big they were. After compacting the database the size remains the same?

What can I do? Its a stupid problem, but it is however, a problem!!!

Please help!

View 1 Replies View Related

Problems Compacting

May 9, 2007

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)

Any ideas what might be happening here.

View 6 Replies View Related

Compacting Problem

Oct 1, 2007

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.

Any idea on what is causing the rename to fail?

Thanks

View 11 Replies View Related

Question About Compacting

Mar 18, 2008

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?

Thanks

View 2 Replies View Related

Access--Compacting Errors

Aug 11, 2005

Since we have installed the latest Windows critical security updates, upon closing an Access database, the database gets corrupted. Error code is c000020c.

View 4 Replies View Related

Problem While Compacting Database

Feb 27, 2006

Dear Friends

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.

Please advice - thanks

View 6 Replies View Related

Close Button Without Compacting

Nov 10, 2006

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,

View 3 Replies View Related

Database Crashes When Compacting

Aug 29, 2007

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

View 1 Replies View Related

Compacting Problems: TempMSysAccessObjects

Feb 11, 2005

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!!!

Help!
:)
Barb :confused:

View 1 Replies View Related

Error In Automatic Counter After Compacting

Jan 9, 2006

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?

View 2 Replies View Related

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??

Thanks people. :-)

View 1 Replies View Related

Compacting Database 'breaks' SQL Code

Sep 22, 2005

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?

View 1 Replies View Related

Compacting And File System Security Problems

Jan 8, 2006

Hi

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.

Is there a solution??

thanks
Jesse

View 1 Replies View Related

Help! Database Doesnt Open And Comes Up With Error Message About Compacting And DAO!

Jan 23, 2008

hi,

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.

Please help!

Thanks in advance

View 2 Replies View Related

Modules & VBA :: Auto-Number Sequencing By Compacting The Database

May 30, 2014

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?

View 3 Replies View Related







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