Error in SQL Query

[Avatar]

Greetings,

I'm getting the following error when trying to run a SQL query in Excel:

Run-time error '-2147217904 (80040e10)':

[Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

The code is:

Sub ExtractFrom_DB_ADO()
 Dim Conn As New ADODB.Connection
 Dim Rst As New ADODB.Recordset
 Dim MySQL As String, _
     AccessConnect As String
 Dim xlApp As Object, _
     xlWb As Object, _
     xlWs As Object
 Dim recArray As Variant
 
 AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=" & DBName & ";" & _
                 "DefaultDir=" & DatabaseDIR & ";" & _
                 "Uid=Admin;Pwd=" & strPassword & ";"
 Conn.Open AccessConnect
 
 MySQL = _
  "SELECT tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, Sum(tbl_DataReturn.Hours) AS SumOfHours, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent] AS Reward " & _
  "FROM tbl_HistoricRewardScore INNER JOIN tbl_DataReturn ON tbl_HistoricRewardScore.Date = tbl_DataReturn.Date " & _
  "GROUP BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent], tbl_DataReturn.[Deleted?] " & _
  "HAVING (((Sum(tbl_DataReturn.Hours)) <> 0) And ((tbl_DataReturn.[Deleted?]) = False)) " & _
  "ORDER BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber;"
 
 Set Rst = Conn.Execute(MySQL)
 'Sheet3.Cells(3, 2) = MySQL
 
 If Not Rst.EOF Then
  Set xlApp = CreateObject("Excel.Application")
  Set xlWb = xlApp.Workbooks.Add
  Set xlWs = xlWb.Worksheets(Sheet3)
  
  xlWs.Cells(3, 2).CopyFromRecordset Rst
  'recArray = Rst.GetRows
  
  'I'm still working here
  
  Set xlWs = Nothing
  Set xlWb = Nothing
  Set xlApp = Nothing
 End If
 
 Rst.Close
 Conn.Close
 Set Rst = Nothing
 Set Conn = Nothing
 
End Sub

The generated statement is (It was unreadably long so i broke it into lines):

SELECT tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, Sum(tbl_DataReturn.Hours) AS SumOfHours, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent] AS Reward 
FROM tbl_HistoricRewardScore INNER JOIN tbl_DataReturn ON tbl_HistoricRewardScore.Date = tbl_DataReturn.Date 
GROUP BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent], tbl_DataReturn.[Deleted?] 
HAVING (((Sum(tbl_DataReturn.Hours)) <> 0) And ((tbl_DataReturn.[Deleted?]) = False)) 
ORDER BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber;

I was expecting to have missed a quote or formatted the statement incorrectly, but i can't see anything..

 

[Norie]

What do you mean by 'generated' statement?

Did you create a query in the QBE window and then copy the SQL from the SQL view?

If you do need to split the query then try something like this.

strMySQL = " SELECT tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, Sum(tbl_DataReturn.Hours) AS SumOfHours, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent] AS Reward "
strMySQL = strMySQL & " FROM tbl_HistoricRewardScore INNER JOIN tbl_DataReturn ON tbl_HistoricRewardScore.Date = tbl_DataReturn.Date "
strMySQL = strMySQL & " GROUP BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber, tbl_HistoricRewardScore.OverallRewardPercent, [tbl_DataReturn].[Hours]*[tbl_HistoricRewardScore].[OverallRewardPercent], tbl_DataReturn.[Deleted?] "
strMySQL = strMySQL & " HAVING (((Sum(tbl_DataReturn.Hours)) <> 0) And ((tbl_DataReturn.[Deleted?]) = False)) "
strMySQL = strMySQL & " ORDER BY tbl_DataReturn.Date, tbl_DataReturn.EmployeeNumber; "

The advantage is you can debug it easier, eg add/remove clauses, change criteria etc.

As for the specific error, I would suggest you start by removing the HAVING clause and ORDER BY clauses.

Oh, and almost forgot - it's a SELECT query, so use Open not Execute.

Execute is for 'action' queries, eg INSERT, DELETE etc.

Set rst = CreateObject("ADODB.Recordset")
 
rst.Open strMySQL, conn

PS You do know you could do some of the grouping and summing in Excel once you've imported the raw data.

 

[Avatar]

By generate statement i meant ther SQL statement in it's finaly form - not so important in this one i guess since i'm not using variable to construct the SQL statement.

I was not aware it was open and not execute. ...I've been using execute for all manner of SQl stataments.. oops.

Well it didn't work anyway, so just used the SQL statement to create a query in Access and call that instead - which works fine.

Thanks.

 

[Norie]

How did it not work?

We can only go on what you've posted - an SQL statement on it's own doesn't tell us much.

The syntax can be checked and other possible errors pointed out but without knowing more about the context it's being used in that's about it.

The problem might not even by with the SQL.

 

[Avatar]

Changing the Execute to open left me with the same error, attempting to remove parts of the query resulted in much longer and more elaborate errors - several of them too as when I attempted to fix one I caused another. I eventually gave up, set a cell equal to "MySQL", copied it and created a new Query in MS Access (which worked fine as I expected since I created it in MS Access in the first place), saved that and just open that instead. Bit messy since the DB is not used only for this report, but either way it works.

Perhaps i'm misunderstanding your message, but you seem to think i'm complaining about somthing? Solving a problem is nice, but not essential if I have another option - and normally someone with a big stick demanding their report yesterday.

 

[Norie]

Not complaining, just wondering how it didn't work and what you actually tried.

Apologies if it came across that way, definitely wasn't intended.

Anyway, if you've got it sorted then that's the important thing.