Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Microsoft Access Pass Through Queries to SQL Server

MSSQLTips author Tim Ford By:   |   Read Comments (25)   |   Related Tips: More > 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.

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.

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.

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.

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:

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

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.

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

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.

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



Last Update: 4/22/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, May 15, 2008 - 9:08:17 AM - JJEugene Read The Tip

 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 


Friday, May 30, 2008 - 1:42:14 AM - whistler Read The Tip

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, June 26, 2008 - 11:21:56 AM - PeteT Read The Tip

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, June 27, 2008 - 1:03:21 AM - whistler Read The Tip

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


Friday, June 27, 2008 - 5:14:11 AM - PeteT Read The Tip

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 - 6:07:40 AM - whistler Read The Tip
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 - 7:22:50 AM - PeteT Read The Tip

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 - 8:27:08 AM - whistler Read The Tip

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


Friday, June 27, 2008 - 8:30:44 AM - whistler Read The Tip

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 - 10:34:06 AM - PeteT Read The Tip

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


Monday, June 30, 2008 - 12:48:13 AM - whistler Read The Tip

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, July 22, 2011 - 2:23:33 PM - Craig Yellick Read The Tip

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

 


Saturday, March 31, 2012 - 6:38:22 AM - DNazareth Read The Tip

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


Monday, July 16, 2012 - 9:35:29 PM - Vlad Mikhaylov Read The Tip

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;


Tuesday, July 17, 2012 - 9:56:34 AM - whistler Read The Tip

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.


Friday, July 20, 2012 - 11:21:49 AM - Vlad Mikhaylov Read The Tip

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


Friday, July 20, 2012 - 2:40:30 PM - Craig Yellick Read The Tip

 

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


Tuesday, July 31, 2012 - 1:14:00 PM - Mark Hensley Jr. Read The Tip

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


Thursday, August 16, 2012 - 12:45:44 PM - Maria Read The Tip

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


Friday, August 17, 2012 - 4:24:57 AM - whistler Read The Tip

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

 

 


Friday, August 17, 2012 - 4:30:29 AM - whistler Read The Tip

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.


Wednesday, August 22, 2012 - 1:53:50 PM - Dmitry Read The Tip

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, December 28, 2012 - 1:46:07 AM - Artyom Read The Tip

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!


Tuesday, January 01, 2013 - 8:27:46 AM - Colin Read The Tip

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


Sunday, May 19, 2013 - 8:36:37 AM - Tom Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.