SQL Via Classic ADO - Undefined "Round" Function?
I am working on a legacy product and I have SQL running through ADO to an Access database with linked tables from SQL Server. I get the error "Undefined function" Round "when I execute the SQL, but if I take the query and run it directly in Access it works fine . I know that everything is correct and that it is a machine problem since this is production code, it works on other machines and has been successfully deployed to many clients.
I don't even know where to start honestly. I am running the correct (latest) versions of Jet / ADO / MDAC.
ANY help would be appreciated.
Thanks in advance.
EDIT: Obviously SQL includes an aggregate "Round" feature. I know the differences between Jet and SQL implementation. This issue is related to some problem with a component on my machine and NOT with the code. SQL executes as expected when executed through MS Access 2007 but NOT through ADO.
a source to share
EDIT2: Correct solution from comments:
shahkalpesh: if it does fine through Access, it could be that Access has a DLL available to it that has a Round function. What connection string are you using?
Stimul8d: I'm not sure how this can happen with the connection string. This code works on EVERY other machine unchanged; just not on mine.
Andomar: Good thing your problem is right there, your car is jammed. You can still install vb6 sp6 .
Stimul8d: Well, SP6 has fixed it. Cheers Anndomar, not sure why SP6 fixed it, but it did!
EDIT: Based on your comment, this newsgroup might be the answer:
Unfortunately, when you are running queries from outside Access (like you are from VB), your only connection to the database is through the Jet engine, which knows nothing about most VBA functions. There is no way around this other than to return data to your VB application and use the functions there.
And two messages later:
I solved the problem. Updated my VB with service pack 6 ... took care of issues.
Old answer here:
Try FLOOR () instead of ROUND ().
To round something to the nearest whole number, you can:
declare @floatmyboat float
set @floatmyboat = 1.51
select floor(@floatmyboat+0.5)
PS Perhaps please report the exact error you will receive. If it is "Round function requires 2 to 3 arguments", it means the Sql server is running on ROUND ().
a source to share
The round () function also exists in SQL Server.
The only difference is that in Access, precision is optional, but in SQL Server, you must specify it.
So this will only work in Access, not SQL Server:
select round(Column) from Table
This will work in Access and SQL Server:
select round(Column,1) from Table
a source to share
it is possible that Access has a DLL available for it which has a round Function
ACE / Jet uses Expression Exchange Services with VBA. Generally speaking, ACE / Jet supports as expressions all VBA5 functions (as opposed to methods) whose arguments and return values are scalar types (for example, no arrays, no objects). Expression Round()
refers to this definition and is indeed available to ACE / Jet with the same semantics as its VBA function equivalent. However, as anyone familiar with the ACE / Jet engine knows, the semantics may differ from the VBA equivalents, for example. ACE / Jet ANSI-92 SQL Query Mode
SELECT TYPENAME(ROUND(5, 1))
returns 'Long' whereas VBA
?Typename(Round(5, 1))
returns 'Integer'.
In other words, Round()
it won't be a problem here.
a source to share