By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Filter by Form causing corruption ???

P: n/a
Since upgrading one of my clients from A97/W2000 to A2003/XP they have
suffered no end of data corruption problems, mainly involving one of
the main tables. The corruption can result in one record's fields
turning into chinese characters. On running the compact/repair I can
see that the corruption may be deeper than I expected because sometimes
indexes are missing and need to be replaced.

I used to think that overuse of 'ctrl-alt-delete' was the problem but
it's still happening. So now I'm wondering if the use of filter-byform
might cause it. There is a large form bound to the main table mentioned
above and they use filter-by- form a lot. Changing it to an unbound
form would be a nightmare.

Anyway - had anybody any views/experience of any problems cause by the
use of filter-by-form under A2003/XP, especially if they were'nt there
before ?

Thanks
Dave

Apr 24 '06 #1
Share this Question
Share on Google+
23 Replies


P: n/a
Hi,
*** I not sure but maybe you shall install Service Pack 2 or the
latter, If u already installed so ignore this remark.

Apr 24 '06 #2

P: n/a
Continue due to my last remark,
I was encountered in that problem when upgrading my office too, but I
saw the table's data was like question mark, like I dont have the right
font, so check for SP2 or latter.

Apr 24 '06 #3

P: n/a
Baz
"Dave G @ K2" <Da*************@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
Since upgrading one of my clients from A97/W2000 to A2003/XP they have
suffered no end of data corruption problems, mainly involving one of
the main tables. The corruption can result in one record's fields
turning into chinese characters. On running the compact/repair I can
see that the corruption may be deeper than I expected because sometimes
indexes are missing and need to be replaced.

I used to think that overuse of 'ctrl-alt-delete' was the problem but
it's still happening. So now I'm wondering if the use of filter-byform
might cause it. There is a large form bound to the main table mentioned
above and they use filter-by- form a lot. Changing it to an unbound
form would be a nightmare.

Anyway - had anybody any views/experience of any problems cause by the
use of filter-by-form under A2003/XP, especially if they were'nt there
before ?

Thanks
Dave


You do have a split front-end/back-end, right? If not, then split that
database NOW!

Having said that, it doesn't seem likely that filter by form would be
causing this. Your first instinct - that something is interrupting database
updates - is more likely to be correct. If you are sure that users aren't
doing daft things like, as you mention, using the task manager to crash
Access, or turning the computer off at the power switch, are they perhaps
reporting that Access is crashing? Or are they having network glitches?
Has someone started using a wireless connection?
Apr 24 '06 #4

P: n/a
"Dave G @ K2" <Da*************@gmail.com> wrote
Since upgrading one of my clients from A97/W2000 to A2003/XP they have
suffered no end of data corruption problems, mainly involving one of
the main tables. The corruption can result in one record's fields
turning into chinese characters. On running the compact/repair I can
see that the corruption may be deeper than I expected because sometimes
indexes are missing and need to be replaced.
I used to think that overuse of 'ctrl-alt-delete' was the problem but
it's still happening.
Any use of Ctrl-Alt-Delete to end Access database execution is "overuse,"
and shutting down by turning off the power and dropped network connections
are also long-known causes of corruption. That is, "ungraceful termination"
is to be avoided.
So now I'm wondering if the use of filter-byform
might cause it. There is a large form bound to the main table mentioned
above and they use filter-by- form a lot. Changing it to an unbound
form would be a nightmare.
Yes, it would be a continuing maintenance nightmare, not to mention that
converting would mean that you are reimplementing functionality that is
built in to Access, and would have to test all that functionality (which, in
Access, has been tested for years and years). And, to top that off, it would
be unlikely to have a beneficial effect on your "frequent corruption
problem".
Anyway - had anybody any views/experience of any problems cause by the
use of filter-by-form under A2003/XP, especially if they were'nt there
before ?


The best source of information and links on avoiding corruption in
multi-user environments that I know about is MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. Another great Access information site
is MVP Jeff Conrad's
http://home.bendbroadband.com/conrad...essjunkie.html.

Many, many Access applications involve Filters, even when the user /
developer doesn't realize it because that is what happens when the
DoCmd.OpenForm is used with either a Filter or WhereCondtion argument. It
seems unlikely to me that filtering itself is going to cause the problem you
are experiencing, but there are a number of conditions known to increase the
probability of corruption.

And, as you were already told, having multiple users logged in to the same
monolithic database or to the same front end significantly increases the
probability of corruption -- and that can be sensitive to many factors,
including versions of Access and the operating system. Many go for years
without experiencing it, and then make some apparently-minor,
apparently-innocuous change and suddenly are in Corruption City.

Of course, you should assure that you are up-to-date with Service Packs and
updates for Access and the operating system (if you have done both, then you
should also be up-to-date on the Jet database engine).

Larry Linson
Microsoft Access MVP
Apr 24 '06 #5

P: n/a
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
You do have a split front-end/back-end, right? If not, then split
that database NOW!

Having said that, it doesn't seem likely that filter by form would
be causing this. Your first instinct - that something is
interrupting database updates - is more likely to be correct. If
you are sure that users aren't doing daft things like, as you
mention, using the task manager to crash Access, or turning the
computer off at the power switch, are they perhaps reporting that
Access is crashing? Or are they having network glitches? Has
someone started using a wireless connection?


Let me also point out that adding a new computer to a network, even
one that is not using the Access application, can lead to
corruption, because it could have a bad NIC that is passing bad
packets into the network.

Look for what has changed in the operating environment between the
time the app was running smoothly and when it first corrupted in
this fashion. Check the server's event logs. Perhaps an automatic
Windows Update patch caused the problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 24 '06 #6

P: n/a
Thanks everyone for your input so far. Just to clarify - it is a
classic FE/BE arrangement. The corruption started as soon as I upgraded
all machines to XP/Office2003. The server at the time was NT4. It has
also been upgraded recently to Server 2003, but of course there was no
difference either way.

I'm using automatic updates for everything.

At the time of the workstation upgrade no new hardware was added. The
company has since moved offices and so there is a completely new
cabling infrastructure - again no change to the corruption frequency -
about twice a week.

Today I started working on an unbound version and made pretty good
progress. I think my view at the moment is that the less time that the
workstations are "talking directly" to the tables, the better off they
will be. The users will lose some functionality which I will need to
replace in other ways, but when all else fails, I don't know what else
to do.

Thanks again for your input
Dave

Apr 24 '06 #7

P: n/a
Just in case it's any help, Dave, you might check the list in:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave G @ K2" <Da*************@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
Thanks everyone for your input so far. Just to clarify - it is a
classic FE/BE arrangement. The corruption started as soon as I upgraded
all machines to XP/Office2003. The server at the time was NT4. It has
also been upgraded recently to Server 2003, but of course there was no
difference either way.

I'm using automatic updates for everything.

At the time of the workstation upgrade no new hardware was added. The
company has since moved offices and so there is a completely new
cabling infrastructure - again no change to the corruption frequency -
about twice a week.

Today I started working on an unbound version and made pretty good
progress. I think my view at the moment is that the less time that the
workstations are "talking directly" to the tables, the better off they
will be. The users will lose some functionality which I will need to
replace in other ways, but when all else fails, I don't know what else
to do.

Thanks again for your input
Dave

Apr 24 '06 #8

P: n/a
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:
Today I started working on an unbound version and made pretty good
progress. I think my view at the moment is that the less time that
the workstations are "talking directly" to the tables, the better
off they will be. The users will lose some functionality which I
will need to replace in other ways, but when all else fails, I
don't know what else to do.


I would consider that all wasted work if you don't hunt down what
went wrong in the first place. You have to get rid of the real
problem, rather than spending hours implementing changes that will
just reduce your chance of encountering the same problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 24 '06 #9

P: n/a
Baz
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:
Today I started working on an unbound version and made pretty good
progress. I think my view at the moment is that the less time that
the workstations are "talking directly" to the tables, the better
off they will be. The users will lose some functionality which I
will need to replace in other ways, but when all else fails, I
don't know what else to do.


I would consider that all wasted work if you don't hunt down what
went wrong in the first place. You have to get rid of the real
problem, rather than spending hours implementing changes that will
just reduce your chance of encountering the same problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


I couldn't agree more. In any case, I don't suppose that the unbound form
will in reality "talk" to the tables any less than the bound one does.
Apr 25 '06 #10

P: n/a
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:
> Today I started working on an unbound version and made pretty
> good progress. I think my view at the moment is that the less
> time that the workstations are "talking directly" to the
> tables, the better off they will be. The users will lose some
> functionality which I will need to replace in other ways, but
> when all else fails, I don't know what else to do.


I would consider that all wasted work if you don't hunt down what
went wrong in the first place. You have to get rid of the real
problem, rather than spending hours implementing changes that
will just reduce your chance of encountering the same problem.


I couldn't agree more. In any case, I don't suppose that the
unbound form will in reality "talk" to the tables any less than
the bound one does.


Well, it depends on how efficiently you program it.

If you do it right, the read and write locks will be shorter in
duration, and for the write locks, that will reduce the
suscepitibility to corruption.

But it won't eliminate it at all if you don't address the underlying
cause of the corruption.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 25 '06 #11

P: n/a
Idle drivelly thoughts while I am waiting not so patiently for a phojne
call:
What is the corruption?
Is it only that some fields do not display correctly? Are they alll one
data type, or similar data types?
Does the corruption ever result in the backend not being able to be
opened?
Is the corruption corrected after a compact-repair?
Are records lost?
What indexes are disappearing? Are they indexes on the fields in which
the data does not display correctly?
Have you looked at the backend with a hex-editor? Can you confirm that
the data are "corrupted" in the file?
Have you gone through Allen's list of checks carefully. Are you up to
date with Service Packs? Were you up-to-date when you made the
conversions? Did you convert the
backend?
Is it possible that your strange characters are simply that unicode is
being interpreted as ansi, or vice versa.
Access wizards are not foolproof. Sometimes they work well and
sometimes they don't. Have you considered doing the conversion again,
starting with an empty 2003 DB and importing not the tables but the
data in the tables with SQL? And then duplicating the indexes?
Have you condsidered deleting the problem form (if indeed it is a
problem form) and saving it as text from the old application and then
loading it form text in the new?
As an aside, I never use an Access wizard for anything if I can avoid
it. This doesn't mean others shouldn't, of course, but it's unlikely,
IMO, that one will get the same degree of satisfaction using a wizard
as with patiently updating each object (and, perhaps, doing a compile
after each one).
Of course, using unbound forms might circumvent this. But going around
a problem does not solve it; it simply avoids it.

Apr 25 '06 #12

P: n/a
Baz
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:

> Today I started working on an unbound version and made pretty
> good progress. I think my view at the moment is that the less
> time that the workstations are "talking directly" to the
> tables, the better off they will be. The users will lose some
> functionality which I will need to replace in other ways, but
> when all else fails, I don't know what else to do.

I would consider that all wasted work if you don't hunt down what
went wrong in the first place. You have to get rid of the real
problem, rather than spending hours implementing changes that
will just reduce your chance of encountering the same problem.


I couldn't agree more. In any case, I don't suppose that the
unbound form will in reality "talk" to the tables any less than
the bound one does.


Well, it depends on how efficiently you program it.

If you do it right, the read and write locks will be shorter in
duration, and for the write locks, that will reduce the
suscepitibility to corruption.

But it won't eliminate it at all if you don't address the underlying
cause of the corruption.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Re the locks, how do you figure that out?
Apr 25 '06 #13

P: n/a
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
> "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
> message news:Xn**********************************@127.0.0. 1...
>> "Dave G @ K2" <Da*************@gmail.com> wrote in
>> news:11*********************@v46g2000cwv.googlegro ups.com:
>>
>> > Today I started working on an unbound version and made
>> > pretty good progress. I think my view at the moment is that
>> > the less time that the workstations are "talking directly"
>> > to the tables, the better off they will be. The users will
>> > lose some functionality which I will need to replace in
>> > other ways, but when all else fails, I don't know what else
>> > to do.
>>
>> I would consider that all wasted work if you don't hunt down
>> what went wrong in the first place. You have to get rid of the
>> real problem, rather than spending hours implementing changes
>> that will just reduce your chance of encountering the same
>> problem.
>
> I couldn't agree more. In any case, I don't suppose that the
> unbound form will in reality "talk" to the tables any less than
> the bound one does.


Well, it depends on how efficiently you program it.

If you do it right, the read and write locks will be shorter in
duration, and for the write locks, that will reduce the
suscepitibility to corruption.

But it won't eliminate it at all if you don't address the
underlying cause of the corruption.


Re the locks, how do you figure that out?


Eh? Sorry, but I don't get the question.

While a bound form is dirty, it holds a write lock on the data page
that the record is stored in. In an unbound form, the write lock
will only last as long as it takes the SQL UPDATE to be processed.
Read locks are, of course, much less important (at least for
corruption), but also not entirely irrelevant.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 25 '06 #14

P: n/a
Baz

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:

> "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
> message news:Xn**********************************@127.0.0. 1...
>> "Dave G @ K2" <Da*************@gmail.com> wrote in
>> news:11*********************@v46g2000cwv.googlegro ups.com:
>>
>> > Today I started working on an unbound version and made
>> > pretty good progress. I think my view at the moment is that
>> > the less time that the workstations are "talking directly"
>> > to the tables, the better off they will be. The users will
>> > lose some functionality which I will need to replace in
>> > other ways, but when all else fails, I don't know what else
>> > to do.
>>
>> I would consider that all wasted work if you don't hunt down
>> what went wrong in the first place. You have to get rid of the
>> real problem, rather than spending hours implementing changes
>> that will just reduce your chance of encountering the same
>> problem.
>
> I couldn't agree more. In any case, I don't suppose that the
> unbound form will in reality "talk" to the tables any less than
> the bound one does.

Well, it depends on how efficiently you program it.

If you do it right, the read and write locks will be shorter in
duration, and for the write locks, that will reduce the
suscepitibility to corruption.

But it won't eliminate it at all if you don't address the
underlying cause of the corruption.


Re the locks, how do you figure that out?


Eh? Sorry, but I don't get the question.

While a bound form is dirty, it holds a write lock on the data page
that the record is stored in. In an unbound form, the write lock
will only last as long as it takes the SQL UPDATE to be processed.
Read locks are, of course, much less important (at least for
corruption), but also not entirely irrelevant.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


A dirty form holds a lock? Of course it doesn't, not unless you change it's
Record Locks property.
Apr 26 '06 #15

P: n/a
Wow - lots to go on here. Thanks for all your thoughts and input. I
clearly know less about this than I need, and certainly less that some
of you.

My thoughts on doing an unbound version were that the form is only
'connected' to the data for a very short period. I realise that this
does not eliminate the problem, but I'm hoping that it would reduce it
significantly.

Lyle - I'll try to answer as best I can. The corruption shows up in
different ways. Sometimes users come across a record where some of the
fields, of different types, display as characters that look Chinese to
me. The record looks ok in other respects, but in fact it isn't because
if you try to edit a field you cannot move to another record - it will
not commit the change. So the only choice is a compact and repair. This
always works, but the key index has gone and the corrupt record
conveniently appears at the top of the table with no key field and all
the fields converted to hashes.

Sometimes the user knows of a corruption because certain functions
become very slow, or fail to work at all. In 55000 records it is not
always possible to spot one with Chinese characters, and so I just do a
repair again. On these occasions there is not always an obvious broken
record, the key index is not lost, and following the repair all seems
well.

The database has never been corrupted to the point where it won't open.
And the nature of the business is such that a corruption may not become
obvious for some hours, or even days.

I assume I am up-to-date with service packs - using Microsoft Update -
which should mean I am - doesn't it ??

I have tried creating an empty database and importing the tables, but I
haven't tried your suggestion of importing the data using SQL, mainly
because I'm not sure what to do.

As for the conversion process, I used the wizards, but can't really do
it again because there have been too many changes since the conversion.

Dave

Apr 26 '06 #16

P: n/a
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:
> "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
> message news:Xn**********************************@127.0.0. 1...
>> "Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
>> news:e2**********@news.e7even.com:
>>
>> > "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
>> > message
>> > news:Xn**********************************@127.0.0. 1...
>> >> "Dave G @ K2" <Da*************@gmail.com> wrote in
>> >> news:11*********************@v46g2000cwv.googlegro ups.com:
>> >>
>> >> > Today I started working on an unbound version and made
>> >> > pretty good progress. I think my view at the moment is
>> >> > that the less time that the workstations are "talking
>> >> > directly" to the tables, the better off they will be. The
>> >> > users will lose some functionality which I will need to
>> >> > replace in other ways, but when all else fails, I don't
>> >> > know what else to do.
>> >>
>> >> I would consider that all wasted work if you don't hunt
>> >> down what went wrong in the first place. You have to get
>> >> rid of the real problem, rather than spending hours
>> >> implementing changes that will just reduce your chance of
>> >> encountering the same problem.
>> >
>> > I couldn't agree more. In any case, I don't suppose that
>> > the unbound form will in reality "talk" to the tables any
>> > less than the bound one does.
>>
>> Well, it depends on how efficiently you program it.
>>
>> If you do it right, the read and write locks will be shorter
>> in duration, and for the write locks, that will reduce the
>> suscepitibility to corruption.
>>
>> But it won't eliminate it at all if you don't address the
>> underlying cause of the corruption.
>
> Re the locks, how do you figure that out?


Eh? Sorry, but I don't get the question.

While a bound form is dirty, it holds a write lock on the data
page that the record is stored in. In an unbound form, the write
lock will only last as long as it takes the SQL UPDATE to be
processed. Read locks are, of course, much less important (at
least for corruption), but also not entirely irrelevant.


A dirty form holds a lock? Of course it doesn't, not unless you
change it's Record Locks property.


Of course it does. How else would optimistic locking work? It *has*
to know that someone else is editing the record.

Of course, the problem here may be in differing definitions of
"write lock." You're using it strictly (and probably correctly),
while I'm using it more loosely.

Consider -- if there were no lock and no involvement in the actual
data page, why would a Ctrl-Alt-Delete dirty shutdown of the editing
form corrupt the back end (as it is likely to do)?

Somewhere along the line, a flag is set or a meta-data structure is
in a state that is no correct. That implies to me that some kind of
"lock" (loosely defined) is in place.

The point is that the length of time of this "lock" (loosely or
strictly defined) is less with an unbound form than with bound, but
never zero. Thus, it's important to resolve the underlying issue
that caused the problem in the first place, rather than trying to
fix it by doing all the work to unbind the form, just to find that
the problem can still strike.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 26 '06 #17

P: n/a
Baz

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
news:e2**********@news.e7even.com:

> "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
> message news:Xn**********************************@127.0.0. 1...
>> "Baz" <ba**@REMOVEbcap.THEeuro1net.CAPScom> wrote in
>> news:e2**********@news.e7even.com:
>>
>> > "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
>> > message
>> > news:Xn**********************************@127.0.0. 1...
>> >> "Dave G @ K2" <Da*************@gmail.com> wrote in
>> >> news:11*********************@v46g2000cwv.googlegro ups.com:
>> >>
>> >> > Today I started working on an unbound version and made
>> >> > pretty good progress. I think my view at the moment is
>> >> > that the less time that the workstations are "talking
>> >> > directly" to the tables, the better off they will be. The
>> >> > users will lose some functionality which I will need to
>> >> > replace in other ways, but when all else fails, I don't
>> >> > know what else to do.
>> >>
>> >> I would consider that all wasted work if you don't hunt
>> >> down what went wrong in the first place. You have to get
>> >> rid of the real problem, rather than spending hours
>> >> implementing changes that will just reduce your chance of
>> >> encountering the same problem.
>> >
>> > I couldn't agree more. In any case, I don't suppose that
>> > the unbound form will in reality "talk" to the tables any
>> > less than the bound one does.
>>
>> Well, it depends on how efficiently you program it.
>>
>> If you do it right, the read and write locks will be shorter
>> in duration, and for the write locks, that will reduce the
>> suscepitibility to corruption.
>>
>> But it won't eliminate it at all if you don't address the
>> underlying cause of the corruption.
>
> Re the locks, how do you figure that out?

Eh? Sorry, but I don't get the question.

While a bound form is dirty, it holds a write lock on the data
page that the record is stored in. In an unbound form, the write
lock will only last as long as it takes the SQL UPDATE to be
processed. Read locks are, of course, much less important (at
least for corruption), but also not entirely irrelevant.


A dirty form holds a lock? Of course it doesn't, not unless you
change it's Record Locks property.


Of course it does. How else would optimistic locking work? It *has*
to know that someone else is editing the record.

Of course, the problem here may be in differing definitions of
"write lock." You're using it strictly (and probably correctly),
while I'm using it more loosely.

Consider -- if there were no lock and no involvement in the actual
data page, why would a Ctrl-Alt-Delete dirty shutdown of the editing
form corrupt the back end (as it is likely to do)?

Somewhere along the line, a flag is set or a meta-data structure is
in a state that is no correct. That implies to me that some kind of
"lock" (loosely defined) is in place.

The point is that the length of time of this "lock" (loosely or
strictly defined) is less with an unbound form than with bound, but
never zero. Thus, it's important to resolve the underlying issue
that caused the problem in the first place, rather than trying to
fix it by doing all the work to unbind the form, just to find that
the problem can still strike.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Obviously, in the default optimistic locking scenario, another user can
update a record whilst I have it dirty, so clearly there is no write lock in
place. This is the very definition of optimistic locking.

There is another way of knowing that someone else has edited the record: you
retain a "copy" of the original record before any updates, so that,
immediately before carrying out the updates, you can compare the original
record against the record as it is now. As I understand it, this is exactly
what Access does when using a SQL Server database (unless there is a
timestamp column, in which case it simply compares the timestamps - oh,
there's *another* way of making optimistic locking work).

To be honest, I don't know how it works with an mdb back-end, and what you
say may well be correct, although I think you are also correct in suggesting
that you are stretching the meaning of the term "lock"! 'Twould be
interesting to know if anyone can point to a definitive answer on this.
Apr 27 '06 #18

P: n/a
I am sorry to say that I am baffled.

Apr 27 '06 #19

P: n/a
For your info_____:

It's been nearly two weeks now - fingers crossed - since preventing use
of filter by form in my application there has not been a single
corruption.

Dave

May 10 '06 #20

P: n/a
Fascinating!

May 10 '06 #21

P: n/a
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:
For your info_____:

It's been nearly two weeks now - fingers crossed - since
preventing use of filter by form in my application there has not
been a single corruption.


Huh.

You've figured out how to avoid the corruption, but you haven't
really fixed the actually problem -- you're just avoiding it.

As far as I can see, there must be something corrupt in your
application or in the Access/Jet versions installed on the
workstations causing the corruption. I wouldn't consider the problem
resolved until I was able to use filter by form again.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #22

P: n/a
David

I couldn't agree more. In fact I'm having to provide other ways for
users to do what they used to do using filter-by-form. Problem is that
I have no idea what was causing the corruption. But believe me it's a
huge relief just to get this far.

Dave

May 10 '06 #23

P: n/a
"Dave G @ K2" <Da*************@gmail.com> wrote in
news:11*********************@i40g2000cwc.googlegro ups.com:
I couldn't agree more. In fact I'm having to provide other ways
for users to do what they used to do using filter-by-form. Problem
is that I have no idea what was causing the corruption. But
believe me it's a huge relief just to get this far.


I've not looked back at the whole thread, but I assume you've
rebuilt the application front end with SaveAsText to eliminate any
corruption.

Have you also checked the Jet versions and Access versions to see if
they are consistent?

And is there anything odd about your recordsource in the problematic
form? Or, a better question, are some forms causing corruption with
filter-by-form and others not? I know that Jet can be sensitive to
certain kinds of joins and other operations and cause crashes
(though I've never seen it cause corruption).

Also, if your forms are using saved QueryDefs in their
recordsources, perhaps the compilation in the QueryDef has gotten
corrupted or out of data. A compact usually fixes that, but another
tactic is to put the SQL directly into the recordsource, and avoid
using any saved QueryDefs.

But it may be that you've tried all of these things already.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #24

This discussion thread is closed

Replies have been disabled for this discussion.