Access与Sql Server之ASP代码比较

[一]连接问题（举例)
[Microsoft Access]
constr = "DBQ=c:\data\clwz.mdb; DRIVER={Microsoft Access Driver (*.mdb)}"
[Microsoft Sql Server]
constr = "DRIVER={SQL Server};SERVER=host;DATABASE=mydata;uid=sa;pwd="

[二]相似函数（举例）

[1]DATEDIFF(datepart, startdate, enddate)
其中“datepart”参数可选项如下：
设置 描述
————————————
[Microsoft Access]
年 yyyy
季度 q
月 m
一年的日数 y
日 d
一周的日数 w
周 ww
小时 h
分钟 n
秒 s

[Microsoft Sql Server]
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms

-------------------------

基本上差不多，但注意的是在写的时候，
[Microsoft Access]要加引号，如：datediff('d',enddate,'2004/08/01')
[Microsoft Sql Server]则不需要，如：datediff(d,enddate,'2004/08/01')

[2][Microsoft Access]中可用如cstr等转数据类型函数，而
[Microsoft Sql Server]中则用convert或cast函数，如：
convert(varchar,[amount])等。

[3][Microsoft Sql Server]
取当前时间用getdate等等...

[三]语句
[Microsoft Sql Server]
可以用
CASE
WHEN THEN
WHEN THEN
...
ELSE
END
语句，而
[Microsoft Access]
不支持。
[Microsoft Access]也不支持between语句
[Microsoft Sql Server]则可以这样写：
[date] between @date1 and @date2
[四]查询表
[Microsoft Sql Server]
可三个及以上表join查询，而
[Microsoft Access]
好像只能两个表联接查询（待权威确认）,
而且[Microsoft Sql Server]可用“*=”和“=*”连接符。[五]除零问题
[Microsoft Access]
在碰到除数为零时，自动丢掉相关记录，而
[Microsoft Sql Server]
则会报错，且查询中止。删除代码：
[Microsoft Access]
可以这样写：delete * from [table]
[Microsoft SQL Server]
只能这样写：delete from [table]
多*会报错
_____________________________________
当前日期：
[Microsoft Access]
用date()
[Microsoft SQL Server]
用getdate()如果数据库可能会更换类型的话，可以
在ASP代码中加上如这样：
if inStr(constr,"Microsoft Access") > 0 then
sqlstr=[Microsoft Access][sql代码]
else
sqlstr=[Microsoft Sql Server][sql代码]
end if
(constr--连接字符串)
这样即使改了数据库，也不用改数据库查询更新代码了。
再加：access中有true、false的字段记录，而sql里只有smallint，对应如果在access里有“字段名=true”的，在sql里要改成“字段名=1”
网上大部分的免费asp程序使用的是access数据库。但是access数据库作为一个中小型的单机数据库系统，在承担访问量、数据量大的网站应用时，往往就不堪重负了。一般认为，超过50M的access数据库性能就开始明显下降，超过100M以后，出错、运行慢的问题会更加突出。尽管可以如动网7.0以后那样，从程序的角度尽量优化以图提高性能，但是不能从根本上解决问题。
这时也许使用微软的SQL Server数据库就是最可能的办法，当然也可以使用其它的如Oracle、MySQL等等，但是作为改写来说，由于同为微软的产品，改写成SQL Server应该是最省力的办法。

一、改写前提：

系统已经安装好SQL Server2000并且打上了SP3补丁；安装好Office套件里面的Access；使用一个支持纯文本编辑并且带有行号显示的编辑器，推荐Ultra Edit，当然也可以使用FrontPage2003，不过以前的版本行号显示不太好用。

个人能力要求：会基本的asp语法、access数据库的操作、SQLServer企业管理器的基本操作。

二、数据库的准备

一般来说有两种情况：
1、程序提供了SQL数据库格式：有一个MDF文件，或者提供了创建SQL数据库的SQL脚本文件（后缀名为.sql）。
如果有mdf文件，可以用企业管理器直接附加上，如果提供的是sql脚本文件，那么就先用企业管理器自己创建一个sql数据库，然后数据库用企业管理器中的查询分析器运行这个脚本创建数据库表。
这样建立的数据库基本不用再去改写什么了。
2、更多的是没有提供SQL数据库或脚本文件的，这时，就要自己来做这一切了，这也是我们这个帖子主要解决的问题。一般这样的程序会提供一个access数据库，这样你就用企业管理器导入access数据库，导入后需要改写下面一些东西：
对照原来的access，改写下面的部分：
（1）sql数据库表是没有自动字段的，因此原来access中的自动字段被转换成了普通字段，需要手工改成标识类型，增量为1。
（2）所有的时间字段，如果定义了默认值，那么原来肯定是now()，需要改成getdate()
（3）原来字段的默认值一般都不会自动引入，需要对照原表的字段手工添加。
（4）由于数据库的不同，access和sql的字段类型很多转换后就变化了，比如原来的《是否》字段会被转换成bit或者int，备注字段被转换成longtext，text字段转换成varchar等等，一般来说不会影响程序运行，如果有问题，我们在下面的程序改写部分再说。
（5）如果你要用一个For SQL的程序，里面用到了存储过程，那么你应该有这个程序本身建立SQL数据库的方法：有其本身的SQL数据库文件，或者sql脚本；如果没有的话，采用导入access数据库的方式是无法建立存储过程的，这样你最好放弃这个For SQL的程序版本，使用同样版本的For Access的程序，导入access数据库，然后用下面的改写方法自己改成SQL版本的程序。

三、连接字符串的改写

可参考动网的这段，分别是针对access和SQL的
Dim ConnStr
If IsSqlDataBase = 1 Then
'sql数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用IP）
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "dvbbs7"
SqlPassword = ""
SqlUsername = "dvbbs"
SqlLocalName = "(local)"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
Else
'免费用户第一次使用请修改本处数据库地址并相应修改data目录中数据库名称，如将dvbbs6.mdb修改为dvbbs6.asp
'http://www.knowsky.com/
Db = "data/fengerqingqing.mdb"
ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)
End If
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr

当然你使用SQL的话，有关access的使用语句可以删除，就是else后面到on error resume next前面，变成这样：

Dim ConnStr
'sql数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用IP）
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "dvbbs7"
SqlPassword = ""
SqlUsername = "dvbbs"
SqlLocalName = "(local)"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr

也可以简洁一些，写成这样：
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider = Sqloledb; User ID = sa; Password = 1234567; Initial Catalog = dvbbs7; Data Source = (local);"
里面的数据库名称、数据源、用户、密码根据自己的实际情况改写一下。

四、程序的改写

这也有两种情况
1、如果你幸运，拿到的是For SQL的程序，那么如果上面的数据库建立过程没有遇到麻烦，程序基本上就可以运行了，出错的话，只是程序本身的bug，如何修改不是这个帖子讨论的内容，就不赘述了。
2、大多数情况，程序本身是For Access的，与For SQL的程序差别主要是程序中使用到的SQL查询语句。注意，SQL查询语句是数据库应用不可缺少的部分，不管是For SQL还是For Aceess的程序使用的语法大体差不多，但是有一些微妙的差别，正是这些差别，造成了程序的不通用，也是我们需要修改的主要内容。这样一般要修改的部分如下：
（1）时间函数的问题：SQL数据库的时间函数与access不同，最常见的是取现在时间的函数，access是now()，SQL是getdate()。因此凡是在where子句中使用了now()的地方都要改成getdate();注意，now()函数在asp程序本身也要使用，凡是不在数据库查询或执行语句中使用的now()函数千万不要改。
（2）时间比较函数：datediff('d','时间1'，‘时间2’)这是access查询用的格式，SQl中这些引号都要去掉，同时时间格式的前后可能加上了#，这也要去掉。同样这也是指在sql语句中的，在asp语句中的要保持原样。
（3）空值的表示：在access中，判断空值一般用是否＝""来表示，但是这在SQL中往往出错，如果遇到出错的问题或者程序运行不正常，可以改成如这样判断：where (name is null)
（4）真假值判断：access中可以用＝true、＝false来判断，但是在SQL中就会出错，因此在SQL查询或执行语句中这类判断要分别改成＝1、＝0。注意一点：有些程序虽然写成＝“true”，但是由于有引号，所以这个字段是字符类型的，你不能改成＝1，保持原样即可。

以上是比较常见的改写的地方，还有一些不太常见，如果遇到了可以在此回帖讨论。

五、程序的调试

前面推荐使用带有行号的编辑器，是因为上述的改写不大可能是直接搜索程序源码来做，很难找全。
我采取的方式一般这样：数据库改写完成，直接调试程序，出错后，看看出错的提示，找到相应文件的代码行，但是根源往往不是那行，比如出错的语句是：conn.execute(sql)，但是这句本身是没有错的，错误原因是里面的这个sql字符串，那就向上看这个sql字符串是如何生成的，按照上面所说的程序修改办法修改。

数据库导入以后，自动增加字段需要重写，所有的数字类型需要增加长度，最好用decimal。

所有的默认值都丢失了。主要是数字类型和日期类型。

所有now()，time()，date()要改成getdate()。

所有datediff('d', time1, time2)要改成datediff(day, time1, time2)

有可能一些true/false类型不能使用，要变为1/0。

备注类型要通过cast(column as varchar)来使用。

CursorType要改成1,也就是打开数据库时要给出第一个数字参数为1，否则记录可能显示不完整。

isnull(rowname)要改成rowname = null

ACCESS的数据库中的自动编号类型在转化时，sql server并没有将它设为自动编号型，我们需在SQL创建语句中加上identity，表示自动编号！

转化时，跟日期有关的字段，SQL SERVER默认为smalldatetime型，我们最好将它变为datetime型，因为datetime型的范围比smalldatetime型大。有时用smalldatetime型时，转化失败，而用datetime型时，转化成功。

对此两种数据库进行操作的sql语句不全相同，例如：在对ACCESS数据库进行删除纪录时用："delete * from user where id=10",而对SQL SERVER数据库进行删除是用："delete user where id=10".

日期函数不相同，在对ACCESS数据库处理中，可用date()、time()等函数，但对SQL SERVER数据库处理中，只能用datediff,dateadd等函数，而不能用date()、time()等函数。

在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数，像cstr()函数，而对SQL SERVER数据库处理中，却不能用。
下表比较了MicrosoftAccess数据库（MicrosoftAccess数据库：数据和对象（如表、查询或窗体）组成的集合，与特定的主题或用途有关。MicrosoftJet数据库引擎用于管理数据。）和MicrosoftAccess项目（MicrosoftAccess项目：与MicrosoftSQLServer数据库连接且用于创建客户/服务器应用程序的Access文件。项目文件中不包含任何数据或基于数据定义的对象（如表或视图）。）的数据类型（数据类型：决定字段可拥有的数据类型的字段特征。数据类型包括Boolean、Integer、Long、Currency、Single、Double、Date、String和Variant（默认））。

MicrosoftAccess数据类型SQLServer数据类型
是/否（“是/否”数据类型：一种字段数据类型，用于只有两种可能值（如是或否、True或False）的字段。不允许有Null值。）bit（bit数据类型：在Access项目中，一种存储值为1或0的数据类型。接受1和0以外的整数值，但总是将其解释为1。）
数字（“数字”数据类型：MicrosoftAccess数据库中的一种字段数据类型，用于将在数学运算中使用的数值数据。但是，若要显示或计算货币值，则应使用“货币”数据类型。）（字节）tinyint（tinyint数据类型：Access项目中的一种占一个字节（8位）的数据类型，用于存储从0到255范围内的整数。）
数字（整型）smallint（smallint数据类型：Access项目中的一种2字节（16位）数据类型，存储位于-2^15(-32,768)与2^15-1(32,767)之间的数字。）
数字（长整型）int（int数据类型：Access项目中的一种4字节（32位）数据类型，存储位于-2^31(-2,147,483,648)与2^31-1(2,147,483,647)之间的数字。）
数字（单精度浮点型）real（real数据类型：在Access项目中，一种近似的数值数据类型，精度为7位，正值取值范围大致从1.18E-38到3.40E+38，负值取值范围大致从-1.18E-38到-3.40E+38，也可以取0。）
（无等价的数据类型）bigint（bigint数据类型：Access项目中的一种8字节（64位）数据类型，存储位于-2^63(-9,223,372,036,854,775,808)与2^63-1(9,223,372,036,854,775,807)之间的数字。）
数字（双精度浮点型）float（float数据类型：在Access项目中，一种近似的数值数据类型，精度为15位。它所存储的正值范围大致是从2.23E-308到1.79E+308，负值范围大致是从-2.23E-308到-1.79E+308，也可以为0。）
货币（“货币”数据类型：MicrosoftAccess数据库中的一种数据类型，用于与货币有关的计算或其精确度极其重要的定点计算。）money（money数据类型：在Access项目中，用于存储货币值的数据类型，取值范围从-922,337,203,685,477.5707到922,337,203,685,477.5807，精确度为万分之一个货币单位。）

smallmoney（smallmoney数据类型：Access项目中的一种存储货币值的数据类型，取值范围从-214,748.3648到214,748.3647，精确度为万分之一个货币单位。当显示smallmoney值时，会将它们四舍五入为两个小数位。）
小数/数值（decimal数据类型（Access数据库）：精确的数值数据类型，用于存储-10^38-1到10^38-1的值。可以指定数值范围（最大总位数）和精度（小数点右边的最大位数）。）decimal（decimal数据类型（Access项目）：精确的数值数据类型，用于存储-10^38-1到10^38-1的值。可以指定数值范围（最大总位数）和精度（小数点右边的最大位数）。）

numeric（numeric数据类型：在Access项目中，一种精确的数值数据类型，取值从-10^38-1到10^38-1。可以指定数值范围（最大总位数）和精度（小数点右边的最大位数）。）
日期/时间（“日期/时间”数据类型：Access数据库的一种数据类型，用来存放日期和时间信息。）datetime（datetime数据类型：在Access项目中，日期和时间的数据类型，范围从1753年1月1日到9999年12月31日，精确度为三百分之一秒，即3.33毫秒。）

smalldatetime（smalldatetime数据类型：Access项目中的一种日期和时间数据类型，精度不如datetime时间数据类型。数据取值范围从1900年1月1日到2079年6月6日，精确度为一分钟。）
自动编号（“自动编号”数据类型：MicrosoftAccess数据库中的一种字段数据类型，当向表中添加一条新记录时，这种数据类型会自动为每条记录存储一个唯一的编号。可以产生三种编号：顺序号、随机号和同步复制ID。）（递增）int（int数据类型：Access项目中的一种4字节（32位）数据类型，存储位于-2^31(-2,147,483,648)与2^31-1(2,147,483,647)之间的数字。）（定义了Identity属性）
文本（“文本”数据类型：MicrosoftAccess数据库中的一种字段数据类型。“文本”数据类型最多可以包含255个字符，或者是由FieldSize属性指定的一个小一些的字符数。）(n)varchar(n)（varchar(n)数据类型：Access项目中的一种可变长度的数据类型，最大长度为8,000个ANSI字符。）

nvarchar(n)（nvarchar(n)数据类型：在Access项目中，一种可变长度的数据类型，最多可含4,000个Unicode字符。Unicode字符每字符占两个字节，而且支持所有国际字符。）
备注（“备注”数据类型：在MicrosoftAccess数据库中的一种字段数据类型。“备注”字段最多可以包含65,535个字符。）text（text数据类型：Access项目中的一种长度可变的数据类型，最多可存储2^31-1(2,147,483,647)个字符；默认长度为16。）
OLE对象（“OLE对象”数据类型：字段的数据类型之一，用于在其他应用程序中创建的、可链接或嵌入（插入）到Access数据库中的对象。）image（image数据类型：在Access项目中，一种长度可变的数据类型，最多可存储2^31-1(2,147,483,647)字节的二进制数据。image数据类型用来存储BLOB（二进制大对象），如图片、文档、声音和已编译代码。）
同步复制ID（又名全局唯一标识符（GUID：在Access数据库中，一种用于建立同步复制唯一标识符的16字节字段。GUID用于标识副本、副本集、表、记录和其他对象。在Access数据库中，GUID是指同步复制ID。）(GUID)）uniqueidentifier（uniqueidentifier数据类型：在Access项目中，16字节的全局唯一标识符(GUID)。）（仅适于SQLServer7.0或更高版本）
超链接（“超链接”数据类型：存储超链接地址的Access数据库字段的数据类型。地址最多可以包含四部分，用以下语法格式编写：displaytext#address#subaddress#。）char（char数据类型：在Access项目中，一种固定长度的数据类型，最多可含8,000个ANSI字符。）,

nchar（nchar数据类型：在Access项目中，一种固定长度的数据类型，最多可含4,000个Unicode字符。Unicode字符每字符占两个字节，而且支持所有国际字符。）,varchar,nvarchar（Hyperlink属性设为Yes）
（无等价的数据类型）varbinary（varbinary数据类型：Access项目中的一种可变长度的数据类型，最多可存储8,000字节的二进制数据。）
（无等价的数据类型）smallint（smallint数据类型：Access项目中的一种2字节（16位）数据类型，存储位于-2^15(-32,768)与2^15-1(32,767)之间的数字。）
（无等价的数据类型）timestamp（timestamp数据类型：在Access项目中，一种每插入或更新一行就会自动更新的数据类型。timestamp列中的值不是datetime数据，而是binary(8)或varbinary(8)，标明了数据修改的顺序。）
（无等价的数据类型）charnchar
（无等价的数据类型）sql_variant（sql_variant数据类型：Access项目中的一种数据类型，存储除text、ntext、image、timestamp和sql_variant类型以外的多种数据类型的值。在列、参数、变量或用户定义函数的返回值中使用。）
（无等价的数据类型）用户定义（用户定义的数据类型：在MicrosoftSQLServer数据库中，允许某列包含的数据的类型定义，由用户利用现有的系统数据类型定义。规则和默认值仅可以绑定到用户定义的数据类型。）

注释在Access项目或SQLServer数据库中，前缀“n”代表“国家/地区”，意思是这个数据类型是启用Unicode的。在Access数据库中，全部文本列在默认情况下都是启用Unicode的。
ACCESS转SQL需要注意的问题
2006-2-13 16:01:20
很多朋友想用SQL2000数据库的编程方法，但是却又苦于自己是学ACCESS的，对SQL只是一点点的了解而已，这里我给大家提供以下参考---将ACCESS转化成SQL2000的方法和注意事项
一，首先，我说的是在ACCESS2000，SQL2000之间转换，其他的我也还没有尝试过，希望大家多多试验，肯定是有办法的；
二，转换的方法
1，打开”控制面板“下”管理工具“中的”数据库源“；
2，按”添加“添加一个新的数据源，在选择栏里选”DriverdomicrosoftAccess
(*.mdb)”,完成后将出现一个框,

在”数据库源“里面输入你想写的名称，我取名叫“ABC”,说明不需要填，接着，按下面的选择，寻找你的数据库地址和选中（注意，请先备份自己的ACCESS数据库），然后确定。
数据源在这里建好了，剩下转换了。

3，打开SQL2000企业管理器，进入数据库，新建一个空的数据库“ABC”；
4，选择新建立的数据库，按鼠标右键，选择“所有任务”下“导入数据”，按“下一步”继续；
5，在数据库源下拉但中选择”DriverdomicrosoftAccess(*.mdb)“，在”用户/系统DSN“中，选种你刚才添加的”ABC“，按”下一步“；
6，“目的”不需要修改，选择服务器（一般下为自己的本机"local",也可以选择服务器地址或者局域网地址,确定你的权限是否可以操作,),"使用WINDOWS身份验证"指用自己的系统管理员身份操作,"使用SQL身份操作验证"可以用于网站的操作,推荐用后者;
7,选上"使用SQL身份操作验证"后,填写你的用户名和密码,我自己选择的是系统默认号码"sa","****",数据库选择刚新建的"ABC",按"下一步";
8,这一步的两个单项选择,"从数据源复制表和视图"与"用一条查询指令指定要传输的数据",选择前者,按"下一步"继续;
9,这里将出现你自己ACCESS数据库的表,按"全选"后,下一步;
10,"DTS导入/导出向导",看"立即运行"被选中按"下一步",
11,按"完成"继续;
12,这个步骤你将看到你的数据被导入SQL2000里面,当出现"已经成功把XXX个表导入到数据库"的字样,而且所有的表前面都有绿色的勾,就表示成功导入所有数据,如果中途出现问题或者表前面有红色的*的话,说明该表没有成功导入,这时就要回去查看自己的操作是否正确了.

三,数据修改
1,由于SQL2000里面没有"自动编号",所以你的以"自动编号"设置的字段都会变成非空的字段,这就必须手工修改这些字段,并把他的"标示"选择"是",种子为"1",增量为"1",
2,另外,ACCESS2000转换成SQL2000后,原来属性为"是/否"的字段将被转换成非空的"bit",这时候你必须修改成自己想要的属性了;
3,另外,大家要注意对时间函数的把握.ACCESS与SQL是有很多不同的.

四、相关的字段问题
1.ACCESS的数据库中的自动编号类型在转化时，sqlserver并没有将它设为自动编号型，我们需在SQL创建语句中加上identity，表示自动编号！
2.转化时，跟日期有关的字段，SQLSERVER默认为smalldatetime型，我们最好将它变为datetime型，因为datetime型的范围比smalldatetime型大。我遇见这种情况，用smalldatetime型时，转化失败，而用datetime型时，转化成功。
3.对此两种数据库进行操作的sql语句不全相同，例如：在对ACCESS数据库进行删除纪录时用："delete*fromuserwhereid=10",而对SQLSERVER数据库进行删除是用："deleteuserwhereid=10".
4.日期函数不相同，在对ACCESS数据库处理中，可用date()、time()等函数，但对SQLSERVER数据库处理中，只能用datediff,dateadd等函数，而不能用date()、time()等函数。
5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数，像cstr()函数，而对SQLSERVER数据库处理中，却不能用。

五、相关语句问题
自动增加字段需要重写。在access中经常使用的自动编号字段，导入到mssql后，他并不是自增型的int，需要手工设置，把导入后的自动编号字段的标识的“否”改为“是”，“种子”和“递增量”都为“1”，才能成为自动编号

所有的默认值都丢失了。主要是数字类型和日期类型

所有now()，time()，date()要改成getdate()

所有datediff('d',time1,time2)要改成datediff(day,time1,time2)

所有datediff('ww',time1,time2)要改成datediff(week,time1,time2)

所有datediff('d',time1,time2)要改成datediff(day,time1,time2)

在mssqlserver中，有许多保留字，在access中是没有的，当你把数据导入到mssql的时候，问题就出来了。mssql在导入的时候，会自动给这些字段（包括数据库中的表名）加上“[字段名]”，因此，你必须修改你的脚本，把相应的字段名字（或者表名字）加上中括号，或改变字段名字为不是mssql的保留字

在用access关于时间的使用，大家喜欢使用“select*fromaaaawhiletime="&now()”这样的sql语句，然而，在mssql中没有“now()”这个函数，而是使用“getdate()”，所以，所有的sql语句中的“now()”必须换成“getdate()”。

日期函数不相同，在对ACCESS数据库处理中，可用date()、time()等函数，但对
SQLSERVER数据库处理中，只能用datediff,dateadd等函数，而不能用date()、time()等函数。

转化时，跟日期有关的字段，SQLSERVER默认为smalldatetime型，我们最好将它变为datetime型。