Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > Using functions in a SQL statement in VB6.0

Thread Tools Display Modes
Old 08-01-2003, 12:18 AM
Cyber1000 Cyber1000 is offline
Join Date: Jul 2003
Location: Austria
Posts: 5
Smile Using functions in a SQL statement in VB6.0

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)

I hope you can help me, thanks in advance,
Reply With Quote
Old 08-01-2003, 12:31 AM
PWNettle PWNettle is offline
Verbose Coder

Retired Moderator
* Guru *
Join Date: Dec 1999
Location: Phoenix, Arizona
Posts: 3,011

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!
Reply With Quote
Old 08-01-2003, 06:31 AM
Cyber1000 Cyber1000 is offline
Join Date: Jul 2003
Location: Austria
Posts: 5

Hey, thanks for your fast reply!!!

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.
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL error in Vb Code blong824 Database and Reporting 16 03-14-2004 06:07 PM
functions in sql statement in vb emata Database and Reporting 2 05-27-2003 11:07 AM
Problem with writing Twice Renidrag Web Programming 20 11-07-2002 03:52 PM
Creating Access Databases TomGuy Database and Reporting 8 07-10-2002 10:56 AM
Function with in SQL Statement kcwallace Database and Reporting 6 04-30-2002 09:22 AM


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..