数据库的连接串
在数据库的各种应用程序开发中，连接数据库是数据库应用程序开发的第一步，同时也是最重要的一步。而对于不同的数据库他们的连接模式各有不同，对应的连接串也不同。
程序员可能都有这样的经历，有时不知道连接数据库所需要的连接串究竟如何写或者经常写错而导致不能正确访问数据库。当然很多编程工具能够通过可视化的界面直接产生正确的连接字符串，但字符串中各个参数的具体含义也不清楚，经常混淆。本文就针对大部分常用数据库列举出不同连接方法所需要的连接字符串并加以说明，以便程序员参考！
Sql Server
· ODBC
o 标准连接（Standard Security）:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
1）当服务器为本地时Server可以使用(local);
"Driver={SQL Server};Server=(local);Database=pubs;Uid=sa;Pwd=asdasd;"
2）当连接远程服务器时，需指定地址、端口号和网络库
"Driver={SQL Server};Server=130.120.110.001;Address=130.120.110.001,1052;Network=dbmssocn;Database=pubs;Uid=sa;Pwd=asdasd;"
注：Address参数必须为IP地址，而且必须包括端口号
o 信任连接（Trusted connection）: （Microsoft Windows NT 集成了安全性）
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
或者
"Driver={SQL Server};Server=Aron1;Database=pubs; Uid=;Pwd=;"
o 连接时弹出输入用户名和口令对话框:
Conn.Properties("Prompt") = adPromptAlways
Conn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
· OLE DB, OleDbConnection (.NET)
o 标准连接（Standard Security）:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o 信任连接（Trusted connection）:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(如果连接一个具体的已命名SQLServer实例,使用Data Source=Servere Name/Instance Name;但仅适用于 SQLServer2000)例如：”Provider=sqloledb;Data Source=MyServerName/MyInstanceName;Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;”
o 连接时弹出输入用户名和口令对话框:
Conn.Provider = "sqloledb"
Conn.Properties("Prompt") = adPromptAlways
Conn.Open "Data Source=Aron1;Initial Catalog=pubs;"
o 通过IP地址连接:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP代替Named Pipes, Data Source的末尾是需要使用的端口号(缺省为1433))
· SqlConnection (.NET)
o 标准连接（Standard Security）:
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
或者
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(这两个连接串的结果相同)
o 信任连接（Trusted connection）:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
或者
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(这两个连接串的结果相同)
(可以用serverName/instanceName代替Data Source，取值为一个具体的SQLServer实例,但仅适用于 SQLServer2000)
o 通过IP地址连接:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP代替Named Pipes, Data Source的末尾是需要使用的端口号(缺省为1433))
o SqlConnection连接的声明:
C#:
using System.Data.SqlClient;
SqlConnection SQLConn = new SqlConnection();
SQLConn.ConnectionString="my connectionstring";
SQLConn.Open();
VB.NET:
Imports System.Data.SqlClient
Dim SQLConn As SqlConnection = New SqlConnection()
SQLConn.ConnectionString="my connectionstring"
SQLConn.Open()
· Data Shape
o MS Data Shape
"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
· 更多
o 如何定义使用哪个协议
§ 举例:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
名称 网络协议库
dbnmpntw Win32 Named Pipes
dbmssocn Win32 Winsock TCP/IP
dbmsspxn Win32 SPX/IPX
dbmsvinn Win32 Banyan Vines
dbmsrpcn Win32 Multi-Protocol (Windows RPC)
§ 重要提示
当通过SQLOLEDB提供者进行连接时使用以下语法：
Network Library=dbmssocn
但通过MSDASQL提供者进行连接时使用以下语法：
Network=dbmssocn
o 所有SqlConnection连接串属性
§ 下表显示了ADO.NET SqlConnection对象的所有连接串属性. 其中大多数的属性也在ADO中使用.所有属性和描述来自于msdn.
名称 缺省值 描述
Application Name 应用程序名称或者当没有提供应用程序时为.Net SqlClient数据提供者
AttachDBFilename或者extended properties或者Initial File Name 主要文件的名字，包括相关联数据库的全路径。数据库名字必须通过关键字'database'来指定。
Connect Timeout或者Connection Timeout 15 在中止连接请求，产生错误之前等待服务器连接的时间（以秒为单位）
Connection Lifetime 0 当一个连接返回到连接池，当前时间与连接创建时间的差值，如果时间段超过了指定的连接生存时间，此连接就被破坏。它用于聚集设置中在运行服务器和准备上线的服务器之间强制负载平衡。
Connection Reset 'true' 当连接从连接池移走时决定是否重置数据库连接。当设置为'false'时用于避免获得连接时的额外服务器往复代价。
Current Language SQL Server语言记录名称
Data Source或Server或Address或Addr或Network Address 要连接的SQL Server实例的名字或者网络地址
Enlist 'true' 为真时，连接池自动列出创建线程的当前事务上下文中的连接。
Initial Catalog或Database 数据库名
Integrated Security或者Trusted_Connection 'false' 连接是否为信任连接。其取值为'true'， 'false'和'sspi'（等于'true'）.
Max Pool Size 100 连接池中允许的最大连接数
Min Pool Size 0 连接池中允许的最小连接数
Network Library或Net 'dbmssocn' 网络库用于建立与一个 SQL Server实例的连接。值包括dbnmpntw (命名管道), dbmsrpcn (多协议), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (共享内存) 和 dbmsspxn (IPX/SPX), 和 dbmssocn (TCP/IP). 所连接的系统必须安装相应的动态链接库。如果你没有指定网络，当你使用一个局部的服务器 (例如, "." 或者 "(local)"),将使用共享内存
Packet Size 8192 与 SQL Server的一个实例通讯的网络包字节大小
Password-或Pwd SQL Server帐户登录口令
Persist Security Info 'false' 设置为'false',当连接已经打开或者一直处于打开状态时，敏感性的安全信息 （如口令）不会返回作为连接的一部分信息。
Pooling 'true' 为真时,从合适的连接池中取出SQLConnection对象,或者必要时创建SQLConnection对象并把它增加到合适的连接池中。
User ID SQL Server登录用户
Workstation ID the local computer name 连接到SQL Server的工作站名称
§ 注意：
使用分号分隔每个属性
如果一个名字出现多于两次，在连接串中的最后一次出现的值将被使用。
如果你通过在应用中由用户输入字段的值来构建连接串，你必须保证用户不会通过用户值里的另一个值插入到一个额外的属性来改变连接串。
Access
· ODBC
o 标准连接（Standard Security）:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Uid=Admin;Pwd=;"
o 组（系统数据库）连接 （Workgroup）:
两种方法分别为：在连接串或在打开数据集中指定用户名和口令
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;SystemDB=C:/mydatabase.mdw;","admin", ""
或
if(pDB.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;SystemDB=C:/mydatabase.mdw;", "", "DatabaseUser", "DatabasePass"))
{DoSomething();
pDB.Close();
}
o 独占方式（Exclusive）:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
· OLE DB, OleDbConnection (.NET)
o 标准连接（Standard Security）:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;User Id=admin;Password=;"
o 组连接 （系统数据库）
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", ""
o 带口令的连接:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;","admin", ""
· Oracle
· ODBC
o 新版本:
"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
o 旧版本:
"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"
· OLE DB, OleDbConnection (.NET)
o 标准连接（Standard Security）:
"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
这是Microsoft的格式, 下面是Oracle的格式（提供者不同）
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"
注意："Data Source=" 必须根据相应的命名方法设置为Net8名称。例如对于局部命名，它是tnsnames.ora中的别名，对于Oracle命名，它是Net8网络服务名
o 信任连接（Trusted Connection）:
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"
或者设置user ID为 "/"
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=/;Password=;"
· OracleConnection (.NET)
o 标准连接:
"Data Source=Oracle8i;Integrated Security=yes";
这只对Oracle 8i release 3或更高版本有效
o OracleConnection声明:
C#:
using System.Data.OracleClient;
OracleConnection OracleConn = new OracleConnection();
OracleConn.ConnectionString = "my connectionstring";
OracleConn.Open();
VB.NET:
Imports System.Data.OracleClient
Dim OracleConn As OracleConnection = New OracleConnection()
OracleConn.ConnectionString = "my connectionstring"
OracleConn.Open()
· Data Shape
o MS Data Shape:
"Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;user id=username;password=mypw"
· MySQL
· ODBC
o 本地数据库:
"Driver={mySQL};Server=mySrvName;Option=16834;Database=mydatabase;"
Sever参数也可以使用localhost作为其值
o 远程数据库:
"Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
Option值 取值的含义
1 客户端不能处理MyODBC返回一列真实宽度的情况
2 客户端不能处理MySQL返回影响的行的真实值的情况如果设置此标志，MySQL返回’found rows’。 MySQL 3.21.14或更新版才能生效
4 在c:/myodbc.log中生成一个调试日志。这与在`AUTOEXEC.BAT'中的设置MYSQL_DEBUG=d:t:O,c::/myodbc.log相同
8 对于结果和参数不设置任何包限制
16 不使驱动器弹出问题
32 使用或去除动态游标支持。这在MyODBC 2.50中是不允许的
64 在'database.table.column'中忽略数据库名字的使用
128 强制使用ODBC管理器游标
256 去除扩展取值（fetch）的使用
512 充满char字段满长度
1024 SQLDescribeCol()函数返回完全满足条件的列名
2048 使用压缩的服务器/客户端协议
4096 告诉服务器在函数后和'('前忽略空格 (PowerBuilder需要)。这将产生所有函数名关键词
8192 使用命名管道连接运行在NT上的Mysqld服务器
16384 将LONGLONG列改为INTl列（有些应用不能处理LONGLONG列）
32768 从SQLTables中返回’user’作为Table_qualifier和Table_owner
66536 从客户端读参数，从`my.cnf'读ODBC群
131072 增加一些额外的安全性检查
如果你需要许多选项，你应该把以上标志相加。
· OLE DB, OleDbConnection (.NET)
o 标准连接:
"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"
Data Source是MySQL数据库的名字，也可以使用server=localhost;DB=test
· MySqlConnection (.NET)
o eInfoDesigns.dbProvider:
"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"
只适用于 eInfoDesigns dbProvider, 附加到 .NET
o MySqlConnection的声明:
C#:
using eInfoDesigns.dbProvider.MySqlClient;
MySqlConnection MySqlConn = new MySqlConnection();
MySqlConn.ConnectionString = "my connectionstring";
MySqlConn.Open();
VB.NET:
Imports eInfoDesigns.dbProvider.MySqlClient
Dim MySqlConn As MySqlConnection = New MySqlConnection()
MySqlConn.ConnectionString = "my connectionstring"
MySqlConn.Open()
· Interbase
· ODBC, Easysoft
o 本地计算机:
"Driver={Easysoft IB6 ODBC};Server=localhost;Database=localhost:C:/mydatabase.gdb;Uid=username;Pwd=password"
o 远程计算机:
"Driver={Easysoft IB6 ODBC};Server=ComputerName;Database=ComputerName:C:/mydatabase.gdb;Uid=username;Pwd=password"
· ODBC, Intersolv
o 本地计算机:
"Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=localhost;Database=localhost:C:/mydatabase.gdb;Uid=username;Pwd=password"
o 远程计算机:
"Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=ComputerName;Database=ComputerName:C:/mydatabase.gdb;Uid=username;Pwd=password"
这个驱动器现在由 DataDirect Technologies来提供 (以前由Intersolv提供)
· OLE DB, SIBPROvider
o 标准连接:
"provider=sibprovider;location=localhost:;data source=c:/databases/gdbs/mygdb.gdb;user id=SYSDBA;password=masterkey"
o 指定字符集:
"provider=sibprovider;location=localhost:;data source=c:/databases/gdbs/mygdb.gdb;user id=SYSDBA;password=masterkey;character set=ISO8859_1"
o 指定角色:
"provider=sibprovider;location=localhost:;data source=c:/databases/gdbs/mygdb.gdb;user id=SYSDBA;password=masterkey;role=DIGITADORES"
· 需要了解更多请链接到Interbase的如下Borland开发者网络文章
数据库的连接串 http://community.borland.com/article/0,1410,27152,00.html
· IBM DB2
· OLE DB, OleDbConnection (.NET) from ms
o TCP/IP:
"Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"
o APPC:
"Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"
AS400
· ODBC
" Driver={Client Access ODBC Driver (32-bit)};System=myAS400;Uid=myUsername;Pwd=myPassword"
· OLE DB, OleDbConnection (.NET)
"Provider=IBMDA400;Data source=myAS400;User Id=myUsername;Password=myPassword;"
· Sybase
· ODBC
o Sybase System 12 (或 12.5) Enterprise Open Client标准连接:
"Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"
o Sybase System 11标准连接:
"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;"
o Intersolv 3.10:
"Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"
o Sybase SQL Anywhere (以前为Watcom SQL ODBC driver):
"ODBC; Driver=Sybase SQL Anywhere 5.0; DefaultDir=c:/dbfolder/;Dbf=c:/mydatabase.db;Uid=username;Pwd=password;Dsn="""""
注意： 对于在末尾紧跟着DSN 参数被引用的双引号(VB语法), 你必须改变符合你所使用的语言所指定的引用符号语法。DSN参数为空实际上非常重要，如果不包括的话将导致7778错误。
· OLE DB
o Adaptive Server Anywhere (ASA):
"Provider=ASAProv;Data source=myASA"
o Adaptive Server Enterprise (ASE)（带数据源为.IDS文件）:
"Provider=Sybase ASE OLE DB Provider; Data source=myASE"
注意你必须使用数据管理器来创建一个数据源.IDS文件.这些.IDS 文件类似于ODBC DSNs.
Visual FoxPro（dBASE）
· ODBC
o dBASE:
" Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=c://DatabasePath"
注意在书写sql语句时必须指定文件名（如“Select Name, Address From Clients.dbf”）.
o Visual Foxpro(有数据库容器):
" Driver={Microsoft Visual Foxpro Driver};UID=; SourceType=DBC;SourceDB=C://DatabasePath//MyDatabase.dbc;Exclusive=No "
o Visual Foxpro(无数据库容器):
" Driver={Microsoft Visual Foxpro Driver};UID=; SourceType=DBF;SourceDB=C://DatabasePath//MyDatabase.dbc;Exclusive=No"
· OLE DB, OleDbConnection (.NET)
o 标准连接:
"Provider=vfpoledb;Data Source=C://DatabasePath//MyDatabase.dbc;"
Excel
· ODBC
o 标准连接（Standard Security）:
" Driver={Microsoft Excel Driver (*.xls)};DriverId=790; Dbq =C://DatabasePath//DBSpreadSheet.xls;DefaultDir=c://databasepath;"
· OLE DB Provider for Microsoft Jet
o 标准连接（Standard Security）:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C://DatabasePath//DBSpreadSheet.xls;Extended Properties=/"/"Excel 8.0;HDR=Yes;/"/";"
注意：如果"HDR=Yes"，那么提供者不会在记录集中包括选择的第一行，如果，那么提供者将在记录集中包括单元范围（或已经命名范围）的第一行
Text
· ODBC
o 标准连接（Standard Security）:
" Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=C://DatabasePath//;Extensions=asc,csv,tab,txt;"
如果文本文件使用tab作为分隔符，你必须创建schema.ini文件，你必须在连接串中使用Format=TabDelimited选项。
注意：你必须在sql语句中指定文件名（例如"Select Name, Address From Clients.csv"）
· OLE DB Provider for Microsoft Jet
o 标准连接（Standard Security）:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C://DatabasePath//;Extended Properties=/"/"text;"HDR=Yes;FMT=Delimited;/"/";"
注意在书写sql语句时必须指定文件名（如“Select Name, Address From Clients.txt”）.
ODBC DSN
" DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;"
OLE DB Provider for ODBC Databases
连接Access:
"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};Dbq=c://DatabasePath//MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;"
连接SQL Server:
"Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;"
使用DSN进行连接:
"Provider=MSDASQL;PersistSecurityInfo=False;Trusted_Connection=Yes;Data Source=MyDSN;catalog=MyDatabase;"
OLE DB Provider for OLAP
"Provider=MSOLAP;Data Source=MyOLAPServerName;Initial Catalog=MyOLAPDatabaseName;"
通过http连接:
这个特征允许客户端应用程序通过在客户端应用程序连接字符串的Data Source参数中指定一个URL并使用IIS连接到一个分析服务器。这种连接方法允许PivotTable服务通过防火墙或者代理服务器连接到分析服务器。一个特殊的ASP页面Msolap.asp通过IIS进行连接。当连接到服务器时，这个文件一定在这个目录中并且作为URL的一部分(例如
数据库的连接串 http://www.myserver.com/myolap/)。
使用url连接:
"Provider=MSOLAP;Data Source=http://MyOLAPServerName/;Initial Catalog=MyOLAPDatabaseName;"
使用ssl连接:
"Provider=MSOLAP;Data Source=https://MyOLAPServerName/;Initial Catalog=MyOLAPDatabaseName;"
OLE DB Provider for Active Directory
"Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;"
OLE DB Provider for Index Server
"provider=msidxs;Data Source=MyCatalog;"
OLE DB Data Link Connections
"File Name=c://DataBasePath//DatabaseName.udl;"
Outlook 2000 personal mail box
"Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;MAPILEVEL=;DATABASE=C://Temp//;"
使用任何临时文件夹代替c:/temp。这将在那个文件夹创建一个模式文件，当你打开它时，它会显示可以得到的所有字段。空的mapilevel表明最高层的文件夹。
Exchange mail box
"Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Mailbox - Pat Smith|;DATABASE=C://Temp//;"
使用任何临时文件夹代替c:/temp.
注意：你能象数据库一样对mail store输入查询
举例："SQL "SELECT Contacts.* FROM Contacts;"
数据库的连接串
<script language=JavaScript> </script> <script language=JavaScript> </script> 数据库的连接串 OLE DB Provider for Active Directory Service OLE DB Provider for Advantage OLE DB Provider for AS/400 (from IBM) OLE DB Provider for AS/400 and VSAM (from Microsoft) OLE DB Provider for Commerce Server OLE DB Provider for DB2 OLE DB Provider for DTS Packages OLE DB Provider for Exchange OLE DB Provider for Excel OLD DB Provider for Internet Publishing OLE DB Provider for Index Server OLE DB Provider for Microsoft Jet OLE DB Provider for Microsoft Project OLE DB Provider for MySQL OLE DB Provider for ODBC Databases OLE DB Provider for OLAP Services OLE DB Provider for Oracle (from Microsoft) OLE DB Provider for Oracle (from Oracle) OLE DB Provider for Pervasive OLE DB Provider for Simple Provider OLE DB Provider for SQLBase OLE DB Provider for SQL Server OLE DB Provider for SQL Server via SQLXMLOLEDB OLE DB Provider for Sybase Adaptive Server Anywhere OLE DB Provider for Sybase Adaptive Server Enterprise OLE DB Provider for Text Files OLE DB Provider for UniData and UniVerse OLE DB Provider for Visual FoxPro OLE DB Provider for Active Directory Service oConn.Open "Provider=ADSDSOObject;" & _
"User Id=myUsername;" & _
"Password=myPassword" For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service
To view Microsoft KB articles related to Data Link File, click here
OLE DB Provider for Advantage oConn.Open "Provider=Advantage OLE DB Provider;" & _
"Data source=c:/myDbfTableDir;" & _
"ServerType=ADS_LOCAL_SERVER;" & _
"TableType=ADS_CDX" OLE DB Provider for AS/400 (from IBM) oConn.Open "Provider=IBMDA400;" & _
"Data source=myAS400;" & _
"User Id=myUsername;" & _
"Password=myPassword" OLE DB Provider for AS/400 and VSAM (from Microsoft) oConn.Open "Provider=SNAOLEDB;" & _
"Data source=myAS400;" & _
"User Id=myUsername;" & _
"Password=myPassword" For more information, see: ConnectionString Property
To view Microsoft KB articles related to OLE DB Provider for AS/400 and VSAM, click here
OLE DB Provider for Commerce Server For Data Warehouse oConn.Open "Provider=Commerce.DSO.1;" & _
"Data Source=mscop://InProcConn/Server=mySrvName:" & _
"Catalog=DWSchema:Database=myDBname:" & _
"User=myUsername:Password=myPassword:" & _
"FastLoad=True"
' Or
oConn.Open "URL=mscop://InProcConn/Server=myServerName:" & _
"Database=myDBname:Catalog=DWSchema:" & _
"User=myUsername:Password=myPassword:" & _
"FastLoad=True"
For Profiling System oConn.Open "Provider=Commerce.DSO.1;" & _
"Data Source=mscop://InProcConn/Server=mySrvName:" & _
"Catalog=Profile Definitions:Database=myDBname:" & _
"User=myUsername:Password=myPassword"
' Or
oConn.Open _
"URL=mscop://InProcConnect/Server=myServerName:" & _
"Database=myDBname:Catalog=Profile Definitions:" & _
"User=myUsername:Password=myPassword" For more information, see: OLE DB Provider for Commerce Server, DataWarehouse, and Profiling System
To view Microsoft KB articles related to OLE DB Provider for Commerce Server, click here
OLE DB Provider for DB2 (from Microsoft) For TCP/IP connections oConn.Open = "Provider=DB2OLEDB;" & _
"Network Transport Library=TCPIP;" & _
"Network Address=xxx.xxx.xxx.xxx;" & _
"Initial Catalog=MyCatalog;" & _
"Package Collection=MyPackageCollection;" & _
"Default Schema=MySchema;" & _
"User ID=MyUsername;" & _
"Password=MyPassword"
For APPC connections oConn.Open = "Provider=DB2OLEDB;" & _
"APPC Local LU Alias=MyLocalLUAlias;" & _
"APPC Remote LU Alias=MyRemoteLUAlias;" & _
"Initial Catalog=MyCatalog;" & _
"Package Collection=MyPackageCollection;" & _
"Default Schema=MySchema;" & _
"User ID=MyUsername;" & _
"Password=MyPassword" For more information, see: ConnectionString Property, and Q218590
To view Microsoft KB articles related to OLE DB Provider for DB2, click here
OLE DB Provider for DTS Packages The Microsoft OLE DB Provider for DTS Packages is a read-only provider that exposes Data Transformation Services Package Data Source Objects. oConn.Open = "Provider=DTSPackageDSO;" & _
"Data Source=mydatasource"
For more information, see: OLE DB Providers Tested with SQL Server
To view Microsoft KB articles related to OLE DB Provider for DTS Packages, click here
OLE DB Provider for Exchange oConn.Provider = "EXOLEDB.DataSource"
oConn.Open = "http://myServerName/myVirtualRootName" For more information, see: Exchange OLE DB Provider, Messaging, Calendaring, Contacts, and Exchange using ADO objects
To view Microsoft KB articles related to OLE DB Provider for Exchange, click here
OLE DB Provider for Excel Currently Excel does not have an OLE DB Provider.
However, you can use the ODBC Driver for Excel
. Or use the OLE DB Provider for JET to read and write data
in an Excel workbook.
OLE DB Provider for Index Server oConn.Open "Provider=MSIDXS;" & _
"Data source=MyCatalog"
For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service
To view Microsoft KB articles related to OLE DB Provider for Index Server, click here
OLE DB Provider for Internet Publishing oConn.Open "Provider=MSDAIPP.DSO;" & _
"Data Source=http://mywebsite/myDir;" & _
"User Id=myUsername;" & _
"Password=myPassword" oConn.Open "URL=http://mywebsite/myDir;" & _
"User Id=myUsername;" & _
"Password=myPassword" For more information, see: Microsoft OLE DB Provider for Internet Publishing and Q245359
To view Microsoft KB articles related to OLE DB Provider for Internet Publishing, click here
OLE DB Provider for Microsoft Jet For standard security oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/myDb.mdb;" & _
"User Id=admin;" & _
"Password="
If using a Workgroup (System Database) oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword" Note, remember to convert both the MDB and the MDW to the 4.0
database format when using the 4.0 OLE DB Provider.
If MDB has a database password oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/mydb.mdb;" & _
"Jet OLEDB:Database Password=MyDbPassword", _
"myUsername", "myPassword"
If want to open up the MDB exclusively oConn.Mode = adModeShareExclusive
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/myDb.mdb;" & _
"User Id=admin;" & _
"Password="
If MDB is located on a network share oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=//myServer/myShare/myPath/myDb.mdb"
If MDB is located on a remote machine - Or use an XML Web Service via SOAP Toolkit or ASP.NET
- Or upgrade to SQL Server and use an IP connection string
- Or use an ADO URL with a remote ASP web page
- Or use a MS Remote or RDS connection string
If you don't know the path to the MDB (using ASP) <% ' ASP server-side code
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(".") & "/myDb.mdb;" & _
"User Id=admin;" & _
"Password="
%> If you don't know the path to the MDB (using VB) oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "/myDb.mdb;" & _
"User Id=admin;" & _
"Password=" This assumes the MDB is in the same directory where the application is running.
For more information, see: OLE DB Provider for Microsoft Jet, Q191754, and Q225048
Note: Microsoft.Jet.OLEDB.3.51 only gets installed by MDAC 2.0. Q197902
Note: MDAC 2.6 and 2.7 do not contain any of the JET components. Q271908 and Q239114
To view Microsoft KB articles related to OLE DB Provider for Microsoft JET, click here
You can also open an Excel Spreadsheet using the JET OLE DB Provider oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/mySpreadsheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes""" Where "HDR=Yes" means that there is a header row in the cell range
(or named range), so the provider will not include the first row of the
selection into the recordset. If "HDR=No", then the provider will include
the first row of the cell range (or named ranged) into the recordset.
For more information, see: Q278973
You can also open a Text file using the JET OLE DB Provider oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:/somepath/;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited""" oRs.Open "Select * From MyTextFile.txt", oConn, _
adOpenStatic, adLockReadOnly, adCmdText OLE DB Provider for Microsoft Project oConn.Open "Provider=Microsoft.Project.OLEDB.9.0;" & _
"Project Name=c:/somepath/myProject.mpp" For more information, see: Microsoft Project 2000 OLE DB Provider Information
To view Microsoft KB articles related to OLE DB Provider for Microsoft Project, click here
OLE DB Provider for mySQL oConn.Open "Provider=MySQLProv;" & _
"Data Source=mySQLDB;" & _
"User Id=myUsername;" & _
"Password=myPassword" OLE DB Provider for ODBC Databases WARNING : This OLE DB Provider is considered obsolete by Microsoft! For Access (Jet) oConn.Open "Provider=MSDASQL;" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:/somepath/mydb.mdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For SQL Server oConn.Open "Provider=MSDASQL;" & _
"Driver={SQL Server};" & _
"Server=myServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword" For more information, see: Microsoft OLE DB Provider for ODBC
To view Microsoft KB articles related to OLE DB Provider for ODBC, click here
OLE DB Provider for OLAP Services Microsoft OLE DB for Online Analytical Processing (OLAP) is a set of
objects and interfaces that extends the ability of OLE DB to provide
access to multidimensional data stores.
For ADOMD.Catalog oCat.ActiveConnection = _
"Provider=MSOLAP;" & _
"Data Source=myOLAPServerName;" & _
"Initial Catalog=myOLAPDatabaseName"
For ADOMD.Catalog (with URL) oCat.ActiveConnection = _
"Provider=MSOLAP;" & _
"Data Source=http://myServerName/;" & _
"Initial Catalog=myOLAPDatabaseName"
For Excel PivotTable With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;" & _
"Provider=MSOLAP;" & _
"Location=myServerDataLocation;" & _
"Initial Catalog=myOLAPDatabaseName"
.MaintainConnection = True
.CreatePivotTable TableDestination:=Range("A1"), _
TableName:= "MyPivotTableName"
End With
For more information, see: OLE DB for OLAP, Catalog Object, PivotTable, Connecting Using HTTP
To view Microsoft KB articles related to OLE DB Provider for OLAP Services, click here
OLE DB Provider for Oracle (from Microsoft) oConn.Open "Provider=msdaora;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword" For more information, see: Microsoft OLE DB Provider for Oracle
To view Microsoft KB articles related to OLE DB Provider for Oracle, click here
OLE DB Provider for Oracle (from Oracle) For Standard Security oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"
For a Trusted Connection oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=/;" & _
"Password="
' Or oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"OSAuthent=1" Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.
For more information, see: Oracle Provider for OLE DB Developer's Guide
OLE DB Provider for Pervasive oConn.Open "Provider=PervasiveOLEDB;" & _
"Data Source=C:/PervasiveEB" OLE DB Provider for Simple Provider The Microsoft OLE DB Simple Provider (OSP) allows ADO to access any data for which a provider has been written using the OLE DB Simple Provider Toolkit. Simple providers are intended to access data sources that require only fundamental OLE DB support, such as in-memory arrays or XML documents.
OSP in MDAC 2.6 has been enhanced to support opening hierarchical ADO Recordsets over arbitrary XML files. These XML files may contain the ADO XML persistence schema, but it is not required. This has been implemented by connecting the OSP to the MSXML2.DLL, therefore MSXML2.DLL or newer is required.
oConn.Open "Provider=MSDAOSP;" & _
"Data Source=MSXML2.DSOControl.2.6"
oRS.Open "http://WebServer/VirtualRoot/MyXMLFile.xml",oConn For more information, see: Microsoft OLE DB Simple Provider and Q272270
To view Microsoft KB articles related to OLE DB Provider for Simple Provider, click here
OLE DB Provider for SQLBase oConn.Open "Provider=SQLBaseOLEDB;" & _
"Data source=mySybaseServer;" & _
"Location=mySybaseDB;" & _
"User Id=myUserName;" & _
"Password=myUserPassword" OLE DB Provider for SQL Server For Standard Security oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
For a Trusted Connection oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
To connect to a "Named Instance" oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName/myInstanceName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword" To Prompt user for username and password oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName"
To connect to SQL Server running on the same computer oConn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
To connect to SQL Server running on a remote computer (via an IP address) oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword" Where:
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption
For more information, see: Microsoft OLE DB Provider for SQL Server
To view Microsoft KB articles related to OLE DB Provider for SQL Server, click here
OLE DB Provider for SQL Server via SQLXMLOLEDB The SQLXMLOLEDB provider is an OLE DB provider that exposes the Microsoft SQLXML functionality through ADO. The SQLXMLOLEDB provider is not a rowset provider; it can only execute commands in the "write to an output stream" mode of ADO. oConn.Open "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" & _
"Data Source=mySqlServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUserName;" & _
"Password=myUserPassword" For more information, see: SQLXML 3.0 and A Survey of Microsoft SQL Server 2000 XML Features
To view Microsoft KB articles related to OLE DB Provider for SQL Server via SQLXMLOLEDB, click here
OLE DB Provider for Sybase Adaptive Server Anywhere (ASA) oConn.Open "Provider=ASAProv;" & _
"Data source=myASA" OLE DB Provider for Sybase Adaptive Server Enterprise (ASE) oConn.Open "Provider=Sybase ASE OLE DB Provider;" & _
"Data source=myASEServer" ' Or oConn.Open "Provider=Sybase.ASEOLEDBProvider;" & _
"Srvr=myASEServer,5000;" & _
"Catalog=myDBName;" & _
"User Id=myUserName;" & _
"Password=myUserPassword" Where:
- The Sybase ASE OLE DB provider from the Sybase 12.5 client CD
- 5000 is the port number for Sybase.
Note: The Open Client 12 Sybase OLE DB Provider fails to work without creating a Data Source .IDS file using the Sybase Data Administrator. These .IDS files resemble ODBC DSNs.
Note: With Open Client 12.5, the server port number feature finally works, allowing fully qualified network connection strings to be used without defining any .IDS Data Source files.
For more information, see: Opening Sybase databases
OLE DB Provider for Text Files Actually there is no OLE DB Provider for Text files. However, you can use the OLE DB Provider for JET to read and write data in Text files. Or you can use the ODBC Driver for Text. OLE DB Provider for UniData and UniVerse oConn.Open "Provider=Ardent.UniOLEDB;" & _
"Data source=myServer;" & _
"Location=myDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword" OLE DB Provider for Visual FoxPro oConn.Open "Provider=vfpoledb;" & _
"Data Source=C:/vfp8/Samples/Data/myVFPDB.dbc;" & _
"Mode=ReadWrite|Share Deny None;" & _
"Collating Sequence=MACHINE;" & _
"Password=''" The Visual FoxPro OLE DB Provider is not installed by MDAC 2.x. You must install Visual FoxPro or Microsoft OLE DB Provider for Visual FoxPro
To view Microsoft KB articles related to OLE DB Provider for Visual FoxPro, click here.
数据库的连接串 Microsoft
Most Valuable Professional
(MVP)
数据库的连接串 数据库的连接串
Questions or comments about this web site, please send email to: WebMaster Copyright © 1997 - 2005 Able Consulting, Inc. Terms Of Use Last Modified: Saturday, February 19, 2005 11:53:27 PM
