Solved

Using parameters in SQL passthrough query

Posted on 1998-10-21
1
379 Views
Last Modified: 2011-09-20
I'm using Access 97 and usign a SQL pass through query to pass a paramter - works fine in normal access query, but not in a sql passthrough query.  [Office] is the parameter in the where clause of a select query, which is used by an Access INSERT query

WHERE substring(tblCurrencySeries.CurrencyCode,1,2) =  [Office]

Doesn't seem to work and it doesn't allow me to set the value of the parameter in code
0
Comment
Question by:tomnich
1 Comment
 
LVL 7

Accepted Solution

by:
spiridonov earned 100 total points
Comment Utility
You  can't pass parameters to pass-through query. However, there is a workaround- you  can modify query in VBA and then run it. Here is an example: Stored procedure with parameter executed from pas-through query.

Private Sub Pr_report(report_date as date)
Dim passSQL As QueryDef
Dim sql_txt As String
Dim stDocName As String
Dim db As Database
   
   Set db = CurrentDb()
   Set passSQL = db.QueryDefs("sub_for_configuration_report")
   sql_txt = "EXECUTE rep_configuration  '" & Format$(report_date, "mm/dd/yyyy hh:mm") &"'"
        passSQL.SQL = sql_txt
        passSQL.ReturnsRecords = True
       stDocName = "Report1"
        DoCmd.OpenReport stDocName, acPreview


0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

13 Experts available now in Live!

Get 1:1 Help Now