?
Solved

Using parameters in SQL passthrough query

Posted on 1998-10-21
1
Medium Priority
?
408 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:
Victor Spiridonov earned 200 total points
ID: 1090706
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

569 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