0

I set a global variable in my program.

public this_is_global_var as integer

this_is_global_var=1

Then I use that variable inside my pass through query

Select * from oracle_table where id=this_is_global_var ;

But error shows "this_is_global_var: invalid identifier"

Please help.Thanks.

ktaro
  • 413
  • 1
  • 7
  • 24

2 Answers2

1

If you want to use variables in your query you have to write it as a variable:

"SELECT * FROM oracle_table WHERE id = " & this_is_global_var
Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • Thanks @Strawberryshrub.I already tried that and its working.But the problem is that my query is very long and hard to define as variable.Thats why I want to directly inject my variable to my query. – ktaro Nov 09 '18 at 08:09
  • If its to long and not very clear you can also work with the Access `QueryDef` – Strawberryshrub Nov 09 '18 at 08:14
  • @ktaro I see that you got another answer that works, but no answers included an explanation. You apparently misunderstand what a "pass through" query is. The entire purpose of the pass through query is to send the SQL statement to another server which executes the query. There is no way for the server to know about an Access variable and get its data. Both of these answers demonstrate how to substitute a **text version** of the variable **value** into the text statement--the variable name is actually never sent to the server. – C Perkins Nov 09 '18 at 14:50
  • @Strawberryshrub A QueryDef will not provide much help here, since a `DAO.QueryDef` object cannot process parameters for a pass-through query. If a QueryDef is used, it still requires injecting explicit values into the text statement, so it actually just complicates the process. However, an `ADODB.Command` object *can* accept and properly process parameters sent to a remote server, but using ADO in Access introduces its own complications. See https://stackoverflow.com/questions/24248870/calling-stored-procedure-while-passing-parameters-from-access-module-in-vba – C Perkins Nov 09 '18 at 14:59
1

You can define placeholders for variable inside the query definition and replace it before execution. 

qdfTemp.SQL = Replace(qdfMyQuery.SQL, "[this_is_global_var]", str(this_is_global_var))

and then execute temp query. Original query will be untouched.

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • This solves my problem.So far I think this is the best way.Though I needed an extra effort, because I have multiple queries. Thanks @Sergey S. – ktaro Nov 09 '18 at 09:29