I have programmed a little program, which calculates my costs and my time for my Internet connection. I have used a simple textfile to store all the values. Now 3 years later this "database" gets bigger und uncomfortable. Now I want to change my program using an Access database. I have great experience in VB, Access and SQL, but now there is a problem:
I have a query in my mdb that lists all entries (every time I connected to the internet) and uses a function x (in the modules section of the mdb), which calculates the costs on every entry. (According to different weekdays, freetime, ...) In my mdb this works great, but I want to implement this in a visual basic program (in a datagrid) ==> Something like this.
Data1.Recordsource="SELECT Date_start, Time_start, Duration, x(Date_start; Duration) FROM List"
But there is a warning: Function x() not found
Now my question: Is it possible to use my function x (no matter if this function is in the mdb or my Visual basic file), which calculates the costs for each entry.
I know I can write data into a datagrid and then browsing this function trough each entry, but a SQL-Query including this function would be faster I think. There is no other way than using a function. To rewrite this function as SqL statement would be impossible (to complex)
You definitely cannot put a function from your Visual Basic code into an SQL select statement and execute it.
I'm not sure if you can use a SELECT statement that includes a custom function that's stored in a code module in the .mdb.
One thing that might work is to make a query in your .mdb out of your SQL SELECT and call that from your VB program rather than manually supplying the SELECT statement. Chances are that since the query is stored and executed on the .mdb side and doesn't have to be interpreted by DAO/ADO that this will work. You don't know what's happening to your SELECT statement when you manually form it and it's handled on the VB-side before it gets to your .mdb.
So, I'd recommend making a new query within your .mdb using your select statement (you can enter SQL view mode when designing a query to paste in SQL...and make sure your custom function is in a module in the .mdb):
SELECT Date_start, Time_start, Duration, x(Date_start; Duration) FROM List
...and then from the VB code call the query directly with:
Data1.Recordsource="SELECT * FROM thenameofthenewquery"
...whichever one you can get away with...ideally the shorter version.
PS - Normally I test to verify before I post something if I'm not sure about it but I'm not at liberty to do that at this time...so I hope this helps or inspires!
I've tested your purposes before, because I had the same idea like you. The problem is, it doesn't work. Even if I have "thenameofthenewquery" in the mdb and set my Data1.Recordsource="thenameofthequerygoeshere"
There comes a message: RT Error 3085: Undefined function x in expression. I think, perhaps If you put a query as Datasource VB takes the whole SQL out of the mdb.
I have also tried to Generate a table with a Create Table Query and to use this generated table in this Query. But if you start this "Create Table Query" out of VB with db.Execute("thenameofthenewquery") you get the same error.
If you have any suggestions write back, if not perhaps I will find another workaround.