MS Access project with SQL Server - Edit on continuous form: Access 2002/2003 crashes ; Access 2000 ok.

Author

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

2005-06-18, 11:25 am

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

2005-06-18, 8:24 pm

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?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"HJ" <hjiscool@nospam.nospam> wrote in message
news:OHp7UtBdFHA.2736@TK2MSFTNGP12.phx.gbl...
> 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
>
>

2005-06-19, 9:23 am

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

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eHFn6eDdFHA.2128@TK2MSFTNGP15.phx.gbl...
> 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(Ca
ncel 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

2005-06-19, 8:25 pm

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 ?, ?, ?, ?, ?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"HJ" <hjiscool@nospam.nospam> wrote in message
news:%23c$%23dgNdFHA
.3616@TK2MSFTNGP09.phx.gbl...
> Thank you for your quick response. Your suggestions have not solved the
> problem and I will answer below.
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:eHFn6eDdFHA.2128@TK2MSFTNGP15.phx.gbl...
> will
> I have done this and it did not result in any error messages nor did it
> solve the problem.
>
> 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(Ca
ncel As Integer)
> Dim rs As ADODB.Recordset
> Set rs = Me.RecordsetClone
> rs.Resync adAffectCurrent, adResyncAllValues <==
> Set rs = Nothing
> End Sub
>
>
> SP2
> I was not using these methods until the above mentioned test.
>
>
> with
> 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
>
>

Razvan Socol

2005-06-21, 9:24 am

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@gmail.com

2005-06-23, 1:24 pm

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

Sponsored Links