Error with Bit values in VBA code in Access 2003 after SP3

[Guest]

I quote another user on this forum, as I have not seen this as an own question.

"I have another problem with access 2003 with sp3 adp poject. If you
have a table with a bit column and default value 0 before sp3 access
insert correctly false, after sp3 access insert true. This is only one
problem, with sp3 i discover a lot of problem, im very scared !!!"

We have a field in our CustomersTable on the SQL that we call, say
"CustomerLocked".
This is a bit value that defaults to 0.
When an Agent opens a customer through the ADP Access file, a Stored
Procedure runs on the SQL. That procedure selects an appropriate cutomer,
sets "CustomerLocked" to 1 and then sends "Select * from Customer where ID =
xxx" as the rowsource to Access.

When the Agent is done with the Customer a set of VBA commands is run and
one of them is:
Me.CustomerLocked.Value = False

But for some reason this does not affect the customer in the database.
But even more strange is that it sometimes actually do work.
We are baffled by this, and cannot see a possible solution to this!
What we do now is run a job that resets the "CustomerLocked" to 0 every night.
But our Agents can only contact the Customers once per day, until this is
resolved.

Its urgent!
Ideas?

Note: This is not our only problem so far...
I have more posts written.

 

[Sylvain Lafontaine]

From the description of your problem, it looks like that you have a locking
problem with a transaction that's kept open after you execute the SP and
sent back the Select query to be used as the row source.

It's also not clear when the call to « Me.CustomerLocked.Value = False » is
done: before or after the other changes to the current record have been
made/sent by Access to the SQL-Server. If this instruction executed before
or in the BeforeUpdate event or after or in the AfterUpdate event of the
form? Is Access sending this command separately to the SQL-Server or
embedding it with other updates to the table?

By taking a look with the SQL-Server Profiler, you should see what's
happening on the Server and why this call to « Me.CustomerLocked.Value =
False » is failling most of the time.

Another possibility would be that the change to « "CustomerLocked = 1 » is
still locked on the server because the transaction is still open and is kept
opened when the query is returned to the form; so you should check for any
outstanding lock on the server.

Are you using this SP directly as the RowSource of the form or if you are
making a direct call to the server using the CurrentProject.Connection
object or another connection object?

Finally, I don't think that this locking problem has anything to do with the
problem of inserting a default value for a bit field to a new record on a
table; however, you could try changing the type of the CustomerLocked field
to something else like a tinyint.

 

[MB]

We also have a problem with a bit column & SP3, not sure if it is related.
If you change from true to false on a form, it will change itself right
back.

And even more bizarre if you edit the table directly as soon as you move to
the ext row it will change back.

Seems to be related to SP3 as I have the identical ADP running on 2
machines, one with SP2 that does not exhibit this behavior. I'm going to add
a new post re the above.

 

[Guest]

I quote another user on this forum, as I have not seen this as an own question.

"I have another problem with access 2003 with sp3 adp poject. If you
have a table with a bit column and default value 0 before sp3 access
insert correctly false, after sp3 access insert true. This is only one
problem, with sp3 i discover a lot of problem, im very scared !!!"

We have a field in our CustomersTable on the SQL that we call, say
"CustomerLocked".
This is a bit value that defaults to 0.
When an Agent opens a customer through the ADP Access file, a Stored
Procedure runs on the SQL. That procedure selects an appropriate cutomer,
sets "CustomerLocked" to 1 and then sends "Select * from Customer where ID =
xxx" as the rowsource to Access.

When the Agent is done with the Customer a set of VBA commands is run and
one of them is:
Me.CustomerLocked.Value = False

But for some reason this does not affect the customer in the database.
But even more strange is that it sometimes actually do work.
We are baffled by this, and cannot see a possible solution to this!
What we do now is run a job that resets the "CustomerLocked" to 0 every night.
But our Agents can only contact the Customers once per day, until this is
resolved.

Its urgent!
Ideas?

Note: This is not our only problem so far...
I have more posts written.

 

[Guest]

I have the same problem and I have verified that the problem occurs only
after installing Office 2003 SP3. Bit fields with default set as "0" in the
database show up in Access tables, queries, and forms, as "1". If I am in
the "Tables" portion of Access and I attempt to tab into a new row, the bit
fields shows as "0" before I enter any values into the row. After I enter
the first value, the bit field immediately changes to "True" (not "1").
Other default values in the row, such as system generated GUID's, insert
properly. Finally, the behavior is erratic! On rare occasions the bit value
will show up properly when accessed by an Access Form. (However, most of the
time the problem does show up when using Access Forms.) This is a serious
and glaring problem and Microsoft should fix it quickly.

 

[jetonline]

Same problem here.

An insert directly into the SQL server database using SQL Query Analyzer works fine, the default false (0) bit values are inserted correctly. However an insert into the same table via an Access ADP project results in the default values being changed to true(1). What a nightmare!

Has anyone found a work around or a fix?

 

[Guest]

Same problem encountered. As a temporary solution to the problem I have
changed the bit datatype to tinyint. In stead of using True and False as
values, I use 1 and 0 respectively. This works fine as the 1 and 0 are
evaluated as True and False in Access.
In cases where you need to display the text 'True' or 'False' use
cBool(<FieldValue>) to change the data type. You can try this with a MsgBox.

 

[jetonline]

http://support.microsoft.com/kb/943967/en-us