http://www.mssqltips.com/tip.asp?tip=1482

 I typically use MS Access for front-end work.  I use pass-through queries as record sources for reports, drop down boxes, etc. when it is an object that the user will not be changing.  However, I use bound forms by declaring a query that returns a single record, say like this:

SELECT mt.field1, mt.field2

FROM MyTable mt

WHERE  mt.field1 = 333

At a conference I attended many years ago, I had been lead to believe that this setup would ensure that only 1 record was returned over the network.  But if I understand your article correctly, you are saying that even when dealing with the record source for a bound form like this, Access pulls all the records from MyTable?   All those records are being transmitted across the network?  Do I understand correctly?

The biggest problem with pass-through queries (other than you can't dynamically pull a criteria/parameter from a form) is that you can't modify the return data.  So, if one wants to use a .mdb/.mde and bound forms, is there no way to get just the data we want?  How can this be done efficiently? 

Thank you,

- JJ 

You can dynamically create the query string and pass it to a proc such as this:

Public Sub PassThrough(strSQL As String)
On Error GoTo ErrHandler

    Dim obj As QueryDef
    Dim dbsCurrent As Database
    Dim qdfPassThrough As QueryDef

    Set dbsCurrent = CurrentDb()
    
    For Each obj In dbsCurrent.QueryDefs
        If obj.Name = "Q_EXEC" Then dbsCurrent.QueryDefs.Delete "Q_EXEC"
    Next

    Set qdfPassThrough = _
        dbsCurrent.CreateQueryDef("Q_EXEC")
    qdfPassThrough.Connect = _
        "ODBC;DSN=MyODBC;DATABASE=MyDatabase;Trusted_Connection=Yes"
    qdfPassThrough.SQL = strSQL
    qdfPassThrough.ReturnsRecords = False 'I tend to use this for running stored procedures hence the False but you could put True here
    
    With DoCmd
        .SetWarnings False
        .OpenQuery "Q_EXEC"
        .SetWarnings True
    End With

    dbsCurrent.QueryDefs.Delete "Q_EXEC"
    dbsCurrent.Close
Exit_ErrHandler:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ErrHandler
End Sub

I am attempting to move a 9 year old database from Access to SQL, still using a Access front end.  I am a novice, and am trying to read all ther material I can, but it is danting.  I under stand your presentation of using pass-through queries, but everytime I attempt to use the procedure with our Access 2007 - SQL I get errors in the code.  I changed all the dbo_ to dbo.  and double quotes to single, but I have problems with ! in combining to cloumns into one, and other strange errors(which I don't understand).  Where would you suggest I get help, is there a definitive white paper or book on Access front end to SQL backend?

Thanks Pete

I don't know of an Access/SQL server resource but I suggest using the SQL Server Books On Line as any syntax you are using in a Pass Through query has to be in a format suitable for SQL Server rather than Access.

If you have a specific query you're having difficulty with, feel free to post it and I will try to help you...

Here is my Access code:

SELECT dbo_Leave.[Request No], dbo_Leave.[Leave Entry], dbo_Employee.Status, dbo_Leave.StdLogonID, [dbo_Employee]![LName] & ", " & [dbo_Employee]![FName] AS Name, dbo_Employee.[Employee ID], dbo_Leave.Team, dbo_Leave.STDate, dbo_Leave.EndDate, dbo_Leave.From, dbo_Leave.To, dbo_Leave.PerLv, dbo_Leave.SickLv, dbo_Leave.CompLv, dbo_Leave.OthLv, [PerLv]+[SickLv]+[CompLv]+[OthLv] AS [Total Hr], dbo_Leave.LvRemarks, dbo_Leave.P3, dbo_Leave.Flex, dbo_Leave.[Auto P3], dbo_Employee.Classification, dbo_Employee.[Phone Group], dbo_Employee.[Group key], dbo_Leave.[Leave Approved], dbo_Leave.[Approved By]
FROM dbo_Employee RIGHT JOIN dbo_Leave ON dbo_Employee.StdLogonID = dbo_Leave.StdLogonID
WHERE (((dbo_Employee.Status)="Active"))
ORDER BY dbo_Leave.STDate;

I get a error because of my combining two columns into one AS Name.  Plus I had one column that used to retrieve the Computer Name of the Supervisor making the entry, however it did not convert from 2003 to 2007 or SQL, is there an equivilant which I can use to grab the Computer Name from the user?

 Thanks Pete

To denote text, use ' instead of "

SELECT L.[Request No], L.[Leave Entry], E.Status, L.StdLogonID, E.LName + ', ' + E.FName AS Name, E.[Employee ID], L.Team, L.STDate, L.EndDate, L.From, L.To, L.PerLv, L.SickLv, L.CompLv, L.OthLv, IsNull([PerLv].0) + IsNull([SickLv],0) + IsNull([CompLv],0) + IsNull([OthLv],0) AS [Total Hr], L.LvRemarks, L.P3, L.Flex, L.[Auto P3], E.Classification, E.[Phone Group], E.[Group key], L.[Leave Approved], L.[Approved By]
FROM dbo.Employee E RIGHT JOIN dbo.Leave L ON E.StdLogonID = L.StdLogonID
WHERE E.Status= 'Active'
ORDER BY L.STDate

Great Information, it's beginning to get brighter in my small work area.  However, when I run this I get an error message : Incorrect syntax near the keyword 'From' (#156).  Does this have something to do with my choice of From as a Column Title, maybe it is a reserved word?

Pete

Yes, From is a reserved word - I would try to avoid using that as a column title.

Also I have made a small typo: 

IsNull([PerLv].0)  should be IsNull([PerLv],0)  (i.e. with a comma).

See if that works...

I made the change to the SQL database dbo.Leave.From and .To  to FFrom and TTo.    However I still get and error:  Invalid Object near dbo.Employee

 SELECT L.[Request No], L.[Leave Entry], E.Status, L.StdLogonID, E.LName + ', ' +E.FName AS Name, E.[Employee ID], L.Team, L.STDate, L.EndDate, L.FFrom, L.TTo, L.PerLv, L.SickLv, L.CompLv, L.OthLv, IsNull([PerLv],0)+IsNull([SickLv],0)+IsNull([CompLv],0)+IsNull([OthLv],0) AS [Total Hr], L.LvRemarks, L.P3, L.Flex, L.[Auto P3], E.Classification, E.[Phone Group], E.[Group key], L.[Leave Approved], L.[Approved By]
FROM dbo.Employee E RIGHT JOIN dbo.Leave L ON E.StdLogonID = L.StdLogonID
WHERE E.Status='Active'
ORDER BY L.STDate

 Is the dbo.Employee E  not correct to create a shortcut for dbo.Employee?

Pete

Did you change the connection string to match your db and ODBC connection?

 qdfPassThrough.Connect = _
        "ODBC;DSN=MyDSNConnectionToDatabase;DATABASE=MyDatabase;Trusted_Connection=Yes"

Also, check that the owner of your table is in fact 'dbo'.

Try running the query string in Management Console (remembering to select the correct database to run the query in).