How to support the Access Nz-function in SQL
-
Hi
We've build a c/s application with Access 2000. Now we try to offer MS-SQL Server as an alternative server.
Usually we put the SQL-string together in VBA and send it to the database.
Coming from Access we often use the Nz-function - but this function is not supported by the SQL Server. I know there is a function called ISNULL in SQL doing the same but the client still should work with an Access-Db and IsNull in Access only returns True or False...
Now here's my question: Is there a way to mimic the Nz-function in SQL? Can I program a stored procedure for that purpose? Or is there an even more elegant way to solve this problem?
Thanks for your help!
Marc
-
To the best of my knowledge, there is no SQL syntax you can pass to both SQL Server and Access to achieve null-to-zero functionality. While they have a lot in common, there are significant differences between their SQL processing engines, and this is one of them.
The general solution is to separate all your data access out into a distinct code layer, which knows what flavour the database is and makes appropriate queries. If null-to-zero functionality is the first instance you have encountered of the difference between Access SQL and T-SQL, this will seem like a lot of work for just a little thing, but believe me this won't be the last time you find such a difference
This is the kind of thing I mean: Instead of what you have currently, which might be along the lines of
' define query string
sSQL = "SELECT Nz(SupplierID) FROM Products WHERE Name='" & sProductName & "'"
' execute query based on sSQL
rs.Open sSQL, conn
' returned field is the supplier ID or zero
lSupplierID = rs.Fields(0).Value
create a layer (which can be anything from a simple procedure, through a module, an in-process DLL, a service, whatever) which does the actual data access and returns what the client actually wants. So your client calling code would look like:
lSupplierID = <appropriate syntax for calling data access layer>.GetSupplierID(sProductName)
and within the data access layer you would have
...
If <conn is a connection to an Access db>
sSQL = "SELECT Nz(SupplierID) FROM Products WHERE Name='" & sProductName & "'"
ElseIf <conn is connection to a SQL Server db>
sSQL = "SELECT ISNULL(SupplierID, 0) FROM Products WHERE Name='" & sProductName & "'"
End If
...
As I said, if this is the only place you need to 'switch' between Access SQL and T-SQL, you won't want to go to all the bother of pulling all your data access code out; you will probably just use the above If ... Else... logic at the appropriate place in your client code. But the principle is worth bearing in mind - that you can't always use the same SQL for Access and SQL Server.
-
You could try this: Create a Function in Access that works the same as the ISNULL() FUNCTION in SQL, for example:
Function IsNull(test_expr,default_value)
IsNull = Nz(test_expr, default_value)
End Function
This would override the built-in Access IsNull() function. I did a quick test in Access 2000 and it does work for me here.
-
Hi Marc
I know that learning to program stored procs is different from a module based environment but try and do as much processing server side, sql is made for it.
If you're offering your service now in SQL Server as well as Access your connection settings should be different, based on that you could select which code to use when compiling your sql statement, what AKM posted. But instead of passing through the sql statement rather call a stored procedure. With this method you'll be sending less data over the network, sql server will be returning the data from precompiled procs and your developers will love not having to recompile the code or rolling out a new version of your software if you simply need to change the order by statement.
If all your customers are using Office 2k for the Access front-end you could also look into upgrading your Access datafile into an MSDE backend, then, as far as I know, you'll only have to have sql code to write, can support more users and be able to upsize your customers to SQL server, if then need to, more easily.
Good luck
Max
Max
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply