常用ADO连接数据库字符串大全文档格式.docx

1、ODBC Driver for ExcelODBC Driver for MySQLODBC Driver for OracleODBC Driver for ParadoxODBC Driver for SQL ServerODBC Driver for SybaseODBC Driver for Sybase SQL AnywhereODBC Driver for TextODBC Driver for TeradataODBC Driver for Visual FoxProOLE DB Data Link Connections Data Link File （UDL）OLE DB D

2、ata Provider Connections OLE DB Provider for AS/400OLE DB Provider for Active Directory ServiceOLE DB Provider for DB2OLD DB Provider for Internet PublishingOLE DB Provider for Index ServerOLE DB Provider for Microsoft JetOLE DB Provider for ODBC DatabasesOLE DB Provider for Oracle （From Microsoft）O

3、LE DB Provider for Oracle （From Oracle）OLE DB Provider for Simple ProviderOLE DB Provider for SQL ServerRemote Data Service （RDS） Connections RDS Data Control - Connect PropertyRDS Data Control - URL PropertyADO URL Connections ADO RecordsetMS Remote Provider Connections MS Remote - Access （Jet）MS R

4、emote - SQL ServerData Shape Provider Connections MS DataShape - SQL Server.NET Managed Provider Connections SQL Client .NET Managed Provider （System.Data.SqlClient）OLE DB .NET Managed Provider （System.Data.OleDb）ODBC .NET Managed Provider （System.Data.ODBC）ODBC DSN ConnectionsUsing an ODBC DSN （Dat

5、a Source Name） is a two step process.1） You must first create the DSN via the ODBC Data Source Administrator programfound in your computers Control Panel （or Administrative Tools menu in Windows 2000）.Make sure to create a SYSTEM DSN （not a USER DSN） when using ASP.Note: You can also create the DSN

6、via VB code.2） Then use the following connection string - with your own DSN name of course. ;-）ODBC - DSNoConn.Open DSN=AdvWorks; & _ Uid=Admin;Pwd=;You can also create and use a File DSN. Then use the following ADO Connection string:ODBC - File DSNFILEDSN=c:somepathmydb.dsn;For more information, se

7、e: About ODBC data sources andHow to Use File DSNs and DSN-less Connections The problem with DSN is that Users can （and will） modify them （or delete by mistake）,then your program wont work so well. So its better to use a DSN-Less or OLE DB Providerconnection string with a Trusted Connection if possi

8、ble!ODBC DSN-Less ConnectionsODBC Driver for AS/400Driver=Client Access ODBC Driver （32-bit）;System=myAS400;Uid=myUsername;Pwd=myPassword; A Fast Path to AS/400 Client/ServerFor Standard Security:Driver=Microsoft Access Driver （*.mdb）;Dbq=c:somepathmydb.mdb;If you are using a Workgroup （System datab

9、ase）:SystemDB=c:somepathmydb.mdw;, _admin, If MDB is located on a network share:Dbq=myServermySharemyPathmyDb.mdb; Microsoft Access Driver Programming ConsiderationsDriver=Microsoft dBASE Driver （*.dbf）; DriverID=277;somepath; Specify the filename in the SQL statement. For example: oRs.Open Select *

10、 From user.dbf, oConn, , ,adCmdText MDAC 2.1 （or greater） requires the Borland Database Engine （BDE） to update dBase DBF files. （Q238431）. dBASE Driver Programming ConsiderationsDriver=Microsoft Excel Driver （*.xls）;DriverId=790;somepathmySpreadsheet.xls;DefaultDir=c: Microsoft Excel Driver Programm

11、ing ConsiderationsODBC Driver for MySQL （via MyODBC）To connect to a local databaseDriver=mySQL;Server=MyServerName;Option=16834;Database=mydb;To connect to a remote databaseServer=;Port=3306;Option=131072;Stmt=; Programs Known to Work with MyODBCFor the current Oracle ODBC Driver from Microsoft:Driv

12、er=Microsoft ODBC for Oracle;Server=OracleServer.world;For the older Oracle ODBC Driver from Microsoft:Driver=Microsoft ODBC Driver for Oracle;ConnectString=OracleServer.world; Connection String Format and AttributesDriver=Microsoft Paradox Driver （*.db）;DriverID=538;Fil=Paradox 5.X;dbpath;Collating

13、Sequence=ASCII; MDAC 2.1 （or greater） requires the Borland Database Engine （BDE） to update Paradox ISAM fDBF files. （Q230126）. Paradox Driver Programming ConsiderationsDriver=SQL Server; Database=myDatabaseName; For Trusted Connection security:Uid=; orTrusted_Connection=yes;To Prompt user for userna

14、me and passwordoConn.Properties（Prompt） = adPromptAlwaysDataBase=myDatabaseName; SQLDriverConnect （ODBC）If using the Sybase System 11 ODBC Driver:Driver=SYBASE SYSTEM 11;Srvr=myServerName;If using the Intersolv 3.10 Sybase ODBC Driver:Driver=INTERSOLV 3.10 32-BIT Sybase; Sybase System 10 ODBC Driver

15、 Reference GuideODBC; Driver=Sybase SQL Anywhere 5.0;Dbf=c:sqlany50mydb.db;Dsn=; Including the DSN tag with a null string is absolutely critical or else you get the dreaded -7778 error. Sybase SQL Anywhere User GuideProvider=Teradata;DBCName=MyDbcName;Database=MyDatabaseName;For more information, se

16、e Teradata ODBC DriverDriver=Microsoft Text Driver （*.txt; *.csv）;somepath;Extensions=asc,csv,tab,txt;Persist Security Info=FalseoRs.Open Select * From customer.csv oConn, adOpenStatic, adLockReadOnly, adCmdText Text File Driver Programming ConsiderationsWith a database container:Driver=Microsoft Vi

17、sual FoxPro Driver;SourceType=DBC;SourceDB=c:somepathmySourceDb.dbc;Exclusive=No;Without a database container （Free Table Directory）:SourceType=DBF;somepathmySourceDbFolder; Visual FoxPro ODBC Driver and Q165492OLE DB Data Link ConnectionsData Link File （UDL）For Absolute Path:File Name=c:somepathmyD

18、atabaseName.udl;For Relative Path:File Name=myDatabaseName.udl; HOWTO: Use Data Link Files with ADO Windows 2000 no longer contains the New | Microsoft Data Link menuanymore. You can add the Data Link menu back in the menu list by running theC:Program FilesCommon FilesSystemOle DBnewudl.reg reg file,then right-click on the desktop and select menu.Or you can also create a Data Link file by creating a text file and change itsfile extension to .udl, then double-click the file.OLE DB Provider ConnectionsOLE DB Provider for AS/400Provider=IBMDA400;Data source=myAS400;User Id=myUsername;