asp调用的后台数据库从sql server2000该为access2000,连接语句应该怎样该?(多谢进来)
我先用sql server2000做后台数据库,在ASP中调用以下语句成功打开数据库:
set conn=Server.CreateObject("ADODB.connection")
Set rs= Server.CreateObject("ADODB.Recordset")
conn.open database,sql_id,sql_pwd
rs.open sql,conn,3,3
其中公用的调用文件是:
<%
Const database="bookserver"
Const sql_id="sa"
Const sql_pwd="123456"
%>
现在想把数据库文件该为Access 2000,用sql server2000导出功能导出转换为bookserver.mdb文件后,在asp中的打开数据库语句应该怎样该?因为Access2000打开不需要密码,公用文件应该怎样该?
问题点数：30、回复次数：5Top
1 楼playmud（(猪头流氓)(抵制日货)(热烈庆祝火箭输球)）回复于 2002-04-22 09:30:54 得分 6
1. ASP与Access数据库连接：
<%@ language=VBscript%>
<%
dim conn,mdbfile
mdbfile=server.mappath("数据库名称.mdb")
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};uid=admin;pwd=数据库密码;dbq="&mdbfile
%>
Top
2 楼freezwy（网络自由人）回复于 2002-04-22 09:32:39 得分 6
下面将简单介绍一下几种ado连接方式：odbc dsn,odbc dsn-less,
ole db provider,和"ms remote" provider.
1。odbc dsn连接
i.dsn
oconn.open "dsn=advworks;" & _
"uid=admin;" & _
"pwd=;"
注意：从mdac2.1开始就不能够在使用这样的方式了，就是只把dsn文件名放在connectstring中
你必须同时使用dsn，uid，pwd标志。例如下面的方式在mdac 2.1中将会出错：
oconn.open "advworks"
ii.file dsn
oconn.open "filedsn=\somepath\mydb.dsn;" & _
"uid=admin;" & _
"pwd=;"
iii.odbc dsn-less connections
a)odbc text driver
oconn.open _
"driver={microsoft text driver (*.txt; *.csv)};" & _
"dbq=\somepath\;" & _
"extensions=asc,csv,tab,txt;" & _
"persist security info=false"
注意：需要在sql语句中指定使用到的文件名。例如：
ors.open "select * from customer.csv", _
oconn, adopenstatic, adlockreadonly, adcmdtext
b)odbc driver for access
i)普通安全模式：
oconn.open _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"uid=admin;" & _
"pwd=;"
ii)如果使用了system database:
oconn.open _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"systemdb=\somepath\mydb.mdw;", _
"admin", ""
c)odbc driver for sql server
i)普通安全模式
oconn.open "driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=sa;" & _
"pwd=;"
ii)使用信任安全模式：
oconn.open "driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=;" & _
"pwd=;"
注意：要使用空白的uid和pwd
d)odbc driver for oracle
i)使用现有的oracle odbc driver from microsoft:
oconn.open _
"driver={microsoft odbc for oracle};" & _
"server=oracleserver.world;" & _
"uid=demo;" & _
"pwd=demo;"
ii)使用老版本的oracle odbc driver from microsoft:
oconn.open _
"driver={microsoft odbc driver for oracle};" & _
"connectstring=oracleserver.world;" & _
"uid=demo;" & _
"pwd=demo;"
iiii)使用微软的ole db data link connections方式data link file (udl)
a)使用绝对路径
oconn.open "file name=\somepath\pubs.udl;"
b)使用相对路径
oconn.open "file name=pubs.udl;"
v)ole db provider connections方式
a)ole db provider for odbc databases
i)access (jet):
oconn.open _
"provider=msdasql;" & _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"uid=admin;" & _
"pwd=;"
ii)sql server:
oconn.open _
"provider=msdasql;" & _
"driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=sa;" & _
"pwd=;"
b)ole db provider for microsoft jet (access)
i)普通安全模式：
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"user id=admin;" & _
"password=;"
ii)如果使用了system database:
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"jet oledb:system database=system.mdw;", _
"admin", ""
注意：当使用ole db provider4.0版本是，需要把mdb和mdw文件转换成4.0的数据库格式
iii)如果mdb需要一个数据库密码的话：
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"jet oledb:database password=mydbpassword;", _
"admin", ""
c)ole db provider for excel spreadsheet:
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\expenses.xls;" & _
"extended properties=""excel 8.0;hdr=yes;"";"
注意： "hdr=yes"表示在第一行中是行标题，在provider中将不把第一行包括入recordset中
d)ole db provider for sql server
i)普通安全模式：
oconn.open "provider=sqloledb;" & _
"network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"user id=sa;" & _
"password=;"
ii)使用信任安全模式：
oconn.open "provider=sqloledb;" & _
network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"trusted_connection=yes;"
注意："network library=dbmssocn"声明ole db使用tcp/ip替代named pipes.
e)ole db provider for oracle
oconn.open "provider=msdaora;" & _
"data source=oracleserver.world;" & _
"user id=sa;" & _
"password=;"
(vi)remote ole db provider connections方式（就是我一直在研究的rds方式哦，呵呵。）：
a)ms remote - access (jet)
i)odbc dsn:
oconn.open "remote server=http://carl2;" & _
"remote provider=msdasql;" & _
"dsn=advworks;" & _
"uid=admin" & _
"pwd=;"
ii)ole db provider:
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"remote provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;", _
"admin", ""
iii)ole db provider自定义事务对象
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"handler=msdfmap.handler;" & _
"data source=myadvworksoledbconnecttag;"
b)ms remote - sql server
i)odbc dsn:
oconn.open "remote server=http://carl2;" & _
"remote provider=msdasql;" & _
"network library=dbmssocn;" & _
"dsn=pubs;" & _
"uid=sa" & _
"pwd=;"
ii)ole db provider
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"remote provider=sqloledb;" & _
"network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"user id=sa;" & _
"password=;"
===================================Top
3 楼freezwy（网络自由人）回复于 2002-04-22 09:33:09 得分 6
下面将简单介绍一下几种ado连接方式：odbc dsn,odbc dsn-less,
ole db provider,和"ms remote" provider.
1。odbc dsn连接
i.dsn
oconn.open "dsn=advworks;" & _
"uid=admin;" & _
"pwd=;"
注意：从mdac2.1开始就不能够在使用这样的方式了，就是只把dsn文件名放在connectstring中
你必须同时使用dsn，uid，pwd标志。例如下面的方式在mdac 2.1中将会出错：
oconn.open "advworks"
ii.file dsn
oconn.open "filedsn=\somepath\mydb.dsn;" & _
"uid=admin;" & _
"pwd=;"
iii.odbc dsn-less connections
a)odbc text driver
oconn.open _
"driver={microsoft text driver (*.txt; *.csv)};" & _
"dbq=\somepath\;" & _
"extensions=asc,csv,tab,txt;" & _
"persist security info=false"
注意：需要在sql语句中指定使用到的文件名。例如：
ors.open "select * from customer.csv", _
oconn, adopenstatic, adlockreadonly, adcmdtext
b)odbc driver for access
i)普通安全模式：
oconn.open _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"uid=admin;" & _
"pwd=;"
ii)如果使用了system database:
oconn.open _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"systemdb=\somepath\mydb.mdw;", _
"admin", ""
c)odbc driver for sql server
i)普通安全模式
oconn.open "driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=sa;" & _
"pwd=;"
ii)使用信任安全模式：
oconn.open "driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=;" & _
"pwd=;"
注意：要使用空白的uid和pwd
d)odbc driver for oracle
i)使用现有的oracle odbc driver from microsoft:
oconn.open _
"driver={microsoft odbc for oracle};" & _
"server=oracleserver.world;" & _
"uid=demo;" & _
"pwd=demo;"
ii)使用老版本的oracle odbc driver from microsoft:
oconn.open _
"driver={microsoft odbc driver for oracle};" & _
"connectstring=oracleserver.world;" & _
"uid=demo;" & _
"pwd=demo;"
iiii)使用微软的ole db data link connections方式data link file (udl)
a)使用绝对路径
oconn.open "file name=\somepath\pubs.udl;"
b)使用相对路径
oconn.open "file name=pubs.udl;"
v)ole db provider connections方式
a)ole db provider for odbc databases
i)access (jet):
oconn.open _
"provider=msdasql;" & _
"driver={microsoft access driver (*.mdb)};" & _
"dbq=\somepath\mydb.mdb;" & _
"uid=admin;" & _
"pwd=;"
ii)sql server:
oconn.open _
"provider=msdasql;" & _
"driver={sql server};" & _
"server=carl2;" & _
"database=pubs;" & _
"uid=sa;" & _
"pwd=;"
b)ole db provider for microsoft jet (access)
i)普通安全模式：
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"user id=admin;" & _
"password=;"
ii)如果使用了system database:
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"jet oledb:system database=system.mdw;", _
"admin", ""
注意：当使用ole db provider4.0版本是，需要把mdb和mdw文件转换成4.0的数据库格式
iii)如果mdb需要一个数据库密码的话：
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;" & _
"jet oledb:database password=mydbpassword;", _
"admin", ""
c)ole db provider for excel spreadsheet:
oconn.open _
"provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\expenses.xls;" & _
"extended properties=""excel 8.0;hdr=yes;"";"
注意： "hdr=yes"表示在第一行中是行标题，在provider中将不把第一行包括入recordset中
d)ole db provider for sql server
i)普通安全模式：
oconn.open "provider=sqloledb;" & _
"network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"user id=sa;" & _
"password=;"
ii)使用信任安全模式：
oconn.open "provider=sqloledb;" & _
network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"trusted_connection=yes;"
注意："network library=dbmssocn"声明ole db使用tcp/ip替代named pipes.
e)ole db provider for oracle
oconn.open "provider=msdaora;" & _
"data source=oracleserver.world;" & _
"user id=sa;" & _
"password=;"
(vi)remote ole db provider connections方式（就是我一直在研究的rds方式哦，呵呵。）：
a)ms remote - access (jet)
i)odbc dsn:
oconn.open "remote server=http://carl2;" & _
"remote provider=msdasql;" & _
"dsn=advworks;" & _
"uid=admin" & _
"pwd=;"
ii)ole db provider:
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"remote provider=microsoft.jet.oledb.4.0;" & _
"data source=\somepath\mydb.mdb;", _
"admin", ""
iii)ole db provider自定义事务对象
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"handler=msdfmap.handler;" & _
"data source=myadvworksoledbconnecttag;"
b)ms remote - sql server
i)odbc dsn:
oconn.open "remote server=http://carl2;" & _
"remote provider=msdasql;" & _
"network library=dbmssocn;" & _
"dsn=pubs;" & _
"uid=sa" & _
"pwd=;"
ii)ole db provider
oconn.open "provider=ms remote;" & _
"remote server=http://carl2;" & _
"remote provider=sqloledb;" & _
"network library=dbmssocn;" & _
"data source=carl2;" & _
"initial catalog=pubs;" & _
"user id=sa;" & _
"password=;"
===================================Top
4 楼forestli（森林）回复于 2002-04-22 10:10:26 得分 6
1. ASP连接Access数据库语句
Set Conn=Server.CreateObject("ADODB.Connection")
Connstr="DBQ="+server.mappath("aaa/bbspp1.mdb")+";DefaultDir=;DRIVER={Microsoft
AccessDriver(*.mdb)};DriverId=25;FIL=MSAccess;ImplicitCommitSync=Yes;MaxBufferSize=5
12;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
Conn.Open connstr
其中Set Conn=Server.CreateObject("ADODB.Connection")为建立一个访问数据的对象
server.mappath( aaa/bbspp1.mdb)是告诉服务器access 数据库访问的路径
2. ASP连接Sqlserver数据库语句
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open"driver={SQLServer};server=202.108.32.94;uid=wu77445;pwd=p780522;database=w
ww_panwei_com"
conn open
其中/Set conn = Server.CreateObject("ADODB.Connection")为设置一个数据库的连接对象
driver=（）告诉连接的设备名是SQL-SERVER
server是连接的服务器的ip地址，Uid是指用户的用户名，pwd是指的用户的password，
database是用户数据库在服务器端的数据库的名称
Top
