Unanswered: Commands from front end not always executed properly by back end
I have an odd problem, and so far it's been stumping everyone. Also, this post might need to be moved to the SQL Server section - if so I apologise, but at first glance it looks like an Access issue.
Our NDC uses a database application to keep track of despatches. This is built as a SQL Server 2000 back end, Access 2000 ADP front end. Each user has a copy of the ADP file on their C: drive, and each ADP file uses the same connection credentials to communicate with the server.
Recently, one user (User1) said that when he tried to run a certain report, he was given a custom error message indicating that the report had no data for the selected date. However, one of his colleagues (User2) was able to run the report without problem.
I looked into the code that generates the report. A form prompts for a date and then uses that date along with some of the user's properties to determine where to source the data. Once the determination has been made, a series of SQL statements are generated and passed to the back end to be executed. These statements cause a holding table to be dropped and then recreated from some other views. The report is based on the holding table.
Stepping through the VBA code and watching the contents of the holding table in SQL Server, I can see that the statement to drop the holding table executes. However, when the statement to repopulate it is executed, the table is recreated but with no data. This implies a permissions issue to me, except that from the point of view of SQL Server, User1 and User2 are the same person.
When I try running the generated INSERT SQL statement directly on the SQL Server box in Query Analyser, it executes successfully and reports that x rows were affected. Yet nothing goes into the table!
I've checked the various references, versions and properties on the three PCs so far in the equation (User1's, User2's and mine [SupportUser]) - they're in the attached ZIP file on separate pages of an Excel 2000 workbook.
If anyone can cast any light on this, I will be most grateful!
10% of magic is knowing something that no-one else does. The rest is misdirection.