Variable Inside the Access Pass Through Query

up vote
0
down vote

favorite

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.

asked Nov 9 at 7:55

ktaro

686

add a comment |

up vote
0
down vote

favorite

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.

asked Nov 9 at 7:55

ktaro

686

add a comment |

up vote
0
down vote

favorite

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.

asked Nov 9 at 7:55

ktaro

686

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.

vba ms-access pass-through

asked Nov 9 at 7:55

ktaro

686

asked Nov 9 at 7:55

ktaro

686

asked Nov 9 at 7:55

ktaro

686

asked Nov 9 at 7:55

ktaro

686

asked Nov 9 at 7:55

ktaro

686

add a comment |

2 Answers
2

active

oldest

votes

up vote
1
down vote

accepted

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.

answered Nov 9 at 8:21

Sergey S.

5,5601927

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 9 at 9:29

add a comment |

up vote
1
down vote

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

answered Nov 9 at 8:03

Strawberryshrub

8281215

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53221764%2fvariable-inside-the-access-pass-through-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
1
down vote

accepted

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.

answered Nov 9 at 8:21

Sergey S.

5,5601927

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 9 at 9:29

add a comment |

up vote
1
down vote

accepted

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.

answered Nov 9 at 8:21

Sergey S.

5,5601927

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 9 at 9:29

add a comment |

up vote
1
down vote

accepted

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.

answered Nov 9 at 8:21

Sergey S.

5,5601927

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.

answered Nov 9 at 8:21

Sergey S.

5,5601927

answered Nov 9 at 8:21

Sergey S.

5,5601927

answered Nov 9 at 8:21

Sergey S.

5,5601927

answered Nov 9 at 8:21

Sergey S.

5,5601927

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 9 at 9:29

add a comment |

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 9 at 9: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 9 at 9:29

add a comment |

up vote
1
down vote

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

answered Nov 9 at 8:03

Strawberryshrub

8281215

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

add a comment |

up vote
1
down vote

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

answered Nov 9 at 8:03

Strawberryshrub

8281215

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

add a comment |

up vote
1
down vote

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

answered Nov 9 at 8:03

Strawberryshrub

8281215

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

answered Nov 9 at 8:03

Strawberryshrub

8281215

answered Nov 9 at 8:03

Strawberryshrub

8281215

answered Nov 9 at 8:03

Strawberryshrub

8281215

answered Nov 9 at 8:03

Strawberryshrub

8281215

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

add a comment |

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

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 9 at 8:09

If its to long and not very clear you can also work with the Access QueryDef
– Strawberryshrub
Nov 9 at 8: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 9 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 stackoverflow.com/questions/24248870/…
– C Perkins
Nov 9 at 14:59

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xtykutl