MS Access/OleDb: Calling custom function in SQL query
-
Hi there, we store a duration value as a Long Integer in milliseconds in our database (MS Access or MS SQL Server or others). Now I want to get that value formatted in H:mm:ss, and for the Access version I wrote a short VBA function (
Public Function FormatTime(inputValue As Long) As String) to do that. When I open Access, and run a query (e.g.SELECT ID, FormatTime([Duration]) From MyTable), it works fine. But our applications are written in C#, and the communication with the Access database is via OleDb. Now I get an error message that function FormatTime is unknown. Is it possible at all to use a custom function in MS Access via OleDb? Looking forward to your ideas. Bernhard -
Hi there, we store a duration value as a Long Integer in milliseconds in our database (MS Access or MS SQL Server or others). Now I want to get that value formatted in H:mm:ss, and for the Access version I wrote a short VBA function (
Public Function FormatTime(inputValue As Long) As String) to do that. When I open Access, and run a query (e.g.SELECT ID, FormatTime([Duration]) From MyTable), it works fine. But our applications are written in C#, and the communication with the Access database is via OleDb. Now I get an error message that function FormatTime is unknown. Is it possible at all to use a custom function in MS Access via OleDb? Looking forward to your ideas. BernhardHave you tried placing your select statement inside a MS-Access saved query ? Your C# application would then execute the saved query, not the SQL statement directly. Give it a shot.
-
Have you tried placing your select statement inside a MS-Access saved query ? Your C# application would then execute the saved query, not the SQL statement directly. Give it a shot.
Thanks for the idea. Since our application generates quite complex queries on the fly (and must be able to use other databases like SQL Server or Oracle also), this "stored procedure" approach is not usable.
-
Hi there, we store a duration value as a Long Integer in milliseconds in our database (MS Access or MS SQL Server or others). Now I want to get that value formatted in H:mm:ss, and for the Access version I wrote a short VBA function (
Public Function FormatTime(inputValue As Long) As String) to do that. When I open Access, and run a query (e.g.SELECT ID, FormatTime([Duration]) From MyTable), it works fine. But our applications are written in C#, and the communication with the Access database is via OleDb. Now I get an error message that function FormatTime is unknown. Is it possible at all to use a custom function in MS Access via OleDb? Looking forward to your ideas. Bernhardbhiller wrote:
Is it possible at all to use a custom function in MS Access via OleDb?
No. You are asking OLEDB to pass FormatTime and OLEDB does not know of this function therefore the error. You are going to have to either move the formattime into your sql statement or format the data AFTER you get the duration from the database.
Never underestimate the power of human stupidity RAH
-
bhiller wrote:
Is it possible at all to use a custom function in MS Access via OleDb?
No. You are asking OLEDB to pass FormatTime and OLEDB does not know of this function therefore the error. You are going to have to either move the formattime into your sql statement or format the data AFTER you get the duration from the database.
Never underestimate the power of human stupidity RAH
That's not correct: 1. The function is already included in the SQL statement:
SELECT ID, FormatTime([Duration]) From MyTable2. I can connect to a SQL Server with OleDb. Here, it works just fine. Hence it is not necessary for OleDb to know my custom functions. It seems to be a Microsoft Access problem... -
That's not correct: 1. The function is already included in the SQL statement:
SELECT ID, FormatTime([Duration]) From MyTable2. I can connect to a SQL Server with OleDb. Here, it works just fine. Hence it is not necessary for OleDb to know my custom functions. It seems to be a Microsoft Access problem...You are correct in that it will work with SQL Server, you can use a UDF in a SQL string as it is a TSQL construct in SQL Server. Your function in Access is a VBA function and I'd bet that will be the difference.
Never underestimate the power of human stupidity RAH
-
Hi there, we store a duration value as a Long Integer in milliseconds in our database (MS Access or MS SQL Server or others). Now I want to get that value formatted in H:mm:ss, and for the Access version I wrote a short VBA function (
Public Function FormatTime(inputValue As Long) As String) to do that. When I open Access, and run a query (e.g.SELECT ID, FormatTime([Duration]) From MyTable), it works fine. But our applications are written in C#, and the communication with the Access database is via OleDb. Now I get an error message that function FormatTime is unknown. Is it possible at all to use a custom function in MS Access via OleDb? Looking forward to your ideas. Bernhard -
Did you ever figure this one out? I am having the exact same issue. Running a query with a custom function in Access works fine, but trying to run it through C++ using OldDB fails. :(
Unfortunately, I haven't yet found a solution...