Invoke MS Access Query which calls a VBA Function in a Module from Delphi ADO Component
I created a function in an MS Access Module, let's say Calculate(A, B).
I also created a Query in MS Access to use this function, let's say:
UPDATE aTable
SET aField = Calculate(bField, cField)
How can I invoke the query, since I tried using: adoConnection.Execute, adoTable, adoQuery, adoCommand, and StoredProcedure, all reject it with the message
Undefined function Calculate in expression
Thank you in advance.
vba function delphi ms-access delphi-2010
edited Mar 8 at 17:22
marc_s
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
add a comment |
I created a function in an MS Access Module, let's say Calculate(A, B).
I also created a Query in MS Access to use this function, let's say:
UPDATE aTable
SET aField = Calculate(bField, cField)
How can I invoke the query, since I tried using: adoConnection.Execute, adoTable, adoQuery, adoCommand, and StoredProcedure, all reject it with the message
Undefined function Calculate in expression
Thank you in advance.
vba function delphi ms-access delphi-2010
edited Mar 8 at 17:22
marc_s
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
1
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09
add a comment |
I created a function in an MS Access Module, let's say Calculate(A, B).
I also created a Query in MS Access to use this function, let's say:
UPDATE aTable
SET aField = Calculate(bField, cField)
How can I invoke the query, since I tried using: adoConnection.Execute, adoTable, adoQuery, adoCommand, and StoredProcedure, all reject it with the message
Undefined function Calculate in expression
Thank you in advance.
vba function delphi ms-access delphi-2010
edited Mar 8 at 17:22
marc_s
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
I created a function in an MS Access Module, let's say Calculate(A, B).
I also created a Query in MS Access to use this function, let's say:
UPDATE aTable
SET aField = Calculate(bField, cField)
How can I invoke the query, since I tried using: adoConnection.Execute, adoTable, adoQuery, adoCommand, and StoredProcedure, all reject it with the message
Undefined function Calculate in expression
Thank you in advance.
vba function delphi ms-access delphi-2010
vba function delphi ms-access delphi-2010
edited Mar 8 at 17:22
marc_s
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
edited Mar 8 at 17:22
marc_s
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
edited Mar 8 at 17:22
marc_s
583k13011241270
edited Mar 8 at 17:22
marc_s
583k13011241270
edited Mar 8 at 17:22
marc_s
583k13011241270
583k13011241270
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
asked Nov 26 '18 at 10:12
Wisnu WidiartaWisnu Widiarta
613
613
1
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09
add a comment |
1
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09
1
1
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09
add a comment |
1 Answer
1
active
oldest
votes
You can't.
Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.
Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.
They're not available through ODBC or OLEDB, thus not available in ADO.
If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53478882%2finvoke-ms-access-query-which-calls-a-vba-function-in-a-module-from-delphi-ado-co%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can't.
Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.
Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.
They're not available through ODBC or OLEDB, thus not available in ADO.
If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
add a comment |
You can't.
Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.
Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.
They're not available through ODBC or OLEDB, thus not available in ADO.
If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
add a comment |
You can't.
Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.
Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.
They're not available through ODBC or OLEDB, thus not available in ADO.
If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
You can't.
Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.
Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.
They're not available through ODBC or OLEDB, thus not available in ADO.
If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
answered Nov 26 '18 at 10:29
Erik AErik A
20.1k62441
20.1k62441
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
add a comment |
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
Ok. Actually I can do the calculation from delphi by iterating each record. But it will take eons to complete due to huge data. I tried to do the calculation in SQL clause, but it is too complex to implement in a sql query. Actually the function will calculate a short string in a field with format hh:mm:ss and convert it in total seconds. 00:01:01 will return 61, and so on. After that I need to put a value based on the seconds. I need to compare two fields with that format, and put in another field an integer mark. If a < b then c is 1, if a < b * 1.1 then c is 2, and so on.
– Wisnu Widiarta
Nov 27 '18 at 4:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53478882%2finvoke-ms-access-query-which-calls-a-vba-function-in-a-module-from-delphi-ado-co%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Thank you @Lee Mac for your edit. I thought there was an AI fixing automatically. LOL.
– Wisnu Widiarta
Nov 27 '18 at 4:09