Cannot Save Table, Duplicate Field Name (corruption?)
Apr 20, 2006
Does this sound like a corruption problem?
I added a new field to a table, and saved the table. When the table is reopened, the new field is not there! I thought perhaps I forgot to save the field, so I tried adding the field again. When saving, the error said, "cannot save; duplicate field name".
If I go to toolsoptions and click the "show system objects" the field will show up??
Do you think the best course of action is to import all the objects into a new db and try again?
My query lists duplicate records which I append to a new table. from that table I would like to delete the duplicates but save the first record of each duplicate. example the fields are employee and date worked. I would like to keep joe smith 1/1/14 but delete the other joe smith 1/2/14 and joe smith 1/3/14 ...
I have a form which calculates alot of numbers. Im trying to figure out how to save the calculation to a table field. Is this possible? Can someone help me with a solution please
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.
:eek: I have a large table with 1 damn corrupted record, the database can't be repaird, compact, I can't use the copy ,method since the table is over 9000 record sets.
I tried creating a new table and appending the non-corrupted record into it with no success, it is keep locking on me.
Have you guys tried anything else that works for this situation???
I got this "AOIndex is not an index in this table" error with Access 2000 after replicating a database and storing it in the same network folder. I am not able to open my database now, i am afraid 2days work on my forms is lost. I usually have backup, but the last backup i have is 2 days ago i have made alot of changes to 2 FORMS after that :(
Please anybody have suggestions on how to retrive my forms??? I am really concerned about the forms. I have backup for data and for the reports. Please help me?
I put a split database (created in 2000 format using Access 2003 SP2) on the network three weeks ago. Three times the entries in two of the columns have been changed for every record, essentially making the data useless. I have not been able to narrow it down to a specific event that is causing it in order to program around it. Any thoughts would be greatly appreciated. Here are the details:
The table involved is called tblTitleLog. It contains 13 columns, the first of which is an autonumbered ID field. Column 7 is called CompletedCSA. Column 9 is called CompletedDate. The data in those cells could be different, from record to record, or could be the same as several records on either side of it. The data in those two cells can be updated either of two ways:
On a per-record basis using a form that contains controls tied directly to the cells for a given record, or On a form that incorporates a multiselect listbox and an Update button. This form is specifically designed to allow batch updating of those two columns.
On three separate occasions, the data in those two cells suddenly changed for all 6940+ records. For example:
This: CompletedCSA CompletedDate Timmy 2/2/2008 Bobby 2/5/2008 Joey 2/10/2008
Would suddenly change to: CompletedCSA CompletedDate Bobby 2/5/2008 Bobby 2/5/2008 Bobby 2/5/2008
No warning messages about multiple people trying to change the same record(s) is displayed. None of the users have been able to spot a trend in something they were doing when it occurred. The code for the Update button on the batch update form only changes the records with the ID of the SelectedItems.
This is the code from the Update click event on the BatchUpdate form:
For Each i In Me.listboxTitles.ItemsSelected strWhere = strWhere & Me.listboxTitles.ItemData(i) & ", " Next
If Len(strWhere) > 0 Then strWhere = "WHERE ID IN (" & Left$(strWhere, Len(strWhere) - 2) & ")" End If
Duplicating the same actions the users reported just before it happened didn't seem to make a difference. So far, I haven't been able to duplicate the conditions. Management is not happy and I'm frustrated. Any thoughts or suggestions on how to prevent this from happening again?
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
Hello everyone, I have been trying to calculate the number of working days a person has off sick between two dates.I was aware of how to calculate the difference between two dates using datediff, this was not quite hat i needed. after much experimentation and a little searching I came across this site that has I would recommend to other beginners to use as a query and a module.
http://www.mvps.org/access/datetime/date0006.htm
I have a calculated field that uses this function to work out the number of days, My question is, Is it worth me saving this calculated field physically in my table instead of in the query?
The reason for this is that im only with company for limited time and others will be building reports in the future and would prob prefer to build reports on tables rather than queries. If so, how can I go about setting the field value to that of the query result?
i have probleb with the comboboxes if i change cobobox value, then comes a error :to make changes to this field, first save the record the combobox code is
Private Sub CatecoryID_AfterUpdate() On Error GoTo Err_CatecoryID_AfterUpdate Dim strFilter As String strFilter = "Products.CatecoryID = " & Me!CatecoryID Me!ProductID = DLookup("ProductID", "Products", strFilter) Exit_CatecoryID_AfterUpdate: Exit Sub Err_CatecoryID_AfterUpdate: MsgBox Err.Description Resume Exit_CatecoryID_AfterUpdate
So I have this relatively simple problem: I need to create a button that once clicked will open the Save As dialog box and allow the user to save a copy of the current database where he wishes. The filename should contain todays date in DDMM format along with some pre-set text e.g. DDMM PresetText.
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
i have a table with a date field with default value for new records date() when i change an other field and im going to save the changes access says that canntot save the changes because of unknown type date()....
I inherited this user written multi user (<=10 simultaneous users) Access 2000 database. The front end in Access 2000, the back end is SQL 2000. All the tables are linked to SQL. When the users were running the Access front end on a Novell server there were no problems. We have sinced moved it to a Windows 2000 server and are now getting a Database needs to be repaired message 2 or 3 times a day. I searched the web and the only thing I came up with is that the Opportunistic Locks Setting on the Windows 2000 server needs to be disabled. Naturally our server team is reluctant to change the settings. Has anyone else experienced this problem or have any suggestions? Right now our solution ws to put a copy og the Access frontend on each user's C:, very ugly and difficult to maintain! Thanks so much for your help!
Hi, With my database I have actually distributed the mdb file to 2 of my users so that they can make their own reports. I have secured it so that these users cannot delete/modify anything they shouldn't.
However every so often it seems to get corrupt. I did decompile it and I tried importing everything into a new DB and secured it again. It seems to be working again but I wasn't able to find what was corrupt so I'm not completely confident that its fixed.
Would it be better if I gave them the MDE file - would it lessen the chances of it becoming corrupt??
When I leave the job I guess I will need to show someone how to decompile it in case there is a problem?
Viewing an email from Code Project, I ran across this article about database corruption and thought it was worth posting here... Be sure and click on the immediate link below to view the latest updates in the discussion area at the bottom of the web page. ---------------------- http://www.codeproject.com/useritems/rdb.asp
Strange behaviors of an access database By Hamed Mosavi.
Introduction
A while ago I encountered a strange behavior in my application which was using an access database (.mdb file). I placed a question in the CodeProject C++ forum, and I did not get any answer. I started searching the web, MSDN, etc... and I found out that it's not all my fault. I found the reason in a knowledge base article. Look at it's description about the possibility of corruption of an access database file:
"There are several things that can happen, both inside and outside of Access, that may leave your database file damaged (corrupted). The symptoms of a corrupted database can range from #Deleted appearing in certain records, to you being unable to open one of the objects in the database, to you being unable to open the database file in Access altogether." (MSDN-Knowledgebase:283849)
Since I got no answer for my question in the forum, I thought there are at least some guys out there who might don't know this , and also since it's easy to corrupt a mdb file, while it's difficult to understand the reason why the application is not Functioning properly, I started writing this article.
The question is what might cause a damaged access mdb file and also how to repair a damaged access file programmatically.
Background
What can cause the mdb file to corrupt The knowledge base says:
"There are three main reasons why an .mdb file may become corrupted, as follows:
* Interrupted write operation * Faulty networking hardware * Opening and saving the .mdb file in another program"(MSDN-Knowledgebase:283849)
There's a detailed description for each one of these in the knowledge base, but some of important reasons are as follows:
* Losing power during database write * Dropping network connection * Abnormal termination of microsoft Jet connection, such as having task manager to shutdown application, power loss, manual shutdown. (notice that:Fatal system errors almost always cause abnormal termination, refer to kb to find out more information) * Forgetting to close ADO or DAO objects that opened. (Objects from classes like:Recordset, QueryDef, TableDef, and Database) * A large number of open and close operations in a loop (more than 40,000 successive open and close operations could cause corruption) * And worst of all, Opening and saving the .mdb file in another program like MS word. It is not recoverable. all your data will be lost.
"There is no way to recover an .mdb file that was opened and then saved in a different program" (MSDN-Knowledgebase:283849)
How to reduce corruption possibility
* Avoid all above * Compact the database often (The class provided with this article)
Things can get worst Q291162: AutoNumber field duplicates previous values after you compact and repair a database: Microsoft also announces that after we compact and repair our database, it is possible to encounter Duplicate Autonumber field, and if it's your database key, then... fortunately this applies just to those who are using Microsoft Jet version 4.0.2927.4 and earlier. "Microsoft has confirmed this to be a problem in Microsoft Jet version 4.0.2927.4 and earlier." (MS KB Q291162). to find out your Jet engine version try to search your system/32 directory for Msjet40.dll (if you are using v.4 and later) then get the properties of the file. This KB can help you find your version and download latest version:Q239114
Another bug exists there and it's "AutoNumber field is not reset after you compact an Access database" I don't describe a solution for this, since there is one already, and it is not really critical, for more information refer to KnowledgeBase Q287756.
if you lost data and have problem recovering data(yet), this can help you a lot: Q247771 and Q306204.
Using the code
In order to use the code, follow the following steps:
1- Copy DBFitter.cpp and DBFitter.h to your project. 2- Check and possibly change first two #import directives in DBFitter.cpp.
3- include DBFitter.h, Create an object from type DBFitter and use it as follows:
CDBFitter fitter; if ( !fitter.CompactAndRepair(szDbPath,m_szDBPass) ) { AfxMessageBox(fitter.GetLastErrString()); }
CompactAndRepair has 3 forms that can be used alternatively. The first one you see above , The other form just gets database file path as input and does not use password
CompactAndRepair(CString szDbPath)
The last one asks for a source, a destination and password, which can be given "" as password to say there is not a password
4- Don't forget to call AfxOleInit(); in the initialization of your application.
"What to Make Sure of Before You Run the Compact and Repair Utility Before you run the Compact and Repair utility on a database, make sure of the following:
* Make sure that you have enough free storage space on your hard disk for both the original and the compacted versions of the Access database. This means that you must have enough free storage space for at least twice the size of your Access database on that drive. If you need to free some space, delete any unneeded files from that drive, or, if possible, move the Access database to a drive that has more free space.
* Make sure that you have both Open/Run and Open Exclusive permissions for the Access database. If you own the database, make sure to set these permissions. If you do not own the database, contact its owner to find out if you can get these permissions.
* Make sure that no user has the Access database open.
* Make sure that the Access database is not located on a read-only network share or has its file attribute set to Read-only. " (Q283849)
Compact and repair in C# Thanks god, there is already an article: http://www.codeproject.com/cs/database/mdbcompact_latebind.asp
--------------------------
If there are any questions or feedback; please direct them to the original authors link at the top of the page so everyone can benefit.
Hi guys i will appretiate it any hlp you can give.
A few weeks ago i started to experiencing problems with my access2k db. Some times i could open it and sometimes an error like this one.
"Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience. It asks to send a report and to repair the db
more data: 1- my jet engine version is 4.0.9025.0 2- because i eliminated the cross from everyform in the db sometimes i had to close access from the task manager. 3- i have never compacted the database. 4- it is still in development phase, so there are only a few records. 5- In order to use the db i have to use a citrix session in the usa (i am in australia), and from the citrix server connect to another drive in the usa. I also moved the db in the citrix server but i had the same problem.
Question: if i copy the forms into a new db, will i have the same problem?
I have a small database being used concurrently by 5-6 users, in the last few weeks we have had some corruption issues, the users are running a mix of access 2000 & 2003 – would unifying versions help these corruption issues?
Also what is the recommended maximum number of simultaneous users accessing one a DB in a LAN/WAN environment?
I believe I have discovered a new way of fixing corrupted forms in Access.
After a crash, one of my forms refused to open in either design or form view saying: Form name | is misspelled or refers to a form that doesn't exist
Having searched the internet for this error I found lots of people recommending Repair/Compact or copying and pasting the currupt object into a new database.
Repair and Compact (Access 97) both ran successfully but failed to fix the problem.
When I attempted to copy and paste the broken form either within the database or externally I got
Error 13: Out of Memory
When I attempted to import the form into a new database I got
Automation error
If I attempted to rename the form, I got:
The save operation failed
Resolution: (I don't know which step here was the key to my success) 1) Delete all other database objects (all tables, queries, forms, macros and modules) 2) Create a new form and put any control on it. 3) Attempt to save the form using the same name as the corrupt form 4) Access asks "The name you entered already exists..." (Aha! The first time Access is admitting that the corrupt form exists.) Say no to this prompt. 5) I was now able to rename and open my corrupt form. Everything was fine.
If this post stops just one other DBA having to go through the morning from hell I just went through then it'll all have been worthwhile!
I have been searching for an answer to this, but no luck. Has anyone ever seen the below error when loading an Access 2003 database?
"[database name] has detected corruption in this file. To try to repair the corruption, first make a backup copy of the file. Then, on the Tools menu, point to Database Utilities, and click Compact and Repair Database. If you are currently trying to repair the corruption then you will need to recreate this file or restore it fom a previous back up."
The database is already compiled and compacted. It's a new Access 2003 program. Only one user is experiencing this problem. She is on XP while the others are on Windows 2000. Everyone is using 2003. I saw other odd error messages on this machine with other databases, but I was able to avoid them by setting the Macro settings to Low. This is a Network environment. Any thoughts?
i'm getting annoyed by Access 2003 (and 97 for that matter!) just corrupting records in a table and then i have to scrabble around, kicking people out the database, compact & repairing, maybe installing the back up copy and in one case, doing a make table query from the data table that excluded the corrupted record
:(
are there any general things i can do to avoid these things, or am i just going to have to roll with them?
