MySQl and VB5 Query Problem

[ToeShot]

First I am using VB5 Enterprise Edition. and I am trying to run this query in this code:

Private Sub cmdUpdate_Click()
strsql = "DROP TABLE temp;" _
& "CREATE TABLE temp" _
& "SELECT COUNT(*) AS Calls, SUM(minutes) AS Minutes FROM" _
& "details WHERE time BETWEEN '7:01' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details WHERE time" _
& "NOT BETWEEN '7:00' AND '15:00'; _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details;" _
& "SELECT * FROM temp;"

datPrimaryRS.RecordSource = strsql
grdDataGrid.Refresh
datPrimaryRS.Refresh
End Sub

I am running this piece of code against MySql 3.39.41 and according to there HTML manual This the way I need to run this query since they don't support union queries yet.

this works in the MySql enviorment. But not when I run the Code in VB. Does anyone have any ideas or knows why.

 

[Broccoli2]

My guess is that you're trying to execute 5 statements all together. Try doing them one at a time and see if that works. (In addition, you might also use

CREATE TEMPORARY TABLE

to avoid naming conflicts).

-Rob

 

[ToeShot]

Thanks RobEyre But I need them to execute at the same time to create my table columns. The problem has been solved though in the visual basic forum. I needed to create a QueryDef. Her is my code now that works

Dim qdfTempTable As QueryDef

Set qdfTempTable = datPrimaryRS.Database.CreateQueryDef

qdfTempTable.SQL = "DROP TABLE temp;" _
& "CREATE TABLE temp" _
& "SELECT COUNT(*) AS Calls, SUM(minutes) AS Minutes FROM" _
& "details WHERE time BETWEEN '7:01' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details WHERE time" _
& "NOT BETWEEN '7:00' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details;"

datPrimaryRS.RecordSource = "SELECT * FROM temp;"
grdDataGrid.Refresh
datPrimaryRS.Refresh