Updateable Pass Through Queries

Posted on 2004-11-14
Last Modified: 2012-05-05
We recently migrated our Access 2002 backend to SQLserver and attempting to isolate performance bottlenecks. Most of the forms we provide for customers to do data entry have a "conventional" SELECT query as it's recordsource. I've been told these queries should be Pass Through queries instead but then I've also been told you can't use a Pass Through query on a data entry/edit form.

Can someone educate me here on this before we spend a lot of time doing something that might not be correct.
    1 Comment
    LVL 8

    Accepted Solution

    Pass through is read only. But quick.

    Most of the time if you use a passthrough to get data you then write a series of functions for updates and inserts.

    This is probably the best way to go with sql anyway. Nice and fast and low overhead.

    If you are looking at data entry they aren't even looking at old data though are they? Hence a passthrough is a waste of time (read only of old data). You just want to look at a nice quick ADO function for inserts....

    Quick example below for one of mine (if you are really lazy and have an identifiable naming convention I can also give you the sql code I use to autogenerate all the vba required for the insert, update, delete and passthrough rewrite modules ;):

    Function ap_iDGroupThing(LGroup_FK, ThingName) As Integer
            On Error GoTo ErrHandler
            Dim cmd1 As New ADODB.Command
            cmd1.CommandType = adCmdStoredProc
            cmd1.CommandText = "ap_iDGroupThing"
            cmd1.Parameters.Append cmd1.CreateParameter("ret", adInteger, adParamReturnValue)
            cmd1.Parameters.Append cmd1.CreateParameter("LGroup_FK", adInteger, adParamInput, , LGroup_FK)
            cmd1.Parameters.Append cmd1.CreateParameter("ThingName", adVarChar, adParamInput, 128, ThingName)
            cmd1.ActiveConnection = Util.ocon
            ap_iDGroupThing = cmd1.Parameters("ret")
            Exit Function
                Select Case Err.Number
                    Case Else
                        MsgBox "Error in ap_iDGroupThing: " & Err.Number & vbCrLf & Err.Description
                End Select
            ap_iDGroupThing = -999
            End Function


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now