Microsoft Access Pass Through Queries to SQL Server

By:   |   Comments (31)   |   Related: > Microsoft Access Integration


Problem

Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name (DSN) using the OLEDB driver, created a System DSN for the new SNAC (SQL Native Client) driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment! There has to be a better way!

Solution

Of course there is. Microsoft Access gives you the ability to issue the query created in Access directly against the SQL Server database, using T-SQL commands. This is called a Pass-Through Query in Microsoft Access. By the time you have finished reading this tip you will know precisely how to create one.

If you have been following along with this tip series on Microsoft Access and SQL Server integration then you should have an Access database with the following linked tables to an instance on your SQL Server for the 2005 Northwind database. The 'Tables' listing should look something like what you see below.

8

Let's take a minute to create a basic Access query against the dbo_Orders, dbo_Customers, and dbo_Employees tables first. Select 'Queries' under the 'Objects' menu on the left side of the main Access interface. You should see a form similar to what is displayed below. Select the 'Create query in Design view' option on the right pane of the window.

1

Once the 'Show Table' interface displays, then select the three tables: dbo_Orders, dbo_Customers, and dbo_Employees. Next, click the 'Add' button to begin designing the query.

2

What you will see next is a query screen that looks something like what is presented below. I have altered the tables' graphical placement and size for presentation purposes, but your screen should be similar. The Access Query Designer is very similar to the one you find incorporated into SQL Server.

3

Double-click the fields as follows in order to load them into the query:

dbo_Orders

  • OrderID
  • ShipCountry

dbo_Customers

  • CompanyName

dbo_Employees

  • LastName
  • FirstName

Select to sort the results by 'Company Name' and to limit the results to only those where the Ship Country is 'USA'. The Access Query Designer should look something like this:

4

Now if you run the query as-is (via either selecting Query | Run from the menu bar or by clicking on the 5 button the following results are displayed.

6

So, you may ask, "What is wrong with this query? Well, it all comes down to what I mentioned earlier: Access will load the entire data set prior to filtering the data and returning the results. Using our example, this means that the complete Orders, Customers, and Employees tables will be returned from SQL Server into Access. Afterwards, the tables will be joined and the query's WHERE clause will be applied, limiting the results to only those records where Orders.ShipCountry = "USA". Furthermore, Access is doing all the processing. All the processing power of your SQL server is disregarded - the client workstation where Access is being run is bearing the brunt of all processing.

Microsoft Access Pass-Through Queries

This is where the Pass-Through query comes into play. Let's first take a look at how to convert this query into a pass-through query first and then I will explain what this accomplishes.

To convert the query into a pass-through query, select Query | SQL Specific | Pass-Through from the menu bar. In doing so you will see that the query view switches to SQL text. Once you convert the query into a pass-through query, design view is not available. This should be familiar ground though for the experienced or novice SQL Server DBA.

7
7 1

The query text does look a little different from what you're accustomed to. Try running it and see what happens:

7 2

So, what is wrong? Well if you convert an existing Access query into a pass-through query, it will use the proprietary Access SQL language, not T-SQL. This is an issue, since we are "passing" the query back to the SQL Server for parsing and processing. This means that the query must be written in the T-SQL language. Therefore you have two options: convert the SQL Server code to T-SQL or create the query as a pass-through query from the start.

  • Converting the SQL Server code from Access to T-SQL typically comes down to replacing the underscore from the table name with a period (dbo_Orders in our example will become dbo.Orders) and replacing double-quotes, signifying a text value, to single-quotes.
  • Creating the query as a pass-through from inception simply means that you select Query | SQL Specific | Pass-Through from the menu bar prior to adding tables to the query while in design view.

What I tend to do is build the query I want to run in SQL Server Management Studio first. I then paste the query text into the Access pass-through query. This resolves two dilemmas: it allows reviewing the estimated execution plan, and it confirms that the query will run successfully. Below I've done just that. I've converted the existing query into T-SQL.

7 3

The results are the same, but I've accomplished two things. I've forced the query to execute on the SQL Server, not the client workstation. I've also eliminated the Access default process of loading the complete record set into the workstation prior to processing the query. I can now save this query and use it as the record source for an Access form or report. Essentially, I can use this query as if it is native to the Access database.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, April 23, 2018 - 7:54:34 AM - Greg Robidoux Back To Top (75759)

 

 Hi Michele,

I think this all comes down to comfort and training.  I think the use of Access is probably used because that is what users were probably trained to use and therefore that was the direction.  Access does give you the ability to use a graphical tool to build queries, so probably another reason why. Another possibility of using Access is to limit which tables people can see and access.

If it were me I would write queries directly using SQL Server Mangement Studio and not use Access, but that's because that is what I prefer.  Also, there are somethings you can do in SSMS that you cannot do in Access.  You can look at query plans to see if the query is efficient and tune accordingly.  You can look at IO and TIME statistics to see what the query is using for resources, again another way to make sure the query is efficient.   You can make use of temp tables and build indexes on the temporary tables if it make sense to break up the query to run faster.  You can write code with loops in SSMS, so basically you have a lot more options writing code in SSMS instead of running a pass through query.

Hope this helps.

-Greg

 

Saturday, April 21, 2018 - 10:01:14 PM - Michele Back To Top (75751)

Hi there,

I am looking for advice from an expert regarding a situation I am facing in my current job that is related to (what I feel) are database limitations.  I just started a new job but in the job I came from, I was heavily involved in writing queries in Oracle via SQLDeveloper and writing queries using SQL Server.  In this new job however, the employees mostly use Access db (bleh) and so they have limited me to accessing the tables on SQL Server by using the pass-through feature in Access.

As you can imagine, I am not too happy but I am dealing with it.  

Here is my question -

Given that my position title is responsible for developing queries for data extraction requests to support an org of over 2K employees, how the hec would using a pass-through feature in Access db be better than accessing SQL Server directly?  

I need a good argument with respect to efficiency and backend processing flow. Honestly - I really just want to say, 'this sucks people and makes no sense' but I need a technical explanation that sounds solid and will pass the message of DUH!

 I am reallllly hoping someone can help me.  I am still a novice in writing queries but I really enjoy writing them because I love logic and data.

 

Thank you in advance,

Michele

 

 

Monday, April 24, 2017 - 2:12:54 PM - Ryan Back To Top (55147)

Another terrible thing Access does, is it will discard the where clause, or at least important parts of it if

 1. Your where clause is a function.  e.g. GetCurrentCustomerID()

 2. You are joining any table of one data source, to any table of another data source (Local + sql) (SqlDSN1 + SqlDSN2)

    2B.  This is especially painful if you are creating a report that first loads a bunch of data from SQL into a local temporary table.  

    2C.  It also loves to use sp_PrepareRecordset, then for each row Sp_Execute...@params... so if I am loading 10,000 rows; it runs 10,000 queries.

 

   I've worked around problem one by moving from a function for my various shared 'current record' lookups to use a hidden form [Forms].[AlwaysLoadedAndHidden].[CurrentCustomerID].   Very lame, but it gets around problem #1. 

   To work around problem #2, another lame hack but it had to be done, create views in SQL1 that point to all the other tables in all the other SQL servers / databases.  This then resolves problem #2, unless of course you are linking a local table and remote table.

 

 

Monday, March 16, 2015 - 4:40:27 PM - tom kosel Back To Top (36548)

I think your tip helped me, but don't know for sure.  I followed your instructions but get an error when I run the pass through query.

SQL is below

SELECT Dbo.IPMaster.IPKey, Dbo.IPMaster.PartNumber, Dbo.IPMaster.PartName, Dbo.IPMaster.OP, Dbo.IPMeasurementsChild.SessionNumber, Dbo.IPMeasurementsChild.StartDate, Dbo.IPMeasurementsChild.StartedBy, Dbo.IPFeatureChild.FeatureID, Dbo.IPFeatureChild.ItemNumber, Dbo.IPFeatureChild.PrintLocation, Dbo.IPFeatureChild.FeatureDescription, Dbo.IPFeatureChild.ToleranceFlag, Dbo.IPFeatureChild.Nominal, Dbo.IPFeatureChild.UpperTolerance, Dbo.IPFeatureChild.LowerTolerance, Dbo.IPFeatureChild.DevNominal, Dbo.IPFeatureChild.DevUpper, Dbo.IPFeatureChild.DevLower, Dbo.IPFeatureChild.Frequency, Dbo.IPFeatureChild.Gauge, Dbo.IPMeasurementsChild.IPKey, Dbo.IPMeasurementsChild.MachinistClockNumber, Dbo.IPMeasurementsChild.JobNumber, Dbo.IPMeasurementsChild.LotNumber, Dbo.IPMeasurementsChild.WorkCenter, Dbo.IPMeasurementsChild.DateTimeEntered, Dbo.IPMeasurementsChild.Measurement, Dbo.IPMeasurementsChild.PercentOfTol, Dbo.IPMeasurementsChild.Complete, Dbo.IPMeasurementsChild.GageID, Dbo.IPMeasurementsChild.PassFail, Dbo.IPFeatureChild.Attribute_Variable, Dbo.IPFeatureChild.ToolMap, Dbo.IPMeasurementsChild.OutOfToleranceNote, Dbo.IPMeasurementsChild.ID, Dbo.IPFeatureChild.KeyType, Dbo.IPFeatureChild.BalloonNumber, Dbo.IPMaster.Notes, Dbo.IPMeasurementsChild.ReferenceJobAndLotNumber, Dbo.IPMeasurementsChild.FeatureID, Dbo.IPMaster.mfgRev, Dbo.IPMaster.IPRev, Dbo.IPMaster.CustomerRev, Dbo.IPMaster.CPRev
FROM Dbo.IPMaster INNER JOIN (Dbo.IPFeatureChild LEFT JOIN Dbo.IPMeasurementsChild ON Dbo.IPFeatureChild.FeatureID = Dbo.IPMeasurementsChild.FeatureID) ON Dbo.IPMaster.IPKey = Dbo.IPFeatureChild.IPKey
WHERE (((Dbo.IPMaster.PartNumber)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![PartNumber]) AND ((Dbo.IPMaster.PartName)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![PartName]) AND ((Dbo.IPMaster.OP)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![Operation]) AND ((Dbo.IPMeasurementsChild.SessionNumber)=[Forms]![WorkingIPSummaryListForWorkOrderNumberSelection]![SessionNumber]) AND ((Dbo.IPMeasurementsChild.StartDate)=[Forms]![WorkingIPSummaryListForWorkOrderNumberSelection]![StartDate]) AND ((Dbo.IPMeasurementsChild.StartedBy)=[Forms]![WorkingIPSummaryListForWorkOrderNumberSelection]![StartedBy]) AND ((Dbo.IPMeasurementsChild.JobNumber)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![JobLotNumber]) AND ((Dbo.IPMeasurementsChild.WorkCenter)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![WorkCenter]) AND ((Dbo.IPMaster.IPRev)=[Forms]![8 - MachinistSelectIPToRunFormByWorkOrderNumber]![LatestIPRev]))
ORDER BY Dbo.IPFeatureChild.ItemNumber;

Tuesday, December 16, 2014 - 4:15:50 PM - Bob McKissick Back To Top (35624)

Hi,

 

I am not sure you can do this in access...maybe someone with a lot of coding experience...but sometimes in queries you need to query one table twice against another table.  I can do this in two steps in two pass through queries...but wondering if possible in one pass through query.

We have a workorder table and a sku attribute table.  The work order table has the item being made (invid) and the bom item (Wip-Sku) that it will be using to make this.  We don't have a bill of material system so use this lazy way of identifying what items make another item.

So...would like to link the Sku Attrib table to the Work Order table twice...once joining for attributes of the work order invid and another linking for attributes against the Wip-Sku.

When I do this in design mode...access throws a _1 or _2 after the duplicated table in the query...but this obviously won't work with direct SQL bypassing access code.

Anyway..can't find anything on line about this directly...and am just wondering if possible..

Thanks much,

Bob McKissick

Friday, November 21, 2014 - 3:45:48 AM - Nicole perez Back To Top (35364)

*** NOTE *** -***How to connect table into form using ms access 2010?how about the codes?

Sunday, May 19, 2013 - 8:36:37 AM - Tom Back To Top (24034)

Hi Tim,

I am having a bit of a problem with my pass-through query. It works ok but building anything on top of it proves to be a problem. Table I am connecting to is on Oracle 12. Code in a query looks as follows:

 

SELECT

                TO_ORGANIZATION,

                SUBINVENTORY_CODE,

                CREATION_DATE,

                ASN_NO,

                BOX_ID,

                PART_NUMBER,

                STATUS_DETAIL,

                SERIAL_NUMBER

 

FROM

                APPS.********************

 

WHERE

                ( ASN_NO IS NOT NULL ) AND

                ( CREATION_DATE > '01-JAN-2010' )

 

Idea is to INNER JOIN the above with small table I have on Access stored locally.

tables name is

 

Search_Table_ASNs.ASNs

 

to connect/join  with "ASN_NO"

 

Tried few things alrerady but all I am getting is ""ORA-00942: table or view does not exist" 

Would you let me know what else I can do and point me out in right direction

Thank you

Tom

Tuesday, January 1, 2013 - 8:27:46 AM - Colin Back To Top (21216)

Hi Tim

I have recently moved my Back end MS access database to Sql Server. I have successfully created a Pass through Query to a stored procedure on my SQL Server. I have been able to attach the query to a form successfully. From what i gather a pass through query is not updatable. Can you please advise how i can create an Updatable ( update, edit or select) query, from an access form, while using a stored procedure on SQL? To be clear i simply want to achieve better speed by transfering my regular MS Access query to a stored procedure on SQL Server. My main concern is how to update or edit etc? thanks

Friday, December 28, 2012 - 1:46:07 AM - Artyom Back To Top (21165)

Thank you very much Tim!

Strangely, but I had been looking for the solution of the problem for a long-long period and couldn't find (may I was using wrong words for the queries :)). My idea was to use stored procedures, which is much more complicated than pass through queries - of course if I were more carefull when studing Access, I should have known about the solution or at least I would think about it.

Anyway, thank you very much.

 

Best regards!

Wednesday, August 22, 2012 - 1:53:50 PM - Dmitry Back To Top (19173)

http://dbconvert.com/convert-access-to-mssql-pro.php is able to automate the process of converting MS Access queries to MS SQL views

Friday, August 17, 2012 - 4:30:29 AM - whistler Back To Top (19092)

Also, have you actually set up DSNs on your computer via Data Sources (ODBC) in Administrative Tools?

Look out for the fact that Windows stores 64 bit ones and 32 bit ones in different places.

Friday, August 17, 2012 - 4:24:57 AM - whistler Back To Top (19091)

Dynamically create your query and execute it through a subroutine. The datasource is already specified so you just need to send it a query in the form of text for it to execute. 


 
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

 

 

Thursday, August 16, 2012 - 12:45:44 PM - Maria Back To Top (19075)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text

same question Answer is appreciated

Hello,

Whenever I attempt to execute T-SQL via a pass-through query, I'm prompted to select a data source.  Is there a way to avoid this (ex: have Access recognize the DSN instead of selecting it each time?)

Thanks in advance.

 

-Mark



editor like NotePad before copying the code below to remove the SSMS formatting.

Tuesday, July 31, 2012 - 1:14:00 PM - Mark Hensley Jr. Back To Top (18861)

Hello,

Whenever I attempt to execute T-SQL via a pass-through query, I'm prompted to select a data source.  Is there a way to avoid this (ex: have Access recognize the DSN instead of selecting it each time?)

Thanks in advance.

 

-Mark

Friday, July 20, 2012 - 2:40:30 PM - Craig Yellick Back To Top (18717)

 

IIF( ) is comparable to the CASE..END operator.  IIF(X=123. 1. 0) would be:

 

   case when X = 123 then 1 else 0 end

 

NZ() is comparable to the ISNULL( ) function. NZ(Phone) would be ISNULL(Phone, '')

 

-- Craig

Friday, July 20, 2012 - 11:21:49 AM - Vlad Mikhaylov Back To Top (18711)

Thank for your help do you know if ther any way to substitute Iff and NZ funictions in T SQL?

Tuesday, July 17, 2012 - 9:56:34 AM - whistler Back To Top (18574)

I'm not sure I understand your FROM statement. Should there be a subquery here?

A subquery should be used like this:

 

SELECT A.MyValue, A.SubID, DRV.MySubqueryValue

FROM MyTable A INNER JOIN

(SELECT ID, MySubQueryValue FROM MyOtherTable) DRV ON A.SubID = DRV.ID

 

A subquery needs a SELECT statement and an alias (in this case DRV). Also are you connecting to SQL server? IIF and NZ are not SQL Server functions. Also you have some exclamation marks and double quotes which are not valid syntax in T-SQL.

Monday, July 16, 2012 - 9:35:29 PM - Vlad Mikhaylov Back To Top (18549)

Greate explenation, with the print screen, thatnks so much for the posintg. I am trying to modify my query as during the financial close it takes way too much time to run for sertain line items when it is especially need it on the fly. I have deleted all underscores and doulble quotatino but it still returns some errors ODBC-call Falled.... FROM keyword  not found where expected (#923). Would you mind to skim through  to see if anything is obivious to you that also needs to be changed?  (besides underscore and double quotation I need to delete, or where I might make the mistake?) Thanks a lot

 

SELECT DISTINCTROW GL_GL_JE_LINES.REFERENCE_1 AS [Vendor Name], GL_GL_CODE_COMBINATIONS.SEGMENT1, GL_GL_CODE_COMBINATIONS.SEGMENT2, GL_GL_CODE_COMBINATIONS.SEGMENT3, IIf([GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3]="M&S",[GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3],IIf([GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3]="GVL",[GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3])) AS Division, GL_GL_JE_HEADERS.PERIOD_NAME, APPS_FND_FLEX_VALUES_VL.DESCRIPTION, GL_GL_CODE_COMBINATIONS.ATTRIBUTE1, GL_GL_CODE_COMBINATIONS.ATTRIBUTE2, GL_GL_CODE_COMBINATIONS.ATTRIBUTE3, GL_GL_CODE_COMBINATIONS.ATTRIBUTE4, GL_GL_JE_HEADERS.JE_SOURCE, GL_GL_JE_HEADERS.JE_CATEGORY, GL_GL_JE_LINES.DESCRIPTION, Sum(Nz(GL_GL_JE_LINES!ENTERED_DR)-Nz(GL_GL_JE_LINES!ENTERED_CR)) AS Amt, GL_GL_JE_HEADERS.POSTED_DATE, GL_GL_JE_LINES.REFERENCE_5 AS [Invoice#], GL_GL_JE_LINES.REFERENCE_6 AS Source, GL_GL_JE_LINES.SET_OF_BOOKS_ID INTO [MTD and YTD Table]
FROM (GL_GL_JE_HEADERS INNER JOIN GL_GL_JE_LINES ON GL_GL_JE_HEADERS.JE_HEADER_ID = GL_GL_JE_LINES.JE_HEADER_ID) INNER JOIN (GL_GL_CODE_COMBINATIONS LEFT JOIN APPS_FND_FLEX_VALUES_VL ON GL_GL_CODE_COMBINATIONS.SEGMENT2 = APPS_FND_FLEX_VALUES_VL.FLEX_VALUE) ON GL_GL_JE_LINES.CODE_COMBINATION_ID = GL_GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
GROUP BY GL_GL_JE_LINES.REFERENCE_1, GL_GL_CODE_COMBINATIONS.SEGMENT1, GL_GL_CODE_COMBINATIONS.SEGMENT2, GL_GL_CODE_COMBINATIONS.SEGMENT3, IIf([GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3]="M&S",[GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3],IIf([GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3]="GVL",[GL_GL_CODE_COMBINATIONS].[ATTRIBUTE3])), GL_GL_JE_HEADERS.PERIOD_NAME, APPS_FND_FLEX_VALUES_VL.DESCRIPTION, GL_GL_CODE_COMBINATIONS.ATTRIBUTE1, GL_GL_CODE_COMBINATIONS.ATTRIBUTE2, GL_GL_CODE_COMBINATIONS.ATTRIBUTE3, GL_GL_CODE_COMBINATIONS.ATTRIBUTE4, GL_GL_JE_HEADERS.JE_SOURCE, GL_GL_JE_HEADERS.JE_CATEGORY, GL_GL_JE_LINES.DESCRIPTION, GL_GL_JE_HEADERS.POSTED_DATE, GL_GL_JE_LINES.REFERENCE_5, GL_GL_JE_LINES.REFERENCE_6, GL_GL_JE_LINES.SET_OF_BOOKS_ID
HAVING (((GL_GL_JE_HEADERS.PERIOD_NAME)="JUN-12") AND ((GL_GL_CODE_COMBINATIONS.ATTRIBUTE1)<>"Revenue") AND ((GL_GL_CODE_COMBINATIONS.ATTRIBUTE2)="Other Variable Expenses (net)") AND ((GL_GL_JE_LINES.SET_OF_BOOKS_ID)=26))
ORDER BY GL_GL_CODE_COMBINATIONS.SEGMENT1, GL_GL_CODE_COMBINATIONS.SEGMENT2;

Saturday, March 31, 2012 - 6:38:22 AM - DNazareth Back To Top (16720)

Hello, I am having difficulty in executing a parameter? pass through query using Access front end and SQL server.  I am able to successfully a pass through query, and also able to run a paramter pass through query if I type the parameter in single quotes ' '.  But, I cannot make the query prompt me for a parameter. 

PARAMETERS Who Text ( 255 );
SELECT *
FROM [Engineering Items & Issues]
WHERE ((([Engineering Items & Issues].Who) Like [Who]));

The error I get is ODBC call failed: ...Incorrect Syntax near 'Text' (102)

I have tried using @who instead of [who] and I get another error: Must declare scalar variable @who.  Not sure how tor resolve this.

I can get the folling pass through query to run, but this returns all records and does not prompt me for the parameter input. 

SELECT *
FROM [Engineering Items & Issues]
WHERE ((([Engineering Items & Issues].Who) Like [Who]));

If I select a specific parameter in quotes; i.e 'DNazareth' & remove PARAMETERS Who Text (255), this runs as well; i,e

SELECT *
FROM [Engineering Items & Issues]
WHERE ((([Engineering Items & Issues].Who) ='DNazareth'));

But I need to create a parameter prompt.  Please help.  Thanks

Friday, July 22, 2011 - 2:23:33 PM - Craig Yellick Back To Top (14248)

>> When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses.

This is not true.  Run SQL Profiler and see for yourself. In fact, Access does an even better job than you'd expect. Instead of simply passing the query through to SQL Server, it breaks the process down into three parts: 1) fetch the keys that match the WHERE clause, 2) create a prepared statement that fetchs pages of results, and 3) execute the paging query on demand as the user scrolls through the results.

This article needs to be updated.

 

Monday, June 30, 2008 - 12:48:13 AM - whistler Back To Top (1321)

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).

Friday, June 27, 2008 - 10:34:06 AM - PeteT Back To Top (1277)

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

Friday, June 27, 2008 - 8:30:44 AM - whistler Back To Top (1274)

Also I have made a small typo: 

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

See if that works...

Friday, June 27, 2008 - 8:27:08 AM - whistler Back To Top (1273)

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

Friday, June 27, 2008 - 7:22:50 AM - PeteT Back To Top (1271)

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

Friday, June 27, 2008 - 6:07:40 AM - whistler Back To Top (1270)
OK, first you need to address the tables using the two part naming convention ([schema].[tablename]). ! is not used to reference table columns, use . instead. The concatenator is + instead of & 

To denote text, use ' instead of "

Don’t put a ; at the end of the query Are your PerLV/SickLv/CompLV/OthLv fields nullable? If so, you might need to use the IsNull() command, otherwise if any of the fields are null, the result will be null. You can also use an alias to make the code a bit more readable. I haven’t tested this, but give it a whirl: 

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

 

Friday, June 27, 2008 - 5:14:11 AM - PeteT Back To Top (1269)

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

Friday, June 27, 2008 - 1:03:21 AM - whistler Back To Top (1268)

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...

Thursday, June 26, 2008 - 11:21:56 AM - PeteT Back To Top (1263)

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

Friday, May 30, 2008 - 1:42:14 AM - whistler Back To Top (1060)

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

Thursday, May 15, 2008 - 9:08:17 AM - JJEugene Back To Top (998)

 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 














get free sql tips
agree to terms