Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Records unexpected deleted from table (Office 2007 pro)

    I have a strange problem in one of may access applications.
    The applications has about 20 tables. The tables do have relations to insure the data integrity between the tables.
    The biggest tables have a maximum of about 10000 records (average 4000 to 5000). Should be no problem.
    Forms, reports, queries and modules are in the same file as the tables (I know, separating data and logic is the best, but in this case I have valid reasons not to do this).
    There are eight users using this application. Auto compress at closing the application is switched on.

    Last couple of weeks it twice occurred that about half the records of a table were deleted (the bottom half). Fortunately I have a backup!
    It occurred to one table a time, it was not the same table!
    Does anyone have an idea how this can happen?

    Thanks for any help

    Bart

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    I haven't heard of this problem, and I have no idea what causes it, but I'd turn off Compact on Close for the moment.

    And you should still split the database into a frontend and backend!

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    - I haven't heard of this problem, and I have no idea what causes it, but I'd turn off Compact on Close for the moment.
    I already did turn of Compact on Close last friday (was already one of mine suspects too). Last time there was one and half week between loosing the data in the tables (one table the first time, a different table the second time).

    - And you should still split the database into a frontend and backend!
    I know! I had seperated those (I normally do that) but the application performance became too slow. I am still trying to find the cause of that. Putting the data and the logic together solved the performance problem for now.
    Application is located at a file server from where it is started by the users.
    At least twice a day a backup is taken (for the moment).

    Thanks for the quick reply
    Bart

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    I have no answer to your problem, but maybe my new Backup tool is useful in this situation?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    It's very odd that splitting a database would cause performance problems. Usually it's the other way around - and the reasons for splitting the database are pretty compelling. With eight users, you might seriously consider using SQL Server Express - a free download - with ODBC linked tables. Also, what format is the database - is it the .mdb or the .accdb?
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Splitting the database is not the problem here. I am working on that!

    The problem is the loss of data in the tables!
    The format of the database is accdb.
    It used to be mdb, I converted it to accdb and added more functionality.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    I don't know of any reports that the accdb format occasionally looses records, but it is a new format and the database engine has changed substantially to support it. Are you using some of the new field types that it introduces? Also are you doing anything with cascading deletes in your referential integrity settings? Either could be a source of trouble if you are. Also have you seen it again in the last few days? (My apologies for not responding sooner - I spent 3 days driving across part of the US and back... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>.)
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Are you using some of the new field types that it introduces?
    No, I only use the good old well known field types.

    Also are you doing anything with cascading deletes in your referential integrity settings?
    No, all deletes are done by code. In the code the correct referential order is ensured. Referential integrity is implemented in the database in case someone is trying to update data directly. Looking at the type of users for this application that is not likely to happen.

    Also have you seen it again in the last few days?
    No, but last time there was a period of two weeks between loosing the data. Both times data was deleted in a different table. About half the records of the tables affected were gone although referential integrity applied to the tables should have prevented that ......

    (My apologies for not responding sooner - I spent 3 days driving across part of the US and back... .)
    Hey, no problem, it is a forum not a telephone call.... I am glad you are thinking about this problem.

    Bart

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Were the records in any other tables deleted? That is, if this was a transaction table, were any records from the Parent table also deleted? Is there any commonality from instance to instance? That is, always on/after the same date or same autonumber as the last time?

    And I'm sorry, but putting the frontend and backend together to solve a performance issue is never a good solution! You generally end up creating more problems than you solve. There are ways to solve all performance issues.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Were the records in any other tables deleted?
    No, it occurred twice, each time only one table and about the bottom half of the records.

    Is there any commonality from instance to instance?
    No, first time in the afternoon, second time in the morning. Several persons were connected to the db each time. No specific actions before the loss of data.

    And I'm sorry, but putting the frontend and backend together to solve a performance issue is never a good solution! You generally end up creating more problems than you solve. There are ways to solve all performance issues.
    I know! I am working on that and I know a lot if solutions to that. They only take some time to implement. Frontend and backand will be separated again! But that is not the issue here!

    The loss of data is the issue here and a bigger problem than the separation of frontend and backend or the performance!

    Thanks for trying to help!

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    When I asked if there was any commonality, I meant was there any commonality in the records deleted. That is, in both times were records entered on/after the same date deleted? Or something like that. The idea being that knowing what records were deleted might give you a clue as to what process might have been involved in deleting them. For example, if all Orders were deleted starting with #1000, then you want to know if anyone was doing anything with Order #1000 at the time. It is just a place to start looking for a flaw.

    And as for not considering splitting the database into frontend/backend because you have other problems; well, I would not dismiss it as the cause of any database problems I was having.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Naturally I was looking for a commonality in the deletions. The only commonality I could detect was that about half the amount of records in the tables were deleted: the bottom half.
    No commanality in dates. It affected also two totally different tables at different times.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Records unexpected deleted from table (Office 2007 pro)

    Mark made an excellent point. If you are continuing to do form and report development in a single database, there is always a higher probability of corruption of the database in that case. That is one of the many reasons we always, even during development and testing, use a split database. Also, I still don't entirely trust the .accdb format - the .mdb format has been around for 15 years and has been pretty completely wrung out, while the .accdb format is only 2 years old, and there is basically a new dbengine used for it. That said, if I am concerned about the loss of data, I nearly always use SQL Server as the backend, and Access as the front-end. Please let us know if you encounter further loss of data situations.
    Wendell

Posting Permissions

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