Results 1 to 4 of 4
  1. #1
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    11

    Records Missing in Pass-Through Query

    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.

  2. #2
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    54
    I will take a stab in the dark and guess that it perhaps is a data problem such as a particular user who was deleted possibly in a users table but still has data in other tables and perhaps there is a join with the users table...

  3. #3
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    11
    Thanks! That was pretty close to what the actual problem was. Took me far too long to track it down, which is the usual case with simple errors.

    Quote Originally Posted by Bullschmidt View Post
    I will take a stab in the dark and guess that it perhaps is a data problem such as a particular user who was deleted possibly in a users table but still has data in other tables and perhaps there is a join with the users table...

  4. #4
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    54
    You're welcome and way to go!
    J. Paul Schmidt - Freelance Web and Database Developer
    Sample Database on the Web
    Sample Access Database

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Records are missing in a query and report
    By Papa Echo in forum Queries
    Replies: 3
    Last Post: 04-14-2018, 11:42 AM
  2. Replies: 12
    Last Post: 01-06-2017, 03:36 AM
  3. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  4. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  5. Missing records when query runs
    By SydB in forum Queries
    Replies: 6
    Last Post: 06-28-2014, 01:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums