Access ADP is sending sp_fkey rather than sp_executesql

Question:  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 had them run a trace with the SQLServer Profiler to show what happens when the checkbox is clicked. 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'

Here's what the trace shows when I run the code 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).

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


Here's what the VB code looks like in the Event handler, in case it helps.  I've tried commenting out everything to no effect.

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

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.  

Finally, 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!  

This is a repeat of an earlier question, just FYI to the people who helped on the original (http://www.experts-exchange.com/Microsoft/Development/MS_Access/ADP/Q_22889885.html)
Start Free Trial