I must create a query compatible for SQL Server and MS-Access.
我必须创建一个兼容SQL Server和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).
问题是在SQL Server中我尝试将数字转换为varchar。在SQL Server中，我可以像这样进行转换：在MS Access：CStr（5）中转换（varchar（100），5）。
Are there possibilities to do this compatible for the both databases?
是否有可能兼容两个数据库？
2 个解决方案
#1
5
In SQL Server, you can write a user defined scalar function called CStr() that does the conversion.
在SQL Server中，您可以编写一个名为CStr（）的用户定义标量函数来执行转换。
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.
要使VB与Access和SQL Server一起使用，您可以放弃Access并使用真实数据库。哦，我想这不是解决方案;）您将需要识别数据库的类型，并为每个数据库分配不同的代码。你可以通过使用视图隐藏一些丑陋来最小化这个。
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().
您可能会发现切换到SQL Server 2012是有利的，SQL Server 2012已扩展其功能集以包含一些Access功能。但不是cstr（）。
#2
4
We can convert the number in varchar so:
我们可以在varchar中转换数字，所以：
select format(Adress_ID,'0') ... next code
this code will convert from the both, Access and SQL Server
此代码将从Access和SQL Server两者转换
#1
5
In SQL Server, you can write a user defined scalar function called CStr() that does the conversion.
在SQL Server中，您可以编写一个名为CStr（）的用户定义标量函数来执行转换。
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.
要使VB与Access和SQL Server一起使用，您可以放弃Access并使用真实数据库。哦，我想这不是解决方案;）您将需要识别数据库的类型，并为每个数据库分配不同的代码。你可以通过使用视图隐藏一些丑陋来最小化这个。
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().
您可能会发现切换到SQL Server 2012是有利的，SQL Server 2012已扩展其功能集以包含一些Access功能。但不是cstr（）。
#2
4
We can convert the number in varchar so:
我们可以在varchar中转换数字，所以：
select format(Adress_ID,'0') ... next code
this code will convert from the both, Access and SQL Server
此代码将从Access和SQL Server两者转换