Convert int to varchar for SQL Server and MS-Access

Question

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

The problem is that in SQL Server I am trying to convert a number to varchar. In SQL Server, I can perform the conversion as follows: convert(varchar(100), 5) to MS Access: CStr(5) .

Is there any way to make this compatible for both databases?

Answer 1

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

Built-in routines are incompatible.

Unfortunately, this will not work because you need the prefix of the all function with the name of the schema. So you can:

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

But you should call it like:

 select dbo.cstr(4); 

If the value is in a column, consider writing a view to a table in each database that performs the conversion in the view.

To get VB to work with both Access and SQL Server, you could simply refuse access and use a real database. Oh, I think this is not a solution;) You will need to determine the type of database and have a different code for each. You can minimize this by using representations to hide some deformities.

You might find it beneficial to upgrade to SQL Server 2012, which has expanded its repertoire of features to include some access features. But not CStr() .

Answer 2

We can convert the number to varchar like this:

  select format(Adress_ID,'0') ... next code 

this code will convert from both: Access and SQL Server