convert int to varchar working for SQL Server and MS-Access

Question

I must create a query compatible for SQL Server and MS-Access.

The problem is that in SQL Server I try to convert a number to varchar. In SQL Server I can do the conversion like this: convert(varchar(100), 5) in MS Access : CStr(5).

Are there possibilities to do this compatible for the both databases?

Answer 1

In SQL Server, you can write a user defined scalar function called CStr() that does the conversion.

The built-in routines are incompatible.

Unfortunately, this won't really work, because you need to prefix the function all with a schema name. So, you can have:

create function cstr(@val int)
returns varchar(255) as
begin return(cast(@val as varchar(255))) end;

But you have to call it as:

select dbo.cstr(4);

If the value is in a column, then consider writing a view on the table, in each database, that does the conversion in the view.

To get VB to work with both Access and SQL Server, you could just give up on Access and use a real database. Oh, I guess that's not a solution ;) You are going to need to identify the type of database and have different code for each one. You can minimize this by using views to hide some of the ugliness.

You might find it advantageous to switch to SQL Server 2012, which has expanded its repertoire of functions to include some Access functions. But not cstr().