Link to home
Start Free TrialLog in
Avatar of Phil
PhilFlag for United States of America

asked on

Microsoft Access Write errors seem to be caused by bit fields

I have an Access application linked to a SQL Server database and are having some database issues. The first thing that popped up happened when installing a new version at my client site. We opened the application prematurely before one of the database changes was made which bombed the application. We then made the appropriate changes to the database and thought everything was fine. However, when the application bombed, the link between the order header table and order detail table as displayed in the database diagram was missing and referential integrity was lost. When they released an order from pending status, the header record showed the order being released but the detail records remained in pending status.

This application has been active for over 15 years and nothing like that ever happened before.

We now are experiencing Write errors from time to time which seem to be related to bit fields. We always set the default value to ((0)) and leave the allow nulls setting to false. Sometimes when creating the fields and setting the default value to ((0)) and leaving the allow nulls setting to false automatically sets the value of the fields to zero in existing records. It appears that this does not always work.

Are there any procedures or settings I can make that will make bit fields fool proof? Or should I use a tiny integer for boolean behavior?

In addition we had a write error last week that I was able to trap the line of code causing the error and fixed it by remming the offending lines. Years ago my VBA code had the following lines:     DoCmd.GoToRecord , , acFirst
    DoCmd.GoToRecord , , acLast
Those lines of code have been in the application for probably 10 years or more. I don't remember why they were there but evidently I had issues that forced me to include them. Now they cause a write error.

It seems that Microsoft office applications are less and less stable with each successive version which is very frustrating to me. All these new versions do is add "fluff" and no stable substance.  Sorry for my rant.

Thank you very much..
SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Phil

ASKER

Thank you very much, I appreciate your comments and suggestions
Avatar of Phil

ASKER

I went through all tables in the database and corrected all issues with respect to nulls in bit fields and things have improved. Thank you so much!!