Incorporating a vb function in a query

[tedsmith]

In MSaccess is is very easy to incorporate a complex function in the access module in a query.
Eg If I have a mdb table that has a list of start times in seconds since midnight and I want to display the actual time as well in another temporary column.
Select * From MyTable StartTime, ConvertSecondsToTime(StartTime) as ShowTime

The function the above statement uses is a bit too complicated to put in the Sql statement and the one that works in MSAccess is as follows:-

Function ConvertSecondsToTime(Seconds As Long) As String
'Converts the number of seconds since midnight to normal time formats for sign
Dim Hours As Double, Minutes As Single, Temp As Long
Temp = Seconds
If Temp = 0 Then ConvertSecondsToTime = "": Exit Function
If Temp > 86400 Then Temp = Temp - 86400 'after midnight
Hours = Temp / (3600)
Minutes = (Hours - Int(Hours)) * 60
'insert a leading space when hours is a single digit
ConvertSecondsToTime = Right("00" & Int(Hours), 2) & ":" & Right("00" & Int(Minutes), 2)
End Function

I have tried various combinations in a vb6 recordset criteria statement but keep getting syntax errors.

My question how do I do this - if this is possible?

Do I perhaps have to perhaps incorporate a few IIFs in the query instead or can I reference the function directly?

 

[dilettante]

I believe that MS Access couples the Jet engine to its own VBA engine for Jet expression processing instead of using the Jet Expression Service. This must be a private hook of some sort, and in any case the VB6 engine/runtime probably isn't compatible.

This coupling is probably what makes your Access VBA functions available in SQL statements.

I agree this would be a very handy thing to have, but I have never found a way to make it work. Every so often I take another whack at trying to figure out how to do this.


Or perhaps it is because the Access VBA is stored inside the MDB file itself. It might be possible to create a module with your function(s) in Access and store them in the MDB, then invoke them in SQL statements from your VB6 program with that MDB open.

Not sure if I've tried that, but it sounds familiar so maybe it doesn't work either.

 

[PWise]

i dont have vb on my machien @ work
but this should work in vb

i tried this and it worked in access
db1:
1) created this function

Function Seconds()
Seconds = DateDiff("s", Date, Now())
End Function

db2:

Dim AppAccess As New Access.Application
Set AppAccess = CreateObject("Access.Application")
AppAccess.OpenCurrentDatabase ("c:\test1.mdb")
With AppAccess.CurrentDb.OpenRecordset("select top 1 * from digits,Seconds")
Debug.Print .Fields(0).Name; .Fields(0), .Fields(1).Name, .Fields(1)

End With

 

[tedsmith]

Thanks PWise but that is the effectively the same method as I already showed that works in access but doesn't work in Vb6!

Putting the function name in the criteria string doesn't seem to work because you can get the value row by row to feed to the expression to process in the first place.

Or is merely that I haven't got the right syntax for vb6?

It can be done by having two recordsets operating side by side, one getting the value row by row and, the other displaying the processed result of the function it but this is very slow and messy but passable for a small display datagrid table

 

[dilettante]

Well you do still have the intrinsic functions of the Jet Expression Service, especially the non-Sandboxed subset unless you deactivate Sandboxing. This means that with a little finesse something as simple as what you are looking for can still be done:

Private Sub DumpSecondsTableSeconds(ByVal cnDB As ADODB.Connection)
    'Assumes cnDB is open to a Jet MDB with a table SecondsTable
    'with a Long field Seconds in it.
    Dim RS As ADODB.Recordset
    
    Set RS = cnDB.Execute( _
  "SELECT " _
& "IIf([Seconds] > 0, " _
& "Right(' ' & Format(CDate(([Seconds] Mod 86400) / 86400.0),'h:Nn:Ss'),8),'') " _
& "AS [TimeVal] FROM [SecondsTable]", _
                          , _
                          adCmdText)
    With RS
        Print .GetString(adClipString, , ", ", vbNewLine, "*null*");
        .Close
    End With
End Sub

Note that the code written is very picky. For example that 86400.0 must have the .0 to force it to be a floating point divide. You cannot use 86400! or 86400# here.
How to configure Jet 4.0 to prevent unsafe functions from running in Access 2000 and Access 2002

 

[strongm]

Shame you are not using SQL Server - you could achieve the result using the DATEADD function

 

[JoeAtWork]

Thanks PWise but that is the effectively the same method as I already showed that works in access but doesn't work in Vb6!

Are you sure your code is the same? Are you creating an instance of Access and using the OpenRecordset method to return a DAO recordset. Or are you using an ADO connection object to return an ADO recordset? I would fully expect PWise's code to work exactly the same way in VB6 as it did in his VBA environment. I would want to see your code before I would rule out his solution.

Anyways, here's my solution, I'm pretty sure I've done something like this years ago:
1. Copy your ConvertSecondsToTime function to your VB6 code
2. Open an ADO recordset retrieving just the base fields, e.g. "Select StartTime From MyTable"
3. Disconnect your recordset
4. Create a new Field object that holds strings
5. Append this field to your recordset
6. Loop through your recordset, passing in the Seconds value to your function and assigning the result to your "temporary" field
7. Assign the recordset to your datagrid

When it comes time to save changes back to the database:
8. Remove your "temporary" field
9. Reconnect your recordset
10. Update as normal

 

[strongm]

Actually, upon refelction, the DATEADD trick ought to work with an ADODB recordset ...

so:

SELECT Starttime, DATEADD('s', Starttime, 0) AS Showtime
FROM myTable

should give you the result that you want.

 

[dilettante]

True enough substituting DateAdd() works, but by itself it doesn't handle the "null string for 0" and "format padding single-digit hours with a space" requirements.

  "SELECT " _
& "IIf([Seconds] > 0, " _
& "Right(' ' & Format(DateAdd('s', [Seconds], 0),'h:Nn:Ss'),8),'') " _
& "AS [TimeVal] FROM [SecondsTable]", _

 

[strongm]

I'm not sure I'd do formatting in the query itself, not convinced it is the right place

 

[tedsmith]

With pwise's solution, wouldn't I have to have a copy of access installed ? (I haven't in the final product)

 

[JoeAtWork]

With pwise's solution, wouldn't I have to have a copy of access installed ?

Yes

 

[strongm]

ted, did any of this help at all?

 

[dilettante]

I'm not sure I'd do formatting in the query itself, not convinced it is the right place

Depends on what you're doing I guess. If you kerchunk through the Recordset instead of using data binding you could post-process the Date values there.

Even using Jet this could be in a stored procedure or prepared query though, which at least gets around "recompiling" the query every use.


I don't know of any method for exposing VB6 functions to Jet's expression service.

 

[tedsmith]

Sorry for not intermittently acknowledging responses.

The reason is that I lead a very relaxed unplanned semi retired lifestyle, spending much time our in the Australian bush well away from computers.
So I sometimes don't even turn a computer on from one week to the other and if I am away form my own computer (often) I don't have the chance to test whether suggested fixes work or not.

This infuriates some of my hyperactive friends who are enraged that I don't check my email at least 4 times a day using a range of expensive sophisticated wireless or satelite mobile devices that would cost me more than my pension in monthly usage usage fees!

"How dare you miss the pic of my pussy cat I sent you this morning?"

I'll try harder next time and check at least every Monday morning.

 

[strongm]

And we still don't know if any of this helped ...

 

[tedsmith]

Because I haven't got back to my computer with vb in it to test it!