Access ADP is crashing when a form checkbox is checked

Posted on 2007-10-12
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

    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.
    LVL 1

    Author Comment

    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
    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.

    LVL 1

    Author Comment

    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!
    LVL 1

    Author Comment

    FYI I have opened another question on this topic, with a more specific title in hopes that I will get more replies.  (
    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
    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.
    LVL 1

    Author Comment

    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!!  :-)
    LVL 1

    Author Comment

    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!
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Closing Comment

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

    Expert Comment

    by:Jim P.
    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now