Upgrade to Access 2007?

[Steve H]

I have an access 2000 database which is getting corrupted more often than I
would expect, and only on one table, all other tables never get corrupted.
The table in question is edited using a custom "split view" form with a
combined datasheet/form, as in the new version of access 2007. I am not sure
that the corruption is being caused by this customized split view, which
contains a datasheet and form linked as subforms on a main form. I am
presuming that upgrading to the access 2007 and using the new split view form
type would fix the problem. Also, would access 2007 be any faster than access
2000?

Thanks for any feedback

 

[Jerry Whittle]

You assumption would most likely be wrong. Whatever is corrupting your
database now will probably corrupt it in 2007 unless you fix the root cause.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Steve H]

Thanks for reply. I have spent a long time going over all possible causes,
the thing that makes me think it's the split view is that if both forms are
dirty at the same time, then it almost always ends up corrupting the record.
I set it up so that this couldn't occur, and now corruption is much less. The
database is compacted and repaired every day. Sometimes it can go for weeks
without corrupting, then it starts corrupting a few times a day. Perhaps one
of the split view forms is corrupted?

 

[Allen Browne]

Are you actually using Access 2007 to edit this mdb on some workstations?

If so, you must be aware that any beta is not fully debugged yet, and that
is certainly true of Access 2007. Especially the new stuff (like the split
view) is still being tested, and you are a brave soul if you are using that
in a production environment.

If you are actually editing with a normal form and subform using Access 2000
(just comparing that process to the new split layout in A2007), there are
some pitfalls here. Particularly:

a) Any memo fields in these forms? That has the potential for concurrency
issues.

b) What do you have in the LinkMasterFields/LinkChildFields of the subform
control? If the primary key is an AutoNumber field, and that field is
nominated in these properties, you can corrupt the Seed of the AutoNumber.
See the Other Causes section of this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[david epsom dot com dot au]

How are the two forms synchronised? Are you saying that you
can edit in both views at the same time? That is pretty
unusual, you may want to consider disabling edits in one
of the views.

(david)

 

[Steve H]

Thanks for replying.

Yes, I've set it up sot that you can edit in both forms at the same time, I
find it really useful, it saves a lot of time.(There are notes which are not
easy to see in the datasheet view) The form's record source is reset using
the ID in the oncurrent event of the datasheet. The ID of the contacts table
is set to autonumber random.
The datasheet and form are both subforms of another form, which I use so i
can have a splitter bar to resize them both. Concurrency is solved by setting
both forms to edit record locks.

 

[Steve H]

Also, there is no linkchild or linkmaster set up, and there are memo fields

 

[david epsom dot com dot au]

Can you leave the memo field out of the query on one side?

If not, it looks like you're going to have to unbind the
memo, and use separate code to copy the memo to and from
the forms.

(david)

 

[Steve H]

I might be able to leave the memo out of the datasheet view, but if not will
try copy/paste code to the form.

Thanks