Edit on continuous form: Access 2002/2003 crashes ; Access 2000 ok.

[HJ]

Hi all,

In Access 2000 I have an Access Project with a continuous form based on a
stored procedure with input parameters. When I edit data the changes are
save correctly.

However, when I use the same .adp file in Access 2002 or Access 2003 changes
on the continuous form lead to a program crash. The error message is the
standard 'crash message': "Microsoft Access has encountered a problem and
needs to close. " Etc.

Why does this happen? In what way do Access 2002 and 2003 treat this
continuous form differently than Access 2000?

Thanks for your help,

HJ

 

[Sylvain Lafontaine]

Check the references and decompile/recompile your project. Maybe this will
help. You can also try to create an empty database and import everything
into it.

Another possibility to solve your problem might be to create a Resync
command or even a dummy Resync command.

Bit fields are known to be troublesome with A2002 running on WinXP with SP2
(but not with SP1) or with A2003 running on Win2003 (but not with A2003 on
WinXP). This is particularly true when you are using .RecordSet.Clone or
..RecordsetClone for moving around.

Please note that I don't remember any particular difference between single
and continuous forms for that matter; so is your problem only occurring with
continuous form?

 

[HJ]

Thank you for your quick response. Your suggestions have not solved the
problem and I will answer below.

Check the references and decompile/recompile your project. Maybe this will
help. You can also try to create an empty database and import everything
into it.

I have done this and it did not result in any error messages nor did it
solve the problem.

Another possibility to solve your problem might be to create a Resync
command or even a dummy Resync command.

I have put a Resync command in the BeforeUpdate event of the form. Access
2002 crashes at the rs.Resync code line.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Resync adAffectCurrent, adResyncAllValues <==
Set rs = Nothing
End Sub

Bit fields are known to be troublesome with A2002 running on WinXP with SP2
(but not with SP1) or with A2003 running on Win2003 (but not with A2003 on
WinXP). This is particularly true when you are using .RecordSet.Clone or
.RecordsetClone for moving around.

I was not using these methods until the above mentioned test.

Please note that I don't remember any particular difference between single
and continuous forms for that matter; so is your problem only occurring with
continuous form?

I have tried this. I changed the continuous form into a single form. That
resulted in the same crashes.


The behaviour is very consistent: I can edit the data on a line in the
continuous form and when I move to another record or when I close the form,
Access 2002 and 2003 crash. Access 2000 has no problems with this and the
update is saved on SQL Server.


What else can I do to solve this? The form is based on a stored procedure
with input parameters, the recordset type of the form is set to Updatable
Snapshot, max records is set to 0, the unique table is set correctly and
there is no resync command.

Thanks for your help,

HJ

 

[Sylvain Lafontaine]

Oh, I'm sorry about the little misunderstanding but by Resync Command, I was
talking about the Resync Command property that is under the Unique Table
property under the Data tab for the properties of the Form and not about
making a call to the rs.Resync() function.

There are some types of field that are troublesome with Access on some
occasions because Access doesn't correctly see the update to the SQL-Server:
bit, timestamp and real fields (on the SQL-Server side: real field: single
precision, 4 bytes wide. Instead of using Real fields, it's a better idea
idea to use Float field (8 bytes) when the database is to be accessed by
Access. For VBA, Double variables are 8 bytes while Single variables are 4
bytes.)

You should use the Profiler on SQL-Server to see what Access is doing and
what field is causing trouble.

For the Resync Command, it's easy to build one: write a stored procedure
identical to your previous SP but with one
parameter: the primary key for your main table and returning only one record
(the new values for the current record to be resync). The property of the
Resync command is simply the name of your resync stored procedure followed
by a single question mark:

My_ResyncQuery ?

A dummy resync command is even simpler to build: use any stored procedure
with the wrong number of parameters (any number of arguments greater than
the number of fields in your primary key; which is usually one or maybe two
or three) :

DummyResyncCommand ?, ?, ?, ?, ?

 

[Razvan Socol]

Hello, HJ

What is the module and offset of this error? You can find out this
information by clicking on "To see what data this error report
contains, click here" link when the crash appears. Please tell us all
the information from the "Error signature" section (AppName, AppVer,
ModName, ModVer, Offset). Also, please tell us what operating system
are you using, what service pack is installed and what version of MDAC
is currently installed (you can find this using MDAC Component Checker
from Microsoft).

Razvan

 

[aaron.kempf]

i have NEVER used the input parameters BS

just use EXEC myProc in the recordsource and then requery.

i specifically do this becuase the input parameters ins't reliable
enough.

HTH