Solved

Using parameters in SQL passthrough query

Posted on 1998-10-21
1
394 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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