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" <(E-Mail Removed)> wrote in message
news:%23c$%(E-Mail Removed)...
> 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:(E-Mail Removed)...
>> 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
>
>
Similar Threads
