Code: Select all
Set objDBRec = objDBCon.Execute("Select UsrStatic.UserName, Count(Messages.Inbox) As CountOfInbox From UsrStatic Left Join Messages On UsrStatic.UserName = Messages.Inbox")returns an error code -2147217887
Error Number : -2147217887
Error Description : You tried to execute a query that does not include the specified expression 'UserName' as part of an aggregate function.
Error Source : Microsoft JET Database Engine
Error HelpFile :
HelpFile Context : 5003000
It's a pretty cool error description, but I have no clue what it means =p
objDBCon is a valid opened connection object using the Microsoft Jet Engine as provider. The SQL command I give as a parameter works fine under MS Access and returns a neat table with the 2 fields I want for each username.
The VBS part of the synthax seems ok, since in another branch of the code, for example, I have a
Code: Select all
Set objDBRec = objDBCon.Execute("Select UsrStatic.* From UsrStatic")which works fine.
Since the Jet SQL Dialect is a bit different from the Access Dialect, I thought that using a dynaset and an SQL pass-through query might force Access to solve the SQL command instead Jet, but when I tried with :
Code: Select all
Set objDBRec = objDBCon.OpenRecordset("Select UsrStatic.UserName, Count(Messages.Inbox) As CountOfInbox From UsrStatic Left Join Messages On UsrStatic.UserName = Messages.Inbox, dbOpenDynaset, dbSQLPassThrough")I got an error 3001 =/
Error Number : 3001
Error Description : The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
Error Source : ADODB.Connection
Error HelpFile :
HelpFile Context : 0
Another cool message, a bit easier to understand this time, but I checked everything and couldn't find what was wrong...
Any suggestions ? Or someone who knows a bit SQL dialects and can give me the Jet equivalent of my Access query ? *smiles hopefully*