[ADP/SQL Server] Access crash when user update a CheckBox on a form
Emiel Metselaar
access xp, sql server sp1. I tried mdac 2.6 and 2.7 plus
latest service packs.
In my case i have a form with only a checkbox. The ms-
access form gets its recordset on the form_open event from
a manually opened ado recordset (see bottom of the
message). The table i am opening with my recordset
contains an id and a bit field.
In my case depending on the connection settings access
crashes or i am 'stuck' on the current record (cannot get
of it anymore). I see this behaviour in both a mdb as an
adp.
In case of a crash I have either the following in the
event viewer:
------------------------------------------------------
Fault bucket 06615362.
0000: 42 75 63 6b 65 74 3a 20 Bucket:
0008: 30 36 36 31 35 33 36 32 06615362
0010: 0d 0a
------------------------------------------------------
Or:
Faulting application msaccess.exe, version 10.0.3409.0,
faulting module msaccess.exe, version 10.0.3409.0, fault
address 0x001b5a22.
0000: 41 70 70 6c 69 63 61 74 Applicat
0008: 69 6f 6e 20 46 61 69 6c ion Fail
0010: 75 72 65 20 20 6d 73 61 ure msa
0018: 63 63 65 73 73 2e 65 78 ccess.ex
0020: 65 20 31 30 2e 30 2e 33 e 10.0.3
0028: 34 30 39 2e 30 20 69 6e 409.0 in
0030: 20 6d 73 61 63 63 65 73 msacces
0038: 73 2e 65 78 65 20 31 30 s.exe 10
0040: 2e 30 2e 33 34 30 39 2e .0.3409.
0048: 30 20 61 74 20 6f 66 66 0 at off
0050: 73 65 74 20 30 30 31 62 set 001b
0058: 35 61 32 32 0d 0a 5a22..
------------------------------------------------------
I have also replaced the checkbox with a textbox and then
by typing true and false the form also works.
And needles to say if the form_open section is omitted and
the form recordsource is filled in design view the form
also works as expected.
Emiel
------------------------------------------------------
Code section:
Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' 'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MyDataSource"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "MyCatalog"
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM testbit"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
Set Me.Recordset = rs
End Sub
------------------------------------------------------
>-----Original Message-----
>I have a big problem using Access Data Project.
>SW Version: Access XP, SQL Server SP2, Win 2000 Server SP2
>
>Short description:
>The problem happen when I have a form with a CheckBox
linked to a bit column
>of a table/view.
>If the form is locally filtrered, every click on the
checkbox make Access
>crash.
>
>Detailed description, (behavior reproduction):
>* create a table T , in that table create a 'bit' column
B, and an identity
>column ID
>* insert some value in table T
>* create a view V that extract a set of data from table T
(Select * from T)
>* create an ADP that access the DB containing T and V
>* create a form F based on the view V
>* set to the right value to the property UniqueTable (T)
and ResyncCommand
>(Select * from T where ID=?) of the form F
>* on the form F you'll have a CheckBox control associated
to the bit column
>B
>* create a button FILT that when clicked execute he
following code (impose a
>local filter)
> Me.Recordset.Filter ="ID>3" 'string that impose a
filter
> Set Me.Recordset =Me.Recordset 'update the screen
>
>Ok, now if you open the form F, you see all inserted
value, and you can
>update the
>checkbox without problem; BUT after you impose the filter
clicking the
>button FILT...
>every attempt to update/chancge/click-on a checkbox make
Access INSTANTLY
>crash
>
>My analysis:
>* The problem only exist when I impose a local filter
(but I need that
>filter on the client (it's a client-server App))
>* The problem persist even in the column B is an 'int'
instead of a 'bit'
>* The problem persist even if on the checkbox control I
impose the following
>event:
>
> Private Sub checkbox _BeforeUpdate(Cancel As
Integer)
> Cancel = True
> End Sub
>
>* The problem persist even if I try to change the
checkbox value directly
>from the code
>* The problem persist even if I try to change directly
the recordset column
>value from the code
>* The only event executed is on_MouseDown, every other
event are not reached
>* Acces crash 'istantly' after the click on the checkbox,
the following
>breakpoint will be never reached
>
> Private Sub checkbox _BeforeUpdate(Cancel As
Integer)
> * Cancel = True
> End Sub
>
>* During the crash, nothing happen on sql server; no type
of comunication
>between access and SQL Server (I checked this using SQL
profiler)
>* Strange thing: even if I set checkbox.locked=true,
checkbox ISN'T locked
>* I searched the net (msdn, technet, www...) for this
problem, but I found
>nothing
>
>Please, help me!
>
>Thanks, Max
>
>
>.
>
MegaMax
> I am having almost the same problem here. Also with ms-
> access xp, sql server sp1. I tried mdac 2.6 and 2.7 plus
> latest service packs.
I use MDAC 2.7, SQL Server SP2, Win2k Server SP2, Access XP
> In my case depending on the connection settings access
what are these settings?
> crashes or i am 'stuck' on the current record (cannot get
> of it anymore). I see this behaviour in both a mdb as an
> adp.
Thanks, Max
Emiel Metselaar
For example the settings i posted in the form_open code.
But the use is very limited, because afterwards you can
modify only one record. Did you also try to replace the
checkbox with a textbox?
If you get some better results please post it here, since
i still haven't found a real solution.
Thanks, Emiel
MegaMax
"Emiel Metselaar" <E.Met...@metamicro.nl> ha scritto nel messaggio
news:1280c01c21c5b$11b65980$3bef2ecf@TKMSFTNGXA10...
> >what are these settings?
> For example the settings i posted in the form_open code.
>
> But the use is very limited, because afterwards you can
> modify only one record. Did you also try to replace the
> checkbox with a textbox?
Yes, and I see that the text box work fine, even if in the same form I have
2 control associated with the same bit column; if I modify the text box,
everithing works, and the checkbox show the correct state of the row; if I
click on checkbox... crash :-(
> If you get some better results please post it here, since
> i still haven't found a real solution.
Do you try Office XP SP1?
Now I can't install it on my workstation.
Thanks, Max
MegaMax
> >Do you try Office XP SP1?
> I have tried both Office XP with and without sp1, same
> results. In the event viewer do you also see the events
> from the msaccess crash?
I see that the only event executed is MouseDown; every other events I tried
will never be executed/reached.
Max
Emiel Metselaar
other events I tried
>will never be executed/reached.
adminastrative tools - event viewer).
Emiel
MegaMax
> I mean the windows event viewer (start menu - programs -
> adminastrative tools - event viewer).
Yes, I see this event:
Faulting application msaccess.exe, version 10.0.2627.1, faulting module
msado15.dll, version 2.70.7713.0, fault address 0x000450ff.
Max
John Sal
I had the same problem trying to use disconnected recordsets!
My work around was "Me.Recorset.close"(!!!!!!), and I work with
me.recorsetclone whenever I want something from the recordset (fields etc).
I don't know why it works like that.
This soultion is not appropriate for you becouse you work "connected
recordsets" so just try to filter with cmd.applyfilter
<null>,<where=yourfilter>. It works!
Please let us know if that works for you, or leads you anywhere!
rgrds,
John