ADO Conection String

如果你是用 ADO 來連線資料庫的話，這些應該會常用到！要記起來！

• 推薦
• |
• << 上一篇
•  
• 下一篇 >>
• |
• 分享
•  
• 關於

連線字串如下

• 1.

  ODBC Driver for dBASE

  1	"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\DatabasePath;"

  
  

  查詢使用:

  1	Select Name, Address From Clients.dbf

• 2.

  ODBC Driver for Excel

  1	"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;bq=C:\DatabasePath\DBSpreadSheet.xls;DefaultDir=c:\databasepath;"

  
  

  ODBC Driver for Text

  1	"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\DatabasePath\;Extensions=asc,csv,tab,txt;"

  
  

  查詢使用:

  1	Select Name, Address From Clients.csv

  
  
• 3.

  Visual FoxPro

  1	"Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBC;SourceDB=C:\DatabasePath\MyDatabase.dbc;Exclusive=No"

  如果沒有使用 .DBC (Database container) 的話則是:

  1	"Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=C:\DatabasePath\MyDatabase.dbc;Exclusive=No"

• 4.

  ODBC Driver for Access

  1	"Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\DatabasePath\dbaccess.mdb;Uid=;Pwd=;"

  或

  1	"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\VC Projects\ADO\Samples\AdoTest\dbTestSecurity.mdb;SystemDB=C:\Program Files\Microsoft Office\Office\SYSTEM.mdw;Uid=Carlos Antollini;Pwd=carlos"

  獨佔模式:

  1	"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\VC Projects\ADO\Samples\AdoTest\dbTestSecurity.mdb;SystemDB=C:\Program Files\Microsoft Office\Office\SYSTEM.mdw;Uid=Carlos Antollini;Pwd=carlos"

  
  
• 5.

  ODBC Driver for SQL Server

  標準模式:

  1	"Driver={SQL Server};Server=MyServerName;Trusted_Connection=no;Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;"

  信任模式:

  1	"Driver={SQL Server};Server=MyServerName;Database=myDatabaseName;Uid=;Pwd=;"

  或

  1	"Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;Trusted_Connection=yes;"

  若為本機執行

  1	"Driver={SQL Server};Server=(local);Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;"

  遠端主機可以這樣:

  1	"Driver={SQL Server};Server=130.120.110.001;Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;Uid=myUsername;Pwd=myPassword;"

  
  
• 6.

  ODBC Driver for Oracle

  1	"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=MyUsername;Pwd=MyPassword;"

  舊版的 Oracle Driver:

  1	"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

  
  
• 7.

  ODBC Driver for MySQL

  1	Driver={MySQL ODBC 3.51 Driver};Server=localhost;"Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;"

  遠端連線:

  1	"Driver={mySQL ODBC 3.51 Driver};Server=MyRemoteHost;Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;"

• 8.

  ODBC DSN

  這應該是最簡單的！

  1	"DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;"

  若是有使用 OLE DB 的話

  
  

  OLE DB Provider for SQL Server

  1	"Provider=sqloledb;Data Source=MyServerName;Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;"

  信任連線:

  1	"Provider=sqloledb;Data Source=MyServerName;Initial Catalog=MyDatabaseName;Integrated Security=SSPI;"

  不同的 Named instance:

  1	"Provider=sqloledb;Data Source=MyServerName\MyInstanceName;Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;"

  本機連線

  1	"Provider=sqloledb;Data Source=(local);Initial Catalog=myDatabaseName;User ID=myUsername;Password=myPassword;"

  遠端主機

  1	"Provider=sqloledb;Network Library=DBMSSOCN;Data Source=130.120.110.001,1433;Initial Catalog=MyDatabaseName;User ID=MyUsername;Password=MyPassword;"

  OLE DB Provider for MySQL

  1	"Provider=MySQLProv;Data Source=test"

  OLE DB Provider for AS400

  1	"Provider=IBMDA400;Data source=myAS400;User Id=myUsername;Password=myPassword;

  OLE DB Provider for Active Directory

  1	"Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;"

  OLE DB Provider for DB2

  1	"Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=130.120.110.001;Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;"

  使用 APPC 連線:

  1	"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;"

  OLE DB Provider for Microsoft Jet - Access

  1	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabasePath\MmDatabase.mdb;User Id=admin;Password=;"

  如果使用 Workgroup:

  1	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataBasePath\mydb.mdb;Jet OLEDB:System Database=MySystem.mdw;"

  OLE DB Provider for Microsoft Jet - Excel

  1	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DatabasePath\\DBSpreadSheet.xls;Extended Properties="Excel 8.0;HDR=Yes;"

  OLE DB Provider for Microsoft Jet - Text File

  1	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabasePath\;Extended Properties="text";HDR=Yes;FMT=Delimited;

  OLE DB Provider for Microsoft Jet - Outlook

  1	"Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;MAPILEVEL=;DATABASE=C:\Temp\;"

  OLE DB Provider for Microsoft Jet - Exchange

  1	"Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Mailbox - Pat Smith|;DATABASE=C:\Temp\;"

  OLE DB Provider for ODBC Databases - Access

  1	"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\DatabasePath\MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;"

  OLE DB Provider for ODBC Databases - SQL Server

  1	"Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;"

  使用 DSN:

  1	"Provider=MSDASQL;PersistSecurityInfo=False;Trusted_Connection=Yes;Data Source=MyDSN;catalog=MyDatabase;"

  OLE DB Provider for OLAP

  1	"Provider=MSOLAP;Data Source=MyOLAPServerName;Initial Catalog=MyOLAPDatabaseName;"

  使用 HTTP 連線:

  1	"Provider=MSOLAP;Data Source=http://MyOLAPServerName/;Initial Catalog=MyOLAPDatabaseName;"

  SSL:

  1	"Provider=MSOLAP;Data Source=https://MyOLAPServerName/;Initial Catalog=MyOLAPDatabaseName;"

  OLE DB Provider for Oracle (Micorsoft)

  1	"Provider=MSDAORA;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"

  OLE DB Provider for Oracle (Oracle)

  1	"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"

  信任連線:

  1	"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=/;Password=;"

  以 OSAuthent 認證:

  1	"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"

  OLE DB Provider for Visual FoxPro

  1	"Provider=vfpoledb;Data Source=C:\DatabasePath\MyDatabase.dbc;"

• 9.

  其他較少用到的

  
  

  ODBC Driver for AS400

  1	"Driver={Client Access ODBC Driver (32-bit)};System=myAS400;Uid=myUsername;Pwd=myPassword;"

  
  

  ODBC Driver for SyBase

  1	"Driver={Sybase System 10};Srvr=MyServerName;Uid=MyUsername;Pwd=myPassword;"

  
  

  ODBC Driver for Sybase SQL AnyWhere

  1	ODBC;Driver=Sybase SQL Anywhere 5.0;DefaultDir=C:\DatabasePath\;Dbf=C:\SqlAnyWhere50\MyDatabase.db;Uid=MyUsername;Pwd=MyPassword;Dsn="";

  
  

• 參考網址：http://www.codeproject.com/Articles/2304/ADO-Connection-Strings
  圖片引用來源：http://encyclopedia2.thefreedictionary.com/ADODB