13 Replies - 344 Views - Last Post: 21 May 2019 - 12:28 AM Rate Topic: -----

#1 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

User defined function in Datatable source

Posted 20 May 2019 - 05:58 AM

Hello,
I used to have a SQL (Access database) query like:
"SELECT Field1,MyFunction(Field2,Field3)"


where MyFunction was a public function declared in a module like for instance a simple multiplication:
Function MyMultiply(Arg1,Arg2) as integer
return(Arg1 * Arg2)
End Function


For instance, if Table1 contains:
1____1____2
2____2____3
3____3____4


Then, the result of the query will be:
1____2
2____6
3____12



This worked perfectly in vba Access.Now, I have to do this under VB.NET:
Dim ds As New DataSet
Dim sSQL as string="SELECT Field1,MyFunction(Field2,Field3)"
Dim cmd As New OleDbCommand(sSQL, _myOLEdbConnection)
Dim da As New OleDbDataAdapter(cmd)

da.Fill(ds, "temp")
Dim SQLSelect As New DataTable
SQLSelect = ds.Tables("temp")


It seems not working (Undefined function 'FormatHEXVALUE' in expression) )
Any idea?

Thanks a lot

Is This A Good Question/Topic? 0
  • +

Replies To: User defined function in Datatable source

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6798
  • View blog
  • Posts: 28,097
  • Joined: 12-December 12

Re: User defined function in Datatable source

Posted 20 May 2019 - 06:17 AM

The following is probably relevant

Quote

User Defined Functions in Queries Microsoft Access queries support the use of functions defined in VBA modules to process data passed to them. Queries can be standalone queries, SQL statements in form/report record sources, data sources of combo boxes and list boxes on forms, reports and table fields, and default or validation rule expressions. SQL Server cannot run these user defined functions. You may need to manually redesign these functions and convert them to stored procedures on SQL Server.


Migrate an Access database to SQL Server

Although this refers to SQL Server I think the same restriction applies to attempting to execute Access SQL statements using OleDb. It is the Access Application that is able to handle VBA functions in SQL statements.

I suppose you could get around it by automating Access to execute the query. Or migrate to SQL Server or another database.
Was This Post Helpful? 1
  • +
  • -

#3 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 20 May 2019 - 07:16 AM

Thanks for your answer but the application shall handle local mdb files, and automation is not an option.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: User defined function in Datatable source

Posted 20 May 2019 - 07:21 AM

This is still connecting to Access?

Use a try/catch and post the full exception message.
Was This Post Helpful? 0
  • +
  • -

#5 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:05 AM

View Postmodi123_1, on 20 May 2019 - 07:21 AM, said:

This is still connecting to Access?

Use a try/catch and post the full exception message.


To be more accurate, the exception raises
Undefined function 'MyFunction' in expression when reaching 6th line
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:18 AM

As it is, yeah.. a VBA function is hard to get a handle to. You would be better off just taking what ever that function is and writing it in VB.NET
Was This Post Helpful? 0
  • +
  • -

#7 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:22 AM

View Postmodi123_1, on 20 May 2019 - 08:18 AM, said:

As it is, yeah.. a VBA function is hard to get a handle to. You would be better off just taking what ever that function is and writing it in VB.NET


The function is NOT a vba one, it's a public function VB.NET in the same project!
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:26 AM

Okay.. then it most definitely won't be used in your SQL query from the Access database.

.. because Access "doesn't know" it exists.
Was This Post Helpful? 0
  • +
  • -

#9 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:31 AM

View Postmodi123_1, on 20 May 2019 - 08:26 AM, said:

Okay.. then it most definitely won't be used in your SQL query from the Access database.

.. because Access "doesn't know" it exists.


Maybe my wordings were not clear. The SQL is built in VB.NET and the function as well. And I am reading "standalone" mdb files
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:33 AM

Your SQL string is executed in the Access database - via the command and adapter objects. There is no way for the Access DB to know about some function inside in the VB.NET project.
Was This Post Helpful? 0
  • +
  • -

#11 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 20 May 2019 - 08:36 AM

Copy. Thanks
Was This Post Helpful? 0
  • +
  • -

#12 DarenR   User is offline

  • D.I.C Lover

Reputation: 617
  • View blog
  • Posts: 4,108
  • Joined: 12-January 10

Re: User defined function in Datatable source

Posted 20 May 2019 - 10:18 AM

your best bet is probably to either created a stored procedure to run that query, pull in all the data into a list and then run the function, or do what everyone else does and get rid of access and use sql
Was This Post Helpful? 0
  • +
  • -

#13 Sheepings   User is offline

  • D.I.C Lover
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,260
  • Joined: 05-December 13

Re: User defined function in Datatable source

Posted 20 May 2019 - 01:28 PM

Hmm As pointed out on post 10, you'd need to get the info you need first from your DB by your select statement, add it to some kind of collection, or as you receive it, execute whatever function you want to execute with the result of the executed select command. Do you know how to return a select statements result? If so, return the result from your query, pass the received variables to a function to execute your calculations.
Was This Post Helpful? 0
  • +
  • -

#14 oligo92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 29-September 17

Re: User defined function in Datatable source

Posted 21 May 2019 - 12:28 AM

View PostSheepings, on 20 May 2019 - 01:28 PM, said:

Hmm As pointed out on post 10, you'd need to get the info you need first from your DB by your select statement, add it to some kind of collection, or as you receive it, execute whatever function you want to execute with the result of the executed select command. Do you know how to return a select statements result? If so, return the result from your query, pass the received variables to a function to execute your calculations.


Yeah, I managed to do this by "post-process" every record...This works, a little bit heavier, but is works...

Anyway, thanks to everyone
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1