rsoble
asked on
Access ADP is crashing when a form checkbox is checked
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"."tblAvai
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!
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"."tblAvai
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!
ASKER
FYI I have opened another question on this topic, with a more specific title in hopes that I will get more replies. (https://www.experts-exchange.com/questions/22927092/Access-ADP-is-sending-sp-fkey-rather-than-sp-executesql.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.
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.
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.
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.
ASKER
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!! :-)
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!! :-)
ASKER
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!
Thanks again for your help!
ASKER
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.
ASKER
This question wasn't solved but I need to close it. Thanks very much for your help.
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.
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.
ASKER