Record Disappear After Adding Data from Access 2003 FORM

[Guest]

Hi, Lately we have this issue.
Currently we have Access 2003 ADP with backend of sql 2000.
About 10 concurrent users are using bounding form to enter record.
some users record they entered using Form which has two more subform,
record will stay but few min later they go back record disappear.
It happen very randomly, I am not sure if it problem with sql 2000 or adp
form.
We use microsoft mappoint that retrieve record from same database.
I don't know that will affect the problem. Or is there suggestion?

Thanks

Jason

 

[Sylvain Lafontaine]

Lately? Does this problem appeared on systems that were working correctly
before? If so, then maybe the cause is Office Service Pack 3. There have
been many reports of problems with ADP and SP3.

If not then you should give more details about these disappearing records.
Are these records gone for good or if there are still there on the
SQL-Server? Does this problem occurs only with some users and not for
others? Any trigger on the tables? Any Views? Any particularity like Bit
fields?

 

[wendy10525]

»ØУÕæ¸ßÐË

 

[52008]

ÄãºÃ£¡

 

[Guest]

Good Questions Sylvain.
We all currently have SP2 version of Access 2003
1. Mainly only some people lose records.
2. On the data entry form we used view that link with three different tables.
3. One of table has two bit fields
4. I make screen copy previous day and compare next day looking at the table.
1 or 2 record out of 20 record somehow erased everything is became empy
field except primary key record.

 

[Sylvain Lafontaine]

Is this a multistep updating (ie, an update updating more than one table)?
Even if it's not, it's usually a good idea to deactivate it by setting the
UniqueTable property - as well as the Resync property - of the form.
Changing the bit fields to tinyint fields might also help.

As this problem is only occurring for some people, I would make sure to do a
decompile/recompile of the ADP file directly on the affected machines, as
doing only a Repair/Compact might be insufficient here.

Also - and very important - make sure that you don't use of RecordsetClone
instead of Recordset.Clone. There have been many reports of memory
corruption with ADP in the past when RecordsetClone was used and maybe this
is what's happening to some of your users. In some of these reports, bit
fields were involved; if I remember correctly.

If possible, taking a look with the SQL-Server Profiler or even with a Log
analyser might shed light on this but personally, I cannot think of a
scenario where this problem will occur (excerpt for the case with
RecordsetClone).

 

[Guest]

You are truly MVP for microsoft.
Those are very good suggestion specially recordsetclone.
I used recordsetclone for refreshing my data.
I will change this to recordset.clone and see what happen.
Also what's difference between recordset.clone and recordsetclone?

Thanks

 

[Guest]

I changed recordsetclone to recordset.clone.
It seems ok for moment but still having the problem.
WHat would do "bit" value of form with losing record?
Do we just not to show bit from form or completely change from table bit to
tinyint?

Thanks

 

[Guest]

Also one of the user got following message
"The macro or function set to the BeforeUpdate or ValidationRule Property
for this field is preventing "xxxXXX" database from saving the data in the
field.
After user see write conflict error message when they try to click X button.
I don't know if this help to get some idea.

 

[Sylvain Lafontaine]

Well, you should take a look at these macro and see or tell us what they are
doing. Maybe that you have also a ValidationRule (either in Access or
directly on the SQL-Server table or column) that is forbidding the writing
of the values as they have been entered by the user. If the user has made
an error while entering these values and these are not written because they
are invalid per see of a table or column constraint (or thery are valid but
there is an error in the macro validation rule), this scenario would
perfectly fit the result that you are getting (ie, a record is first created
but after that no other values are written).

For the difference between RecordsetClone and Recordset.Clone, the first one
call the second on its first call but will internally cache the cached
cloned recordset and will reuse it / return it on the subsequent calls. For
example, in the following piece of code, both rs1 and rs2 will get a
reference to the exact same clone:

Dim rs1 as ADODB.Recordset
Dim rs2 as ADODB.Recordset

set rs1 = Me.RecordsetClone
set rs2 = Me.RecordsetClone

If these calls are made inside subroutine, the cached cloned recordset will
be kept alive and existant even after the exit of the subroutine. With
Recordset.Clone, a new clone will be created each time and will be destroyed
at the end of the subroutine or when the variable will be set to Nothing (or
to another recordset).

 

[Guest]

Thanks Sylvain.
Still seem to have a problem
Do you do any consulting? I need some assitant maybe you can look at it
through remote

THanks

 

[Sylvain Lafontaine]

Yes, I do consulting but you know that a price will be attached to it. You
can contact me by email if you want to and it will be my pleasure to answer
you on that matter.

Regards,

 

[Guest]

Here is what I found so far.
We tried user to enter with very simple form which worked fiine year 2004.
Even that using that bound form to enter new record having similar problem
to lose record.
So we check everybody's computer.
Couple of user has MDAC 2.7 RTM version with SP1
Other (8) users have MDAC 2.8 with SP2 version I am not sure if that would
conflict to save record to database .
Also which version of MDAC you should use with SQL 2000 SP4 version
Does anyone know about this issue?
Also One user used 10mbps network cable other all use 100mb network calbe

Thanks

 

[Guest]

We created Trigger which track whenver user update the information from
Access 2003 Form.

We found out that sometimes it automatically update null value on whole table.
We are not sure what would cuasing that.
Does anyone know in access 2003 form , what would cause update null values.

Thanks