SQL through classic ADO - Undefined Function 'Round' ?

Question

I'm working on a legacy product and i have some SQL being executed through ADO, to an Access database with linked tables to SQL Server. I'm getting the error 'Undefined function 'Round' when i execute the SQL but if i take the query and run directly in Access it works fine. I know that EVERYTHING is correct and that this is a machine specific issue since this is production code, it works on other machines and has been deployed successfully for many clients.

I'm not even sure where to begin to be honest. I'm running the correct (latest) versions of Jet/ADO/MDAC.

ANY help would be appreciated.

Thanks in advance.

EDIT: Obviously, the SQL includes the aggregate function 'Round'. I'm aware of the differences between Jet and SQL implementations. This problem is due to some problem with a component on my machine and NOT with the code. The SQL executes properly when done through MS Access 2007 but NOT through ADO.

Answer 1

EDIT2: Right solution from the comments:

shahkalpesh: If it executes fine thru Access, it could be that Access has the DLL available to it, which has the Round function. What is the connection string, you are using?

Stimul8d: I'm not sure how it can be anything so do with the connection string. This code works on EVERY other machine, with no changes; just not on mine.

Andomar: Well, that's your problem right there, your machine is farked up. You can still install vb6 sp6 maybe.

Stimul8d: Well, SP6 fixed it. Cheers Anndomar, no idea why SP6 fixed it but it did!

EDIT: Based on your comment this newsgroup post might be the answer:

Unfortunately, when you are running queries from outside of Access (as you are from VB), your only connection to the database is through the Jet engine, which doesn't know anything about most VBA functions. There's no way around this, other than to return the data to your VB application and use the functions on the data there.

And two posts later:

I solved the problem. Updated my VB with the Service Pack 6... it took care of the problems.

Old answer here:

Try FLOOR() instead of ROUND().

To round something to the nearest integer, you could:

declare @floatmyboat float
set @floatmyboat = 1.51
select floor(@floatmyboat+0.5)

P.S. Maybe post the exact error you get. If it's "The round function requires 2 to 3 arguments.", that means Sql Server is borking on the ROUND().

Answer 2

The round() function exists in SQL Server as well.
The only difference is: in Access the precision is an optional parameter, but in SQL Server you have to specify it.

So this will only work in Access, but not in SQL Server:

select round(Column) from Table

This will work in Access and SQL Server:

select round(Column,1) from Table

Answer 3

it could be that Access has the DLL available to it, which has the Round function

ACE/Jet uses share expression services with VBA. Broadly speaking, ACE/Jet supports as expressions all VBA5 functions (as distinct from methods) whose arguments and return values are scalar types (e.g. no arrays, no objects). The Round() expression falls into this definition and indeed is available to ACE/Jet with the same semantics as its VBA function equivalent. As anyone familiar with the ACE/Jet engine should know, though, the semantics can differ from the VBA equivalents e.g. ACE/Jet ANSI-92 Query Mode SQL

SELECT TYPENAME(ROUND(5, 1))

returns 'Long', whereas VBA

?Typename(Round(5, 1))

returns 'Integer'.

In other words, Round() wasn't going to be the problem here.