[Last Call] Learn how to a build a cloud-first strategyRegister Now


Updateable Pass Through Queries

Posted on 2004-11-14
Medium Priority
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

Accepted Solution

SimonLarsen earned 750 total points
ID: 12578370
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


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

829 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