[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access ADP is crashing when a form checkbox is checked

Posted on 2007-10-12
Medium Priority
Last Modified: 2013-12-05
I am working with an Access 2002 .adp that connects to a SQLServer database over a network.  There are several of the .adp front-ends on people's workstations and are networked to the SQLServer machine on a small LAN (total of 7 workstations).  The application is basically an inventory management and sales tool (for a farmers' cooperative).  The application has been working fine for several years and I occasionally do modifications to make the software match new business practices, etc.  I made some modifications recently and afterwards this problem started.

There is a form that displays the results of a view from SQLServer in an easy to read format.  The form has the Unique Table value set so that the records are updatable, but there is only one field on the form that is left unlocked for the users to modify.  This is a checkbox called "Push", which is used so that the sales manager can mark which items the salespeople should be encouraging the customers to buy.  It is a two-state checkbox, either on or off.

The problem is that since my latest modifications (which didn't involve making any changes to this Push functionality), every time a user tries to mark an item for Push, the adp will crash and create an auto-backup.  Every time.

Unfortunately, this error was not detected immediately, and I have since left the area so I am no longer working for them on-site.  I have a copy of their setup on my own computer, but I am using Access 2003, with the SQLServer on the same workstation, and I am not having the problem that they are having.  But not being on-site is making it difficult to trouble-shoot.  

I can't post the exact code for the VBA Event OnClick (for when the checkbox is clicked) at the moment because I'm on the wrong computer, but I will add it later.  However it is very short, just setting a bookmark on the current record and set Me.Dirty = False.  Also this code was not modified with my recent changes, it is the same as in several previous version.  I tried modifying the code (commenting out pretty much everything) and they reported the same error.

A colleague on-site tested this with a computer that is there and running the latest version on a single computer (ADP and SQLServer on the same workstation - the testing machine that I was working on while I was there) and it's also having the same error, which seems to indicate that the networking is not part of the problem.  But I haven't been able to duplicate the error in my own testing environment (which is only different in that I'm using Access2003, as I said).
I have attempted replacing their version of the .adp with the one that is working on my computer, but that didn't help.  I have checked that the UniqueTable is set properly, I have checked that the view is returning the data correctly and that the underlying primary table hasn't been modified to remove or change that data field.    I'm not sure what else to try, so I'm hoping an expert out there will be able to guide me!  

Thanks very much for any and all input.
Question by:rsoble
LVL 32

Accepted Solution

Daniel Wilson earned 900 total points
ID: 20077413
What happens, working against the problem DB, if someone issues SQL to do what the checkbox is supposed to do?

Update TableName Set Push = 1 where PK = Whatever

Can the colleague on site run SQL Profiler and capture the exact instructions the SQL Server DB is getting when the crash occurs?  If those instructions are re-run against the SQL Server DB using query Analyzer, what happens?

I'm hoping there's something going on at the DB level, not in the Access application ... and those tests should answer that question.

Author Comment

ID: 20083397
Hi Daniel Thanks for your input.  I'll do some research into it and get back to you.   I have to work with their busy schedules so sometimes things will take a few days to get done.  Please be patient and don't think I've forgotten if I don't reply for a few days.  :)
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 600 total points
ID: 20092339
This could be related to the SQL bit field type. When access connects to it, it can strike a problem if the SQL database has not explicitly set a value. Ensure that the default is set to 0 (false) for that field and then runan update query (Stored proc) to set every balue that is not true to false.

Problem may go away without having to alter the adp.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 20131934
I finally got the results of the trace from my colleague (he was off for a few days).  It doesn't look good, it seems to me that it indicates the problem is in the ADP but I'll let you look.

Here's what the trace shows when I run the code here on my computer, where it is working properly:

exec sp_executesql N'UPDATE "CELERIAC2"."dbo"."tblAvailability" SET "fld_bolPush"=@P1 WHERE "fld_CropID"=@P2 AND "fld_iInventory"=@P3 AND "fld_iIncoming"=@P4 AND "fld_iSold"=@P5 AND "fld_iIncomingOutlook"=@P6 AND "fld_iSoldOutlook"=@P7 AND "fld_bolZero"=@P8 AND "fld_curRegularPrice"=@P9 AND "fld_curDiscountPrice"=@P10 AND "fld_iDiscountLevel"=@P11 AND "fld_bolPush" IS NULL AND "fld_iSurplus"=@P12 AND "fld_iSurplusOutlook"=@P13 AND "fld_iIncomingTwoDay"=@P14 AND "fld_iSoldTwoDay"=@P15 AND "fld_iSurplusTwoDay"=@P16 AND "fld_dtAvailDate"=@P17 AND "fld_dtOneDayOut"=@P18 AND "fld_dtTwoDayOut"=@P19', N'@P1 bit,@P2 nvarchar(7),@P3 real,@P4 real,@P5 real,@P6 real,@P7 real,@P8 bit,@P9 money,@P10 money,@P11 real,@P12 real,@P13 real,@P14 real,@P15 real,@P16 real,@P17 datetime,@P18 datetime,@P19 datetime', 1, N'1FRHU20', 4.000000000000000e+000, 1.000000000000000e+000, 4.000000000000000e+000, 4.000000000000000e+000, 3.000000000000000e+000, 0, $40.0000, $37.5000, 3.000000000000000e+000, 0.000000000000000e+000, 0.000000000000000e+000, 8.000000000000000e+000, 1.200000000000000e+001, 0.000000000000000e+000, 'Sep 25 2007 12:00AM', 'Sep 26 2007 12:00AM', 'Sep 27 2007 12:00AM'

exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."viewAvailability" ) AS DRVD_TBL WHERE "fld_CropID" = @P1', N'@P1 nvarchar(7)', N'1FRHU20'

Basically it's sending all the data for the row that is being updated back to the server and then returning the updated row (fld_CropID is the Primary Key).

Here is what the trace shows when the ADP is crashing:

EXEC sp_fkeys NULL, NULL, NULL, 'tblCrops', 'dbo', 'CELERIAC2'

EXEC sp_fkeys NULL, NULL, NULL, 'tblAvailability', 'dbo', 'CELERIAC2'

I don't understand why the exec has changed from sp_executesql to sp_fkeys.  Maybe if we could answer that question, we would find the source of the problem.   And clearly it's only sending null values which isn't good either.    So it looks like the problem is in the ADP (unfortunately, because I find the ADP stuff much harder to troubleshoot/solve than the SQLServer stuff).

Kelvin, I did think about your comment on the bit type, but at this point I don't think that's the issue.  I'll keep it in mind in case it helps down the line.  The table that this updates is one that is recreated every morning, and the Push values are left at NULL, but it would be very easy to modify the code to set them all to 0 when the table is created each day.

So if we've narrowed the problem down to the ADP, here's what the VB code looks like, in case it helps.  

Private Sub chkPush_Click()
    Dim strBookmark As String
    strBookmark = Me.Bookmark
    Me.Dirty = False
    Me.Bookmark = strBookmark
End Sub

It's rather short, isn't it?  The bookmark stuff isn't even necessary any longer, it could be removed. (I tested commenting it out but it didn't change anything.)  If we can figure out why the trace is showing sp_fkeys instead of sp_executesql that might help.  

One thing that has changed in this version of the ADP from the previous version is that I put in a menu/toolbar rather than all the functions being on a main form with links on it.  I had to learn about creating menus/toolbars in the process, so it's possible that there's something I don't know about them and perhaps that affected this functioning?  When the ADP opens, I hide the default menu and use my own version of it that has some of the functions removed (hide/unhide, open VB, etc, things that we don't want the users messing with).  And then there's a custom toolbar with links to the forms.  I don't think this would cause this problem, and actually we tested seeing if the problem happens with the menu set back to normal and it still happened, but I thought I'd mention it in case it's useful.  

Thank you Daniel and Kelvin for your ideas so far!

Author Comment

ID: 20177913
FYI I have opened another question on this topic, with a more specific title in hopes that I will get more replies.  (http://www.experts-exchange.com/Microsoft/Development/MS_Access/ADP/Q_22927092.html)
Daniel's suggestion of using the Profiler was a good one so points will be given for that, helping me to move forward with the problem, even if the problem is solved from the other question.  Thanks for your help.
LVL 32

Expert Comment

by:Daniel Wilson
ID: 20178854
sp_fkeys ... it's asking about the foreign keys to the tables.

You probably figured that part out, though.

re: bit fields & NULL's, I usually make bit fields not nullable and default to 0.  Rarely will leaving it nullable actually be helpful ... and it makes for more null-condition checking in the client code.  Not sure whether that actually is hurting here, though.

Author Comment

ID: 20227213
I'm still struggling with this issue if anyone can please help me...
I've worked with it a little more and here's more information:

The form is looking at a VIEW which joins only two tables.  One is a CROP table which contains general information about a crop such as it's name and storage temperature. The other is an AVAILABILITY table which contains daily-updated information about the current inventory of the crops.  These tables both use an alphanumeric code called CROP_ID as their primary key, and they are joined on this field in the view.

The view is returning accurate data with no errors.  Each table works independently alone - crops can be added, deleted, and modified in the crop table and the availability table is successfully updated each day (based on other tables - incoming and outgoing information).  The only problem is that the checkbox on the form is calling this sp_fkeys instead of sp_executesql when we want to change its value.  

I've updated the table so that there are no null values for the bit field, it is using 0 as a default.  I've also tried recreating the form in case there was just something funky there, but that had no effect.  If anyone can help me to think about why it might be wanting the foreign key, I would appreciate it.  I have two questions open on this same topic and I'm happy to give away all the points!!  :-)

Author Comment

ID: 20282440
One more observation that may or may not be related.  When I look at the tables in the SQL Database through the ADP, I have noticed that there are some tables I can open and some that I cannot.   FOr these two tables that are involved with this problem, I can open and design tblAvailability in the ADP but I can neither open nor design tblCrops in the ADP.  Even though tblCrops has more data in it (2500-2600 rows which still isn't huge) I don't understand why I can't open it in design view in the ADP.  Could this anomaly be somehow related to this problem?  Or is there some other reason this happens?

Thanks again for your help!

Author Comment

ID: 20766643
A lot of time has passed without any new comments, and I still haven't solved the problem.  At some point it will happen but it has become lower priority at this point in time.  I am closing this question at this time but will give points for the useful suggestions I received on how to track down and research bugs of this sort.  Thanks very much for your time.

Author Closing Comment

ID: 31408145
This question wasn't solved but I need to close it.  Thanks very much for your help.
LVL 38

Expert Comment

by:Jim P.
ID: 20894030
Just a quick comment: What is the field type -- Bit? I've run into problems with VBA and SQL because VBA uses 0 and -1, The SQL Bit data type can only take 0 and 1.

So if you are feeding the VBA True (-1) to a Bit data field (+1) it wil throw an error.

Try changing the SQL data type to smallint which is the smallest numeric field that can handle a negative number.

bigint: Has a length of 8 bytes and stores numbers from 2^63 
(-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). 
integer or int:  Has a length of 4 bytes, and stores numbers from
-2,147,483,648 through 2,147,483,647. 
smallint: Has a length of 2 bytes, and stores numbers from -32,768
through 32,767. 
tinyint: Has a length of 1 byte, and stores numbers from 0 through 255. 
bit: An integer data type that can take a value of 1, 0, or NULL.

Open in new window


Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question