2022年ADO连接字符串大全[归 .pdf

《2022年ADO连接字符串大全[归 .pdf》由会员分享，可在线阅读，更多相关《2022年ADO连接字符串大全[归 .pdf（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、标题ADO 连接字符串大全选择自bohut 的 Blog关键字ADO 连接字符串大全出处This page contains sampleADO connection strings for ODBC DSN/DSN-Less,OLE DB Providers,Remote Data Services(RDS),MS Remote,MS DataShape.Also included are ADO.NET connection strings for each.NET Managed Provider(SQLClient,OLEDB,and ODBC).Thesesample connect

2、ion strings are compiledby Carl Prothman,a Microsoft Visual Basic MVPEnjoy!Table of ContentsODBC DSN Connections DSNFile DSNODBC DSN-Less ConnectionsODBC Driver for AS/400ODBC Driver for AccessODBC Driver for dBASEODBC Driver for ExcelODBC Driver for MySQLODBC Driver for OracleODBC Driver for Parado

3、xODBC Driver for SQL ServerODBC Driver for SybaseODBC Driver for SybaseSQL AnywhereODBC Driver for TextODBC Driver for TeradataODBC Driver for Visual FoxProOLE DB Data Link Connections Data Link File(UDL)OLE DB Data Provider ConnectionsOLE DB Provider for AS/400OLE DB Provider for Active Directory S

4、erviceOLE 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)OLE DB Provider for Oracle(From Oracle)OLE DB Provider for Simple Provider名师资料总结-精品资料欢迎下载-名

5、师精心整理-第 1 页，共 21 页 -OLE 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 Remote-SQL ServerData ShapeProvider ConnectionsMS DataShape-SQL

6、Server.NETManagedProviderConnectionsSQLClient.NETManagedProvider(System.Data.SqlClient)OLE DB.NET Managed Provider(System.Data.OleDb)ODBC.NET Managed Provider(System.Data.ODBC)ODBC DSN ConnectionsUsing an ODBC DSN(Data Source Name)is atwo step process.1)You must first create the DSN via the ODBC Dat

7、a SourceAdministratorprogramfound in your computers Control Panel(or Administrative Tools menu in Windows 2000).Make sure to createaSYSTEM DSN(not a USER DSN)when using ASP.Note:You can also create the DSN via VB code.2)Then usethe following connection string-with your own DSN name of course.;-)ODBC

8、-DSNoConn.Open DSN=AdvWorks;&_Uid=Admin;&_Pwd=;You can also create andusea File DSN.Then usethe following ADO Connection string:ODBC-File DSNoConn.Open FILEDSN=c:somepathmydb.dsn;&_Uid=Admin;&_Pwd=;For more information,see:About ODBC data sourcesandHow to Use File DSNs andDSN-less Connections名师资料总结-

9、精品资料欢迎下载-名师精心整理-第 2 页，共 21 页 -Note: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 usea DSN-Less or OLE DB Providerconnection string with a Trusted Connection if possible!ODBC DSN-Less ConnectionsODBC Driver for A

10、S/400oConn.Open Driver=ClientAccess ODBC Driver(32-bit);&_System=myAS400;&_Uid=myUsername;&_Pwd=myPassword;For more information,see:A Fast Path to AS/400 Client/ServerODBC Driver for AccessFor Standard Security:oConn.Open Driver=MicrosoftAccessDriver(*.mdb);&_Dbq=c:somepathmydb.mdb;&_Uid=Admin;&_Pwd

11、=;If you are using aWorkgroup(System database):oConn.Open Driver=MicrosoftAccessDriver(*.mdb);&_Dbq=c:somepathmydb.mdb;&_SystemDB=c:somepathmydb.mdw;,_admin,If MDB is located on a network share:oConn.Open Driver=MicrosoftAccessDriver(*.mdb);&_Dbq=myServermySharemyPathmyDb.mdb;For more information,se

12、e:Microsoft Access Driver Programming ConsiderationsODBC Driver for dBASEoConn.Open Driver=MicrosoftdBASE Driver(*.dbf);&_DriverID=277;&_Dbq=c:somepath;名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 21 页 -Note:Specify the filename in the SQL statement.For example:oRs.Open Select*From user.dbf,oConn,adCmdTextNote:MD

13、AC 2.1(or greater)requires the Borland Database Engine(BDE)to update dBase DBFfiles.(Q238431).For more information,see:dBASE Driver Programming ConsiderationsODBC Driver for ExceloConn.Open Driver=MicrosoftExcel Driver(*.xls);&_DriverId=790;&_Dbq=c:somepathmySpreadsheet.xls;&_DefaultDir=c:somepath;F

14、or more information,see:Microsoft Excel Driver Programming ConsiderationsODBC Driver for MySQL(via MyODBC)To connect to a local databaseoConn.Open Driver=mySQL;&_Server=MyServerName;&_Option=16834;&_Database=mydb;To connect to a remote databaseoConn.Open Driver=mySQL;&_Server=;&_Port=3306;&_Option=1

15、31072;&_Stmt=;&_Database=mydb;&_Uid=myUsername;&_Pwd=myPassword;For more information,see:Programs Known to Work with MyODBCODBC Driver for Oracle名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 21 页 -For the current Oracle ODBC Driver from Microsoft:oConn.Open Driver=MicrosoftODBC for Oracle;&_Server=OracleServer.wor

16、ld;&_Uid=myUsername;&_Pwd=myPassword;For the older Oracle ODBC Driver from Microsoft:oConn.Open Driver=MicrosoftODBC Driver for Oracle;&_ConnectString=OracleServer.world;&_Uid=myUsername;&_Pwd=myPassword;For more information,see:Connection String Format andAttributesODBC Driver for ParadoxoConn.Open

17、 Driver=MicrosoftParadox Driver(*.db);&_DriverID=538;&_Fil=Paradox 5.X;&_DefaultDir=c:dbpath;&_Dbq=c:dbpath;&_CollatingSequence=ASCII;Note:MDAC2.1(or greater)requires the Borland Database Engine(BDE)to update ParadoxISAM fDBF files.(Q230126).For more information,see:Paradox Driver Programming Consid

18、erationsODBC Driver for SQL ServerFor Standard Security:oConn.Open Driver=SQLServer;&_Server=MyServerName;&_Database=myDatabaseName;&_Uid=myUsername;&_Pwd=myPassword;For Trusted Connection security:名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 21 页 -oConn.Open Driver=SQLServer;&_Server=MyServerName;&_Database=myDa

19、tabaseName;&_Uid=;&_Pwd=;oroConn.Open Driver=SQLServer;&_Server=MyServerName;&_Database=myDatabaseName;&_Trusted_Connection=yes;To Prompt user for usernameand passwordoConn.Properties(Prompt)=adPromptAlwaysoConn.Open Driver=SQLServer;&_Server=MyServerName;&_DataBase=myDatabaseName;For more informati

20、on,see:SQLDriverConnect(ODBC)ODBC Driver for SybaseIf using the SybaseSystem 11 ODBC Driver:oConn.Open Driver=SYBASESYSTEM 11;&_Srvr=myServerName;&_Uid=myUsername;&_Pwd=myPassword;If using the Intersolv 3.10 SybaseODBC Driver:oConn.Open Driver=INTERSOLV3.10 32-BIT Sybase;&_Srvr=myServerName;&_Uid=my

21、Username;&_Pwd=myPassword;For more information,see:SybaseSystem 10 ODBC Driver ReferenceGuideODBC Driver for SybaseSQL AnywhereoConn.Open ODBC;Driver=Sybase SQL Anywhere 5.0;&_名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 21 页 -DefaultDir=c:dbpath;&_Dbf=c:sqlany50mydb.db;&_Uid=myUsername;&_Pwd=myPassword;Dsn=;Note

22、:Including the DSN tag with a null string is absolutely critical or else you get the dreaded-7778 error.For more information,see:SybaseSQL Anywhere User GuideODBC Driver for TeradataoConn.Open Provider=Teradata;&_DBCName=MyDbcName;&_Database=MyDatabaseName;&_Uid=myUsername;&_Pwd=myPassword;For more

23、information,seeTeradataODBC DriverODBC Driver for TextoConn.Open Driver=MicrosoftText Driver(*.txt;*.csv);&_Dbq=c:somepath;&_Extensions=asc,csv,tab,txt;&_Persist Security Info=FalseNote:Specify the filename in the SQL statement.For example:oRs.Open Select*From customer.csv,_oConn,adOpenStatic,adLock

24、ReadOnly,adCmdTextFor more information,see:Text File Driver Programming ConsiderationsODBC Driver for Visual FoxProWith a databasecontainer:oConn.Open Driver=MicrosoftVisual FoxPro Driver;&_SourceType=DBC;&_SourceDB=c:somepathmySourceDb.dbc;&_Exclusive=No;名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 21 页 -Without

25、 a databasecontainer(Free Table Directory):oConn.Open Driver=MicrosoftVisual FoxPro Driver;&_SourceType=DBF;&_SourceDB=c:somepathmySourceDbFolder;&_Exclusive=No;For more information,see:Visual FoxPro ODBC Driver and Q165492OLE DB Data Link ConnectionsData Link File(UDL)For Absolute Path:oConn.Open F

26、ile Name=c:somepathmyDatabaseName.udl;For Relative Path:oConn.Open File Name=myDatabaseName.udl;For more information,see:HOWTO:Use Data Link Files with ADONote: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

27、:Program FilesCommon FilesSystemOle DBnewudl.reg reg file,then right-click on the desktop and selectNew|Microsoft Data Link menu.Or you canalso createa Data Link file by creating a text file and changeitsfile extension to.udl,then double-click the file.OLE DB Provider ConnectionsOLE DB Provider for

28、AS/400oConn.Open Provider=IBMDA400;&_Data source=myAS400;User Id=myUsername;&_Password=myPassword;For more information,see:A Fast Path to AS/400 Client/ServerOLE DB Provider for Active Directory ServiceoConn.Open Provider=ADSDSOObject;&_名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 21 页 -User Id=myUsername;&_Passw

29、ord=myPassword;For more information,see:Microsoft OLE DB Provider for Microsoft Active Directory ServiceOLE DB Provider for DB2oConn.Open=Provider=DB2OLEDB;&Network Transport Library=TCPIP;&Network Address=MyServer;&_Package Collection=MyPackage;&Host CCSID=1142Initial Catalog=MyDB;&User ID=MyUserna

30、me;&_Password=MyPassword;For more information,see:OLE DB Provider for DB2andINF:Configuring Data Sourcesfor the Microsoft OLE DB Provider for DB2OLE DB Provider for Index ServeroConn.Open Provider=msidxs;&_Data source=MyCatalog;For more information,see:Microsoft OLE DB Provider for Microsoft Indexin

31、g ServiceOLE DB Provider for Internet PublishingoConn.Open Provider=MSDAIPP.DSO;&_Data Source=http:/mywebsite/myDir;&_User Id=myUsername;&_Password=myPassword;For more information,see:Microsoft OLE DB Provider for Internet Publishing and Q245359OLE DB Provider for Microsoft JetFor standard security:

32、oConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepathmyDb.mdb;&_名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 21 页 -User Id=admin;&_Password=;If using a Workgroup(System Database):oConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepathmydb.mdb;&_Jet OLEDB:System Database=MySystem.mdw;,_

33、admin,Note,remember to convert both the MDB and the MDW to the 4.0 databaseformat when usingthe 4.0 OLE DB Provider.If MDB hasa databasepassword:oConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepathmydb.mdb;&_Jet OLEDB:Database Password=MyDbPassword;,_admin,If MDB is located on a netw

34、ork share:oConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=myServermySharemyPathmyDb.mdb;If want to open up the Access databaseexclusively:oConn.Mode=adModeShareExclusiveoConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepathmyDb.mdb;&_User Id=admin;&_Password=;For more informa

35、tion,see:OLE DB Provider for MicrosoftJet,Q191754,Q225048,Q239114,andQ271908You can also open anExcel Spreadsheetusing the OLE DB Provider for Microsoft JetoConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepathmyExcelSpreadsheet.xls;&_Extended Properties=Excel 8.0;HDR=Yes;Where HDR=Yes

36、 meansthat thereis a header row in the cell range名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 21 页 -(or named range),so the provider will not include the first row of theselection into the recordset.If HDR=No,then the provider will includethe first row of the cell range(or named ranged)into the recordset.For mor

37、e information,see:Q278973You can also open aText File using the OLE DB Provider for Microsoft JetoConn.Open Provider=Microsoft.Jet.OLEDB.4.0;&_Data Source=c:somepath;&_Extended Properties=text;HDR=Yes;FMT=Delimited;Then open a recordsetbasedon aselect on the actual fileoRs.Open Select*From MyTextFil

38、e.txt,oConn,adOpenStatic,adLockReadOnly,adCmdTextFor more information,see:Q262537OLE DB Provider for ODBC DatabasesFor Access(Jet):oConn.Open Provider=MSDASQL;&_Driver=MicrosoftAccess Driver(*.mdb);&_Dbq=c:somepathmydb.mdb;&_Uid=myUsername;&_Pwd=myPassword;For SQL Server:oConn.Open Provider=MSDASQL;

39、&_Driver=SQLServer;&_Server=myServerName;&_Database=myDatabaseName;&_Uid=myUsername;&_Pwd=myPassword;For more information,see:Microsoft OLE DB Provider for ODBCOLE DB Provider for Oracle(from Microsoft)oConn.Open Provider=msdaora;&_Data Source=MyOracleDB;&_User Id=myUsername;&_名师资料总结-精品资料欢迎下载-名师精心整理

40、-第 11 页，共 21 页 -Password=myPassword;For more information,see:Microsoft OLE DB Provider for OracleOLE 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 Pr

41、ovider=OraOLEDB.Oracle;&_Data Source=MyOracleDB;&_User Id=/;&_Password=;OroConn.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 namingmethod in use.For example,for Local Naming,it is the alias in the

42、 tnsnames.orafile;for OracleNames,it is the Net8 Service Name.For more information,see:Connecting to an Oracle Database(Note,if you get a Logon dialog,then click Cancel,then perform a one-time free signup withOracles TechNet system)OLE DB Provider for Simple ProviderThe MicrosoftOLE DB Simple Provid

43、er(OSP)allows ADO to access any data for which aprovider hasbeen written using the OLE DB Simple Provider Toolkit.Simple providers are intended to accessdatasources that require only fundamental OLE DB support,such as in-memoryarrays or XMLdocuments.OSP in MDAC2.6 has been enhanced to support openin

44、g hierarchical ADO Recordsets overarbitrary名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 21 页 -XML files.These XML files may contain theADO XML persistence schema,but it is not required.Thishasbeen implemented by connecting the OSP to the MSXML2.DLL,therefore MSXML2.DLLornewer isrequired.oConn.Open Provider=MSDAO

45、SP;&_Data Source=MSXML2.DSOControl.2.6;oRS.Open http:/WebServer/VirtualRoot/MyXMLFile.xml,oConnFor more information,see:Microsoft OLE DB Simple ProviderOLE DB Provider for SQL ServerFor Standard Security:oConn.Open Provider=sqloledb;&_Data Source=myServerName;&_Initial Catalog=myDatabaseName;&_User

46、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(SQL Server 2000)oConn.Open Provider=sqloledb;&_Data Source=myServerNameInst2;&_Initial Catal

47、og=myDatabaseName;&_User Id=myUsername;&_Password=myPassword;To Prompt user for usernameand password:oConn.Provider=sqloledboConn.Properties(Prompt)=adPromptAlwaysoConn.Open Data Source=myServerName;&_Initial Catalog=myDatabaseName;名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 21 页 -To connect via an IP address:o

48、Conn.Open Provider=sqloledb;&_Data Source=xxx.xxx.xxx.xxx,1433;&_Network Library=DBMSSOCN;&_Initial Catalog=myDatabaseName;&_User ID=myUsername;&_Password=myPassword;Note:-xxx.xxx.xxx.xxxis an IP address-NetworkLibrary=DBMSSOCNtellsOLE DBto use TCP/IP rather than Named Pipes(Q238949)-1433 is the def

49、ault port number for SQL Server-You can also addEncrypt=yes for encryptionFor more information,see:Microsoft OLE DB Provider for SQL ServerRemote Data Service(RDS)ConnectionsThe following examples show how to connect to a remote databaseusing the RDS Data Control.When using the RDS DataControls Serv

50、er/SQL/Connect properties,the RDS DataControl usestheRDS DataFactory on the remote server.If you usethe RDS DataControls URL property,then the RDS DataFactory is not used at all.WARNING:The RDS DataFactory can be a major security hole if not setup and configuredcorrectly!For more information,seeRDS