Running mysql code in access

[Guest]

I have a mysql database that I connect Access to. I use access to generate
time cards for employees. One the class modules I have contains a function
that adds up the total hours a person worked in a day. My problem occurs
when I use a select statement to add the times together. Here is the code:

Public Function calculateDailyTotal(ByVal userid As String, ByVal
clockOutDate As String) As String
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim recAddedTime As DAO.Recordset
Dim tempVar1 As String
Dim tempVar2 As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("select total_time_per_session from
recorded_work where user_id='" & userid & "' and clock_out_date='" &
clockOutDate & "' and total_time_per_session is not null;")

tempVar2 = "00:00"
Do Until rec.EOF
tempVar1 = rec("total_time_per_session")
selectStr = "select addtime('" & tempVar2 & "', '" & tempVar1 & "')"

Set recAddedTime = db.OpenRecordset(selectStr)

tempVar2 = recAddedTime(0)
rec.MoveNext
Loop

calculateDailyTotal = tempVar2
End Function

The error message I get tells me that the function or procedure addtime
doesn't exist or is invalid. I know it work because I go to my mysql
terminal and type it in and it works. There may be other errors with the
code, but I have been focusing on the select statement at the moment. If
anyone can help, it would be much appreciated.

 

[Ron Hinds]

addtime is not a valid function in DAO, however. If you wish to use MySQL
syntax with Access DAO, then you need to use SQL PassThrough queries. This
type of query doesn't try to interpret the SQL but rather passes it directly
to the ODBC datasource for processing. I wrote a function I call SQLExecute
to do just this. Then whenver I want to execute a statement directly on the
server, either with or without return values, I call this function. If you
are expecting return values, pass the optional rs argument, otherwise just
pass the SQL string. The rs argument needs to be declared in the calling
routine as a DAO.Recordset (but not initialized). Here is the entire
function:

Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records

On Error GoTo Error_SQLExecute

Dim qdf As QueryDef
Dim errAny As error

Set qdf = DBEngine(0)(0).CreateQueryDef("")

qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL

If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647 '2^31-1
Set rs = qdf.OpenRecordset()
End If

Exit_SQLExecute:
Set qdf = Nothing
Exit Sub

Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next

Resume Exit_SQLExecute

End Sub