Different results-Access 2016 passthrough query vs MSSQL (1 Viewer)

Hello all -- first time this has ever happened to me:



I have an Access 2016 front end using data from a SQL Server back end. In Access, I created a passththrough query which returned 859 records. This was one record fewer than expected. So I copied the SQL code to a real SQL Server view within the SQL Server database; it returned the correct 860 records, as I expected given the various joins and conditions.



I've done a character-by-character comparison of the two SQL strings; they are identical. (Stripped out all embedded white space just to be sure.)


Has this ever happened to anyone here before?


Btw, the reason I'm not just linking to the SQL server view instead of using the passthrough: the passthrough query's SQL code is actually created on-the-fly based on conditions present at the moment. Just using the regular Access query engine rather than a passthrough query produces unacceptable performance.


Thanks for any brainstorms. It's a stumper to me!


P.S. Not pasting/uploading the SQL here because the query is over 5K characters long, referencing many tables and other views. I'm not worried about the query per se, since there's no difference between the two versions of code -- just can't figure out why there'd be a difference in the results returned.

Hi. Not sure either but how about examining what's so special about the missing record? Hopefully, it might give you some clues.

Yes, thanks DBGuy, that would be the most obvious question. Unfortunately, there's NOTHING special about the one missing, except that it's the most recently added. (When I view the raw underlying table data, it's right where it should be.) I even went back to a development version of the back end, residing on a different server, and got the same results: one missing record.

Still checking a couple of things. Will report back when (assuming if!) I come up with anything.

FLJerseyBoy said:

Yes, thanks DBGuy, that would be the most obvious question. Unfortunately, there's NOTHING special about the one missing, except that it's the most recently added. (When I view the raw underlying table data, it's right where it should be.) I even went back to a development version of the back end, residing on a different server, and got the same results: one missing record.

Still checking a couple of things. Will report back when (assuming if!) I come up with anything.

Click to expand...

Hi. Thanks for the update. Happy hunting! You could try adding test records to see if they disappear too or get skipped.

Is this a DAO or ADO "recordset" usage on the Access side of things? Or perhaps some dynamic recordsource or rowsource usage?

Can you (for test purposes) turn the query into a named query that can then be opened in datasheet view to determine how many records it can see? I understand that you have performance issues, but this is for test only to determine where the error lies. If you can make it a datasheet view and the wrong number of records comes over, can you then trigger a Refresh to see if it helps? This is admittedly clutching at some straws to try to find a difference for you that might point the way.

Thanks, Doc_Man! Unfortunately, when the pass-through query is generated I'm doing exactly that: saving it in a named query. As users tinker with the various filters and sort options available to them, that saved query's .SQL property is constantly being re-written (and saved); therefore, in a given copy of the front end, the current SQL is the most recently saved. This makes it easy to test back in the native SQL Server database should the need arise. (I actually thought I was done testing that; the process has been in use for over a year without generating any user trouble reports.)
At this point I'm suspecting it's got something to do with the fact that my login to my employer's network identifies me to the application as a developer-level user rather than a regular read-only or read-write user. As you can see, I'm ready to clutch at straws; if you think of anything else, by all means toss them my way.

So one of my original questions still applies. If you open the named query in datasheet view, does THAT give you the right or wrong record count?

But your post also leads to a different question. You see a difference in record count with an Access FE and Passthru vs. what you see as a "permanent" view local to the server. Are you logging in to the same account both times?

I don't know if it is even possible to be applicable within the bowels of SQL Server, but as it happens, network connection permissions can differ from local session permissions because the network permissions are MINIMIZED against local permissions. I.e. if the local permission is "Read/Write" but the network says "Read-Only" then you get read-only. IF that applies in any way, the question would then revolve around network permissions on your remote vs. local login. You might need your local IT guy to help you answer some part of that question because I'm not familiar enough with SQL Server to be sure of how you would test that.

However, as a simple-minded approach, through two Windows sessions - one local to the SQL server and one remote, browse to a folder where you can see the same files. Right-click on the same file through each window for Properties>>Security. Try to find the "Effective Permissions" on some files through both the local and remote sessions. If at ANY time you see a file in one window having different permissions than you have on the same file in the other window, this might be a network permissions issue.

So, your results are consistent - running the query from SSMS gives you x records and running it as a pass-through gives you x-1???

If this is a very busy application, maybe you are running into "read committed" differences?

Thanks to all who responded.


I still have no idea why this is happening. I've traced the process line-by-line in the VBA: as Pat Hartman says, copying-and-pasting the SQL code into an MSSQL view's SQL produces X when run, but produces X-1 when run as a passthrough query in Access. I don't believe I'm running into a "read committed" issues because (a) there are only 3-4 users, counting me, who are only intermittently in the system, and (b) the record I added 4 days ago still does not show up when supposedly (but not actually) fetched by the Access passthrough query.


I'm going to try a couple more things.

1. The system as a whole won't actually run this way, but I'm going to try linking directly to the SQL Server view to see if I still get X-1 from it within Access.
2. I'll also replace all data (except the key field) in my test record, one field at a time, with data from a record which IS retrieved. Maybe that'll produce the magic X, and so tell me what my test record is missing.

Hi. Thanks for the update. That sounds like a good plan to me. Good luck!

I am soooo relieved to report that I now know what the problem was. ("Was" being the key word, lol.) It had nothing at all to do with VBA or, really, with anything else I could imagine.

Short version of the critical information: as you all know, a table linked via ODBC has a Connect property which points all references to that table name to the correct back-end source database. Access queries simply use table name(s) to identify the source(s), too. The catch is: pass-through queries are very subtly different from regular Access Query Designer-built queries in one regard: like the underlying tables, pass-through queries also have a Connect property.

In my situation, we just last weekend ported the application back end (SQL Server) from a development server to a production one. I had dutifully changed all of the ODBC tables' connection strings, leaving the tables' names unchanged -- because I naively thought it would make no difference in the queries referring to those tables. And indeed, it didn't... except for the pass-through ones.

So when I tested the production version following the port, I successfully added a record to the main back-end source -- but the pass-through query behind the main form still pulled its data from the development server! (Result: X-1 records from the development DB, instead of X records from the production.)

This is such a huge relief for me. I've been doing Access VBA since version 2.0 in the mid-'90s, and had gotten so confident I "knew" how it behaved... little suspecting that my first use of pass-through queries would lob me such a surprise! I'm so relieve that I can't even feel embarrassed.

(For the record, the "pass-through queries have a Connect property" tidbit never showed up in my Web research. It was only when I compared two queries' properties in the design window that I even noticed a difference.)

Thank you all for your patience and help. Maybe this will prove useful to someone else in the future!

FLJerseyBoy said:

The catch is: pass-through queries are very subtly different from regular Access Query Designer-built queries in one regard: like the underlying tables, pass-through queries also have a Connect property.

Click to expand...

The difference between local queries and a pass through query is not in the least bit subtle or a tidbit.

A pass though query is sent verbatim directly to the server for processing and must be written in the server's SQL syntax. It doesn't matter what your tables are called locally because they have absolutely nothing to do with the query.

Thanks, Galaxiom -- but yes, I knew all that about pass-through queries. I did NOT know that they had a connection string which must be properly set to the back end, because pass-through queries (unlike regular Access queries) don't simply rely on the names assigned to tables in the Access front end.



That was the only "subtlety" to which I referred. Other than that, yes, I knew that pass-through queries differed in major ways from plain old garden-variety queries.

FLJerseyBoy said:

I am soooo relieved to report that I now know what the problem was. ("Was" being the key word, lol.) It had nothing at all to do with VBA or, really, with anything else I could imagine.

Short version of the critical information: as you all know, a table linked via ODBC has a Connect property which points all references to that table name to the correct back-end source database. Access queries simply use table name(s) to identify the source(s), too. The catch is: pass-through queries are very subtly different from regular Access Query Designer-built queries in one regard: like the underlying tables, pass-through queries also have a Connect property.

Click to expand...

Hi. Glad to hear you got it sorted out. Good luck with your project.

I've posted a relinking app several times recently that relinks ODBC tables AND the pass-through queries so they don't get forgotten. If you can't find it just ask.

Thank you very much, Pat Hartman! I actually have such an app myself, since the back-end links change periodically (from development to production server)... just never knew (or really thought much) about a need to do so for pass-through queries as well.



I did a search on your name and the various keywords involved but didn't find the specific link... was going to include it in this reply for reference by anyone else who stumbled upon this thread. Do you have a specific link or at least a specific thread topic handy?


Thanks again; very much appreciate the offer!

Here's the database.