Pass through query question

[roberto21]

Experimenting with access and sql server, I ran into a problem. I have a query that works ok in access

SELECT PrimaNota.Ricevuta, PrimaNota.[Conto N], Pianodeiconti2.Descrizione, PrimaNota.Data, PrimaNota.Entrate, PrimaNota.Uscite, PrimaNota.Note, Pianodeiconti2.LIVELLO_1, Pianodeiconti2.LIVELLO_2, Pianodeiconti2.INDICE, PrimaNota.MESE, Pianodeiconti2.CODICE_REGIONE_AVERE, Pianodeiconti2.CODICE_REGIONE_DARE, Pianodeiconti2.RAGGRUPPAMENTO1FROM Pianodeiconti2 INNER JOIN PrimaNota ON Pianodeiconti2.[Conto N] = PrimaNota.[Conto N]
WHERE (((PrimaNota.Data) BETWEEN #31/12/1023# AND #01/01/2025#))
ORDER BY PrimaNota.Data, PrimaNota.[Conto N];

For the sake of testing and learning something, I created an identical query using the create->passThrough in access, deliberately leaving the query in access syntax (BETWEEN, dates wrapped in #) .




So, I was expecting the query to fail if executed on the server. But the query worked fine also in this case. What does this mean? That the query is executed in access anyway? And if so, what am I missing in order to have a real pass through query?

[Welshgasman]

I would have honestly expected the data to be in mm/dd/yyyy format ?
Though I do not use SQL server BE I now use

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

[roberto21]

Handling of dates is always painful. Anyhow, I am in Italy, so I use the european standard dd/mm/yyyy, In any case, this is not the problem: the query works fine in both cases, but it should fail whan handled by the server which, as far as I know, expects T-SQL syntax: Between is not recognized, dates should be enclosed in quotes and in yyyy/mm/dd format. How come does it work? I am missing something here...

[Welshgasman]

I am in the UK and use the same format, except for when I am using Access.
No idea, as I said, I have not used SQL BE.

According to ChatGPT, Between is allowed. Dates are in quotes as you stated though. Perhaps Access translates it on sending?

SELECT 
    PrimaNota.Ricevuta, 
    PrimaNota.[Conto N], 
    Pianodeiconti2.Descrizione, 
    PrimaNota.Data, 
    PrimaNota.Entrate, 
    PrimaNota.Uscite, 
    PrimaNota.Note, 
    Pianodeiconti2.LIVELLO_1, 
    Pianodeiconti2.LIVELLO_2, 
    Pianodeiconti2.INDICE, 
    PrimaNota.MESE, 
    Pianodeiconti2.CODICE_REGIONE_AVERE, 
    Pianodeiconti2.CODICE_REGIONE_DARE, 
    Pianodeiconti2.RAGGRUPPAMENTO1
FROM 
    Pianodeiconti2 
INNER JOIN 
    PrimaNota 
ON 
    Pianodeiconti2.[Conto N] = PrimaNota.[Conto N]
WHERE 
    PrimaNota.Data BETWEEN '1023-12-31' AND '2025-01-01'
ORDER BY 
    PrimaNota.Data, PrimaNota.[Conto N];

Changes:

1. Date Format:
   
   
   • Access uses #MM/DD/YYYY#, whereas SQL Server uses 'YYYY-MM-DD' for date literals.
   
   
2. Aliasing Fix:
   
   
   • Fixed RAGGRUPPAMENTO1FROM, which appears to have a typo (RAGGRUPPAMENTO1 FROM).
   
   
3. BETWEEN Clause:
   
   
   • The dates are kept as strings in 'YYYY-MM-DD' format for T-SQL compatibility.

This should work in SQL Server. Let me know if you need any refinements! ��

[Minty]

Most of my work these days is with Azure SQL backends and Access FE 's.
This query should fail SQL Server,

SELECT PrimaNota.Ricevuta, PrimaNota.[Conto N], Pianodeiconti2.Descrizione, PrimaNota.Data, PrimaNota.Entrate, PrimaNota.Uscite, PrimaNota.Note, Pianodeiconti2.LIVELLO_1, Pianodeiconti2.LIVELLO_2, Pianodeiconti2.INDICE, PrimaNota.MESE, Pianodeiconti2.CODICE_REGIONE_AVERE, Pianodeiconti2.CODICE_REGIONE_DARE, Pianodeiconti2.RAGGRUPPAMENTO1FROM Pianodeiconti2 INNER JOIN PrimaNota ON Pianodeiconti2.[Conto N] = PrimaNota.[Conto N]
WHERE (((PrimaNota.Data) BETWEEN #31/12/1023# AND #01/01/2025#)) ORDER BY PrimaNota.Data, PrimaNota.[Conto N];

as it won't understand the Date delimiters being # and won't understand the dd/mm/yyyy date format either.

I always use 'yyyy-MM-dd' format for Sql Server.
I use the function below to take normal access dates or dates as string

Function ServerDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by SQL Server and Azure SQL.

    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Adjusted from a Allen Browne function for user with SQL server. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            ServerDate = Format$(varDate, "'yyyy-mm-dd'")
        Else
            ServerDate = Format$(varDate, "'yyyy-mm-dd hh:nn:ss'")
        End If
    End If
End Function

[roberto21]

Thanks a lot for your kind interest, but maybe I was not clear. I know that the query (as amended to reflect T-SQL syntax) works fine in sql server; I can run it with SSMS without any trouble.
What I do not understand is this: I define the query in access as pass-through query, meaning (correct me if I am wrong) that the query is passed to the server "as is". BUT,
if I leave the date in the format #..#, I expect he server to complain about invalid syntax. So one might conclude that the query, in spite of the fact of being passthru, gets executed in access. Why? Or, is the conclusion wrong? That's why I think I am missing something here...

[Minty]

It shouldn't work. It doesn't get past the SQL Driver
See the below


Changing the format doesn't work either:

SELECT * FROM tb_Calendar 

WHere FullDate > #2024-12-31#

This does work

SELECT * FROM tb_Calendar 

WHere FullDate >'2024-12-31'

So to answer your question - I think there is something not right in how you are running or using the pass through?

[roberto21]

Frankly, I don't know what to say. If you look at my first post, you can see that I have a pass-through query in access, clearly identified (I circled it in red). This query when executed should go straight to the server that, because of the #..# syntax of the dates, should complain of invalid syntax. Instead the query is executed without errors. It seems very simple, and in any case I followed the steps outlined here
https://support.microsoft.com/en-us/...2-6dac62532a42
So it appears that the query is executed in access (I don't believe access modifies the query before forwarding it), but the situation is so simple that, again, I must be missing something trivial.

[June7]

What is the connection string in the query ODBC Connect Str property?

I have SQLServerExpress backend on my laptop. Connection string:
ODBC;DRIVER=SQL Server;SERVER=localhost\SQLEXPRESS01;DATABASE=Umpi resSSE;Trusted_Connection=True

This works for me: WHERE GameDate BETWEEN '2023-10-1' AND '2023-12-1'

# and / fail

[jojowhite]

what you just created is a query from your Linked table.
If you ever need to create a pass through query, you
must supply the ODBC connection in it's Property first.

[Minty]

Stupid question No1.

Did you save the query before running it?
If not, it will remain as whatever type of query it was originally, unlike Access queries they aren't updated until they are saved.

So, if your workflow was, create normal Access Query, view it, change to a pass through, Run it - nothing will have changed at that point.