I've developed a cash receipt system for a network of hospitals. MS Access front end to SQL Server back end. It's been running great for over three years now.
For adhoc reporting and querying, I created a "read only" MS Access database that uses pass-through queries to execute a stored procedure to retrieve data. The proc takes their Windows username in order to filter the data as follows:
- The CashReceipt table stores the SiteID for the hospital posting the receipt.
- The SiteUser table is a join table that maps users to the sites they are allowed to see data for.
The SQL in the proc is roughly this: SELECT * FROM CashReceipt cr INNER JOIN SiteUser su on cr.SiteID = su.SiteID WHERE UserID = @UserID and su.EndDate IS NULL
Again, this has been working fine for over three years.
I've been making changes because they switched to a different G/L system. In the test version of the "read only" MS Access database, we've discovered (so far) one record that is not returning from the pass-through call to the stored proc. However, if I run the proc in SSMS, passing the same username, the record shows up in the result set.
I'm sure I'm overlooking something simple, given how much time I've already spent trying to figure it out.
Any suggestions would be greatly appreciated.
Thank you.