I'm using VB 6.0 to write a tool to acess both SQL Server and MS Access Databases. My problem is:
- CASE statement does not work with MS Access Database while IIF statement does not work with SQL -> How to write a statement can work with both SQL and MS Access Database?
- CONVERT function does not work with Access Database while VAL function does not work with SQL (I'd like to convert a string to numeric value) -> How to write a statement can work with both SQL and MS Access Database?
- Some scalar functions only work with Access (like TRIM, UCASE,...) -> How to write a compensating function to work with SQL.
There are often language differences between databases that you can't code around in the SQL itself. Instead, your VB program will have to test for the database it is going to run SQL over and build the appropriate syntax for that db.
TRIM - You can replicate this function in SQL Server by using LTRIM(RTRIM(MyField))