Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012

    Unanswered: what is this error?

    i made a database that has been working fine so far.
    suddenly, when i open the access file it is saying: " "ID" is not an index in this table".
    do you know how this has happened and what shall i do?
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    well either you have changed something or you are experiencing symptoms of corruption

    either go back to the last 'good' backup and roll forward
    leaving the original file(s) alone, ie work on copies NOT the original
    try a "compact and repair" that sometimes can fix faults

    you can also try the jet utility jetcomp which may well be the same as compact and repair WITHIN access but soemtimes its cleared faults C&R failed to do

    in either case look at the system table MSysCompactErrors to check for faults the above cannot fix

    if that fails then create a new db or db's
    then import all tables, queries, forms, reports and code modules, compact & repair the new blank db(s) to ensure the baseline is 'clean'

    reasons for corruption in the first place.

    the most common problem is multiple users using a monolithic access appliciation file. In a multi user environment at the very least the the applcition MUST be split into 2 parts, one part (the back end) contains all common tables and queries, the other (the front end) contains all forms, reports and code modules and any user specific queries. Ideally each user should have their own copy of the front end and its recommended that the front end is located on the users local machine. there are tools in the code bank in this forum that can help you manage deploying a new front end

    after that the most common causes for corruption are system crashes, power outages/brownouts and so on. power outages can be protected against by using a UPS, if thats too expensive an option for a file server and EACH client PC then put a UPS on the file server. there is a rerason why data centres use UPS's as they dfont' like having to rebuild stuff because of power issues

    user intervention (user did a 3 finger salute as the system seemed locked out). usually thats down to you as a developer to make certain that the user always get feed back on long running batch or semi batch process so the user is aware that the process is running

    The excellent Allan Browne, as you would expect has some good tips
    Microsoft Access tips: Preventing corruption

    As you go forward
    learn to love backups.
    consider using an audit log identifying what was changed then restore to a backup, run your audit log and get the data to the current good state. historically I've created an audit log as a series of individual SQL statements. so when it comes to restoring I just runs each statement going forward. my audit log looks like
    EventTime        userid    computer SQL
    2014/08/24 08:16 ariansman DEVTPAM INSERT INTO mytable........
    2014/08/24 08:17 ariansman DEVTPAM UPDATE mytable set......
    another advantage of this approach is that if the data gets corrupted or mysteriously disappears then instead of the finger pointing at your the developer you can have a necklace party of the specified user. if your audit log is write only and you have used the network API calls to identify the user and computer AND you have system and corporate policies that can only allow one network logon by a user and one user account per person then you have enough legal grounds at the very least for a verbal warning and possibly a written warning / dismissal if the damage is severe enough. NOTE you also need to implement a company policy that a user must not leave their computer unattendded by them when not locked. An added advantage of the API approach is that you do not have to implement your own logon procedures (its authenticated by network logon).
    Last edited by healdem; 08-24-14 at 03:26.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts