출처 : http://www.topxml.com/sql/articles/adoforxml/
Using VB and ADO to return and update Record Set based XML

by: Sean Grimaldi, Serious Consulting LLC.

This article is about making the most of ADO and XML until the complete Visual Basic.net, including ADO.net, becomes available.  This article is focused on how you, as a developer, can use ADO and XML today; and get valuable skills that apply to Visual Basic.net.  I also want to show how you can use ADO, XML, and XSL today, to improve the quality of your development.

 I am taken aback almost daily, at the slow adoption many standards-based technologies face.  XSLT is a fine example.  XSLT reached candidate recommendation, meaning the w3c considered it stable and encouraged implementation, in November 2000.   I am writing this article six months later, in a room packed with developers working for a worldwide software company.  Not one of these developers, that I am aware of, has ever used XSLT in a production environment.

 Part of the blame must go to how Microsoft spoiled us with Visual Basic.  As a development tool, it is unparalleled.  Think how simple it is to get an ADO Recordset; Visual Basic will even assist you in debugging the SQL query.

Unfortunately, at this time the situation is not at all the same with XSLT.  Microsoft does not currently make a powerful IDE that includes a powerful XML editor.  Visual Interdev, for example, mangles XSL. The best XML IDE available from Microsoft is XML Notepad (http://msdn.microsoft.com/xml/NOTEPAD/download.asp). As Visual Basic developers expecting features like statement completion, this is a tremendous loss.

We all suspect Visual Basic.net will provide us with the tools to make the use of these technologies simple, but until then, I would like to offer a few pointers to flatten the learning curve.

As an example of coming XML tools, Microsoft includes a XML Designer with color codes and statement and tag completion in Visual Studio.net.

Figure 1. Color-Coded XML Designer with Statement and Tag Completion

Since most developers prefer code samples to help illustrate technologies with which they are unfamiliar, this article loosely follows a sample. The sample scenario is simple and something you will likely run into frequently.  I am interested in improvements you may make or ways in which you extend it to be more useful.  Please email me your comments at my website, http://www.SeriousConsulting.com.

ADO

Every Visual Basic developer sooner or later becomes quite strong with ADO, because it is comparatively simple and allows access to every data source the developer is likely to encounter.

The disconnected Recordset is the staple of Microsoft web development.  It is most likely the single programming practice that can increase the scalability of a Visual Basic and/or ASP driven web site more than any other.  ADO makes it almost effortless.  A disconnected Recordset is simply a Recordset that no longer has a live connection to the server.  To disconnect a recordset, set it's ActiveConnection property equal to nothing. At this point it is safe to close the ADO connection object as well.  In ADO.net by default the data set is disconnected.

'declare variables

Dim objConn as new ADODB.Connection

Dim objRS as new ADODB.Recorset

Dim strConnectionString as String

Dim strSQL as String

'open Connection

objConn.open strConnectionString

'optimize record set properties

objRS.CursorLocation = AdUseClient

objRS.CursorType = adOpenStatic

objRS.LockType = adLockBatchOptimistic

'open the record set

objRS.open strSQL , objConn

'disconnect the record set and close Connection

Set objRS.ActiveConnection = Nothing

ObjConn.Close

'use the recordset

While NOT objRS.eof

'do something

Wend

In a couple of lines data can be retrieved from almost any data source a developer is likely to encounter, including text files and many spreadsheets.

XML

XML is the future for almost all data transmission and data manipulation.  Notice that I did not say anything about the Internet; XML is that huge!

Microsoft, IBM, and Sun support XML-based standards, which include SOAP and UDDI.  These two XML based technologies promise interoperability at the method level across languages, domains, and platforms.  This interoperability allows the RAD abilities of Visual Basic to be felt everywhere, ensuring Visual Basics' place as the most popular development language for years to come.

SOAP is a platform independent protocol, in this sense like HTTP, for exchanging messages in a decentralized, distributed-environment using XML.  SOAP is more standards-based and platform-neutral than previous technologies like CIS, RDS, and Remote Scripting.

The Universal Discovery Description and Integration (UDDI) specifications define a way to publish and discover information about Web Services using XML.  Since a major portion of new development will be Web Services based, UDDI is extremely important because it allows the appropriate functionality to be found on the Internet and utilized.

Best of all, this major shift to platform-independent Web Services as a new industry-wide software model, is fairly simple, all thanks to XML.

Although you can hand-code XML, as you can with HTML, it is easier to have the application do the work. Although this is really BizTalk's forte, here is an example of SQL Server 2000 doing the work, using the very handy FOR XML clause.

SELECT TOP 10 FirstName

FROM Employees

FOR XML AUTO

Results in:

<Employees FirstName="Nancy" EmployeeID="1"/>

<Employees FirstName="Andrew" EmployeeID="2"/>

<Employees FirstName="Janet" EmployeeID="3"/>

<Employees FirstName="Margaret" EmployeeID="4"/>

<Employees FirstName="Steven" EmployeeID="5"/>

<Employees FirstName="Michael" EmployeeID="6"/>

<Employees FirstName="Robert" EmployeeID="7"/>

<Employees FirstName="Laura" EmployeeID="8"/>

<Employees FirstName="Anne" EmployeeID="9"/>

Unfortunately, the FOR XML AUTO clause does not support all SQL statements.  It currently does not even support GROUP BY, for example, which is fairly common.

An alternative to the FOR XML clause is executing SQL statements using the URL.  Although this sounds like the dream of every ASP developer, after all it takes the form of a URL with a SQL statement in the query string, URL access does not allow you to easily write generic Visual Basic functions that return XML from any data source.

http://IISServer/Nwind?sql=SELECT+top+10+FirstName+FROM+Employees +FOR+XML+RAW&root=ROOT

How can you write a generic Visual Basic function that accepts standard SQL statements?  You can not just tack FOR XML AUTO on the end of a regular SQL statement or stick it in the URL.

Even worse, you rely on other database vendors supporting FOR XML or SQL access using HTTP in exactly the same manner.  

ADO equals effortless XML

Fortunately plain ADO does allow you to easily retrieve XML, today.

You can write a generic Visual Basic function, or even one in VB Script, based on the model of a SQL Select query as a parameter and a return value of a XML string.  Furthermore it allows you to easily retrieve the XML, use it, and update it back to the database.  This is functionality you are likely to use again and again, so it is valuable to put the functionality in a data access class.

Since there are so many interesting options available while writing this class, each worthy of its own article, I broke it down into three parts.  Perhaps in future articles, I will explore each part in more depth.

Get the XML from ADO

Call this class ClsDataAccess so it is clear what we intend to use it for.  The most obvious way to implement the class using a simple method GetRS, is still fairly awkward to use.

 Public Function GetRS(ByVal strSQL as String,ByVal strConnectionString as String) as ADODB.Recordset

The awkwardness comes from having the ConnectionStringproperty exposed to the developer, especially if the password is reasonably secure.

"provider=SQLOLEDB; data source=db3x.seriousconsulting.com; initial catalog=Pubs; UID=mtsusr01; PWD=38710z2c7993F82"

How secure can a password be if every developer, designer, content manager, and QA team member has access to a username and password with full read and write permissions on the production server?  Furthermore, changing values within the connection string does not allow the connection to be pooled as efficiently, reducing scalability.

A solution that simplifies reuse and increases security is to set the connection strings as enumerated constants.  Here I provide constants for only two database connections, but in practice, there may often be several.

'the db connectionstrings constants

Public Enum eCONNECTIONSTRING

   CS_Pubs = 0

   CS_NorthWind= 1

End Enum

A simple private function in ClsDataAccess that applies values to the enumerated constants may look like this:

Private Function EvalEnumCS(ConnectionString)

'this is a helper function used by the methods to evaluate the enum value

     If ConnectionString =  CS_ Pubs Then

          EvalEnumCS =  "provider=SQLOLEDB; data source = db3x.seriouscons

ulting.com; initial catalog = Pubs; UID = mtscsusr01; PWD =1eer5130q82F"

     ElseIf ConnectionString = CS_ NorthWind Then

          EvalEnumCS =  "provide r=SQLOLEDB; data source=db2x.ohgolly.com; initial catalog = NorthWind; UID = mtscsusr02; PWD = c4883H55433"

     End If

 Exit Function

By using enumerated constants the method is simpler to use; and by using a function to process the values of the constants, access can be changed in one place for the whole site.  Additionally developers do not need to know any passwords, they create an object from the ClsDataAccess class, pass in a SQL query as a parameter and pick a constant.

Figure 2. Using Enumerated Constants for Database Access

A subtle point that you may have missed is that the connection strings are using a DNS as the datasource property.  This allows the compiled component to be deployed to a development server, QA server, or a production server and hit the correct database without being recompiled.  This is perhaps the best way  to get around the serious shortcoming of having to recompile the class to access a different database in development versus production without having to pass the server name or DSN as an argument of the method.  Remember, passing the Connection String or a DSN as a parameter does not make the GetRS method especially useful.

Here is the GetRS method of the ClsDataAccess, with the enumerated constants and a function to evaluate the constants incorporated into the class.

Public Function GetRS(ByVal strSQL As String,ByVal ConnectionString As eCONNECTIONSTRING = CS_Pubs)

As ADODB.Recordset

'declare variables

Dim objConn as new ADODB.Connection

Dim objRS as new ADODB.Recorset

Dim strConnectionString as String

'set the connection string = to the value of the constant

strConnectionString = CStr(EvalEnumCS(ConnectionString))

'open Connection

objConn.open strConnectionString

'open the record

   'continue as before

This GetRS method is pretty good as a demonstrator, but it returns an ADO Recordset rather than XML. You may want to add error handling and keep it as a method in your data access class, adding a separate method to return XML.

Using Microsoft XML Parser (MSXML) 3.0 there are a couple of ways to get a XML document from the Recordset.  Since the goal is to get XML, and XML is a string, I save the Recordset object to an ADO stream object as XML.

'once the record set is open

objRS.save objADOStream, 1 'adPersistXML

Set objRS.ActiveConnection = Nothing

Conn.Close

' Put the Recordset Stream into a string variable.

strXML = objADOStream.ReadText(-1)  '-1=adReadAll

'return the XML string rather than the record set object

With ADO 2.5 and later, Recordset objects can be persisted into any object that implements the IStream interface. The obvious choice is the ADO Stream object, although the ASP response object also implements IStream.

I use the ADO stream object, which is regrettably infrequently used, so the XML does not have to be saved as a document before we can work with it.  The Recordset can also be saved directly into the XML DOM object. This is more scalable, as it skips having to save the Recordset into an ADO Stream and then loading the ADO Stream into the DOM object.   For demonstration purposes, it is clearer to do the extra step.   Now, you have a usable function that returns XML as a string in a standard ADO Recordset definition.  ADO even allows persistence of hierarchical Recordsets into XML, so you are not especially limited.

Get HTML from the XML

Just to give you an idea of the many uses of XML, lets display the XML as HTML on a web page. Since XML is just data in a text format, you have to transform it with a presentation style before it can be displayed as HTML.  If you write out the raw XML string to a web page the user's browser will not display anything. However, the XML is shown in Source View.

It may not be clear to you what you are seeing in the page source. The source is broken into two sections, a schema section followed by a data section.

The XML document starts with a definition of the record set schema and some additional Meta information. The simplest way to think of a schema is to think of the document as an instance of the schema in the same way an object is an instance of a class.

The actual Recordset data is contained in elements that look like this:

<z:row FirstName='Sean' LastName='Grimaldi' />

If you try working with the XML and experience unexpected problems remember that ADO, and almost all of Windows, is UTF-8 format, where as Java and XML are Unicode.

The Presentation = XML + XSL

In this instance we are using only using simple XSL, although you could also display the XML using Cascading Style Sheets (CSS).  

XSL becomes complex quickly, so it may be useful to keep it basic at first.  There are many excellent code samples and articles on XSL on the Internet, even if there are currently no tools as simple as Visual Basic for working with XSL.

It may be simpler to use a generic XSL style sheet since it will work on any ADO DB Recordset; and modify the XSL as the situation calls for.  An excellent example of a generic XSL style sheet can be in Michele Vivoda topxml.com article http://www.topxml.com/xsl/articles/xsl_ado/.

An ASP page that combines the XML and the XSL could be very short.

'get the XSL style sheet

styleFile = Server.MapPath("Genericxsl.xsl")

'create an XML DOM object, an XSL Dom object, and your Data Access object

Set XMLDoc = Server.CreateObject("Microsoft.XMLDOM")

Set XSLDoc = Server.CreateObject("Microsoft.XMLDOM")

Set DataAccess = server.CreateObject("ProDataAccess.ClsDataAccess")

XMLDoc.async = false

 'load the XML returned from your GetRS method into the XML object XMLDoc.loadXML(DataAccess.GetRs(strSQL,1))

 XSLDoc.async = false

'load the XSL stylesheet into the XSL object

XSLDoc.load(styleFile)

 'write our the HTML result of combining the two

Response.Write XMLDoc.transformNode(XSLDoc)

Combining the XML returning GetRS method with the XSL style sheet results in a plain little HTML table.  Since XSL is much more powerful than what the example has demonstrated, please remember that you could do much, much, more with the XML.  Microsoft Biztalk Server excels at generating XSL so one XML schema can be mapped to a different XML schema.  This means that if you can map fields from one XML document to fields in a generic XML document, you can write more abstract code that deals with one generic case.  This results in more code reuse, faster development, centralized error handling, and more maintainable code.

Get ADORS from the XML

Since you now know that XSL can transform one XML document into an XML document with a different schema, you may have realized that you can transform a very dissimilar XML document into an XML document that is valid to the ADO Recordset schema.  This means you could convert the ADO schema based XML document back into a Recordset, which could be updated to the server/Database.  This uses the source parameter of the ADO Recordset object.

' Open the Stream back into a RecordsetObject.

objRs.open  ADOStream

Almost Done

As I stated, this article is about making the most of ADO and XML until the full-blown Visual Basic.net becomes available.  It covers the Stream object, which is unfortunately commonly ignored.  The sample began showing how ADO could be used to get an XML document from the database.  Secondly the sample demonstrated how the XML document could be used to display records in an HTML table using XSLT.  Lastly, the sample showed how to get the XML into a Recordset object so that the database could be updated.

Obviously this example could be extended quite a bit.  As mentioned, to increase scalability the Stream object could be eliminated from the class. Other extensions, such as more sophisticated XSLT combined with CSS would be valuable in real-world situations.

I hope you reuse the concepts in this article often and to good measure.

Here are some resources you may find useful:

MSDN ADO Stream
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/mdao1ajx.htm

UDDI.org
http://www.uddi.org/

W3C SOAP
http://www.w3.org/TR/SOAP/

W3C XSL
http://www.w3.org/Style/XSL/

TopXML
http://www.topxml.com/xsl/

신고
Posted by 나비 나비:D

원문 : http://www.codemaker.co.uk/it/tips/ado_conn.htm


MDAC 2.8 다운로드 : http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c


ADO Connection String Samples

This page contains sample ADO connection strings for ODBC DSN / DSN-Less, OLE DB Providers, Remote Data Services (RDS), MS Remote, and MS DataShape.

Also included are ADO.NET connection strings for MySQL, ODBC, OLE DB, Oracle, and SQL Server .NET Data Providers.

These sample connection strings are compiled by Carl Prothman, a Microsoft ASP.NET MVP and Microsoft Certified Professional (MCP)

If you have an ADO or ADO.NET connection string that is not listed below, or you see an connection string that does not have the correct setting, please send an email to Carl Prothman.  Thanks!


Table of Contents


ODBC DSN Connections

Using an ODBC DSN (Data Source Name) is a two step process.

1) You must first create the DSN via the "ODBC Data Source Administrator" program found in your computer's Control Panel (or Administrative Tools menu in Windows 2000). Make sure to create a SYSTEM DSN (not a USER DSN) when using ASP. You can also create the DSN via Visual Basic code.

2) Then use the following connection string - with your own DSN
name of course.

oConn.Open "DSN=mySystemDSN;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 
oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see:  About ODBC data sources and 
How to Use File DSNs and DSN-less Connections

Note: The problem with DSN is that Users can (and will) modify or delete them by mistake, then your program won't work so well. So it's better to use a DSN-Less or OLE DB Provider connection string - with a Trusted Connection if possible!


ODBC DSN-Less Connections

For Standard Security:

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "Uid=admin;" & _ "Pwd=" 

If you are using a Workgroup (System database):

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "SystemDB=c:\somepath\mydb.mdw;", _ "myUsername", "myPassword" 

If want to open up the MDB exclusively

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "Exclusive=1;" & _ "Uid=admin;" & _ "Pwd=" 

If MDB is located on a Network Share

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=\\myServer\myShare\myPath\myDb.mdb;" & _ "Uid=admin;" & _ "Pwd=" 

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 "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=" & Server.MapPath(".") & "\myDb.mdb;" & _ "Uid=admin;" & _ "Pwd=" %>

This assumes the MDB is in the same directory where the ASP page is running. Also make sure this directory has Write permissions for the user account.
 

If you don't know the path to the MDB (using VB)

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=" & App.Path & "\myDb.mdb;" & _ "Uid=admin;" & _ "Pwd="

This assumes the MDB is in the same directory where the application is running.

For more information, see:  Microsoft Access Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Access Driver, click here 


oConn.Open "Driver={Client Access ODBC Driver (32-bit)};" & _ "System=myAS400;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see:   A Fast Path to AS/400 Client/Server


oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _ "DriverID=277;" & _ "Dbq=c:\somepath"

Then specify the filename in the SQL statement:

oRs.Open "Select * From user.dbf", oConn, , ,adCmdText

Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update dBase DBF files. (Q238431).

For more information, see:  dBASE Driver Programming Considerations

To view Microsoft KB articles related to Microsoft dBASE Driver, click here 


oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _ "DriverId=790;" & _ "Dbq=c:\somepath\mySpreadsheet.xls;" & _ "DefaultDir=c:\somepath" 

For more information, see:  Microsoft Excel Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Excel Driver, click here 


If using INFORMIX 3.30 ODBC Driver

oConn.Open "Dsn='';" & _ "Driver={INFORMIX 3.30 32 BIT};" & _ "Host=myHostname;" & _ "Server=myServerName;" & _ "Service=myServiceName;" & _ "Protocol=olsoctcp;" & _ "Database=myDbName;" & _ "UID=myUsername;" & _ "PWD=myPassword" & _ ' Or
oConn.Open "Dsn=myDsn;" & _ "Host=myHostname;" & _ "Server=myServerName;" & _ "Service=myServiceName;" & _ "Protocol=onsoctcp;" & _ "Database=myDbName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

If using INFORMIX-CLI 2.5 ODBC Driver

oConn.Open "Driver={Informix-CLI 2.5 (32 Bit)};" & _ "Server=myServerName;" & _ "Database=myDbName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" & _

For more information, see: Informix Developer ZoneConnection to ODBC Data Source,


For the local machine

oConn.Open "Driver={Easysoft IB6 ODBC};" & _ "Server=localhost;" & _ "Database=localhost:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For a remote machine

oConn.Open "Driver={Easysoft IB6 ODBC};" & _ "Server=myMachineName;" & _ "Database=myMachineName:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For more information, see:  Connecting to InterBase and Easysoft


For the local machine

oConn.Open "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};" & _ "Server=localhost;" & _ "Database=localhost:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For a remote machine

oConn.Open "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};" & _ "Server=myMachineName;" & _ "Database=myMachineName:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For more information, see: Google Search  (if you know a direct URL email me)


oConn.Open "Driver={Lotus NotesSQL 3.01 (32-bit) ODBC DRIVER (*.nsf)};" & _ "Server=myServerName;" & _ "Database=mydir\myDbName.nsf;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" & _

For more information, see:   Connection keywords


To connect to a local database

oConn.Open "Driver={mySQL};" & _ "Server=MyServerName;" & _ "Option=16834;" & _ "Database=mydb" 

To connect to a remote database

oConn.Open "Driver={mySQL};" & _ "Server=db1.database.com;" & _ "Port=3306;" & _ "Option=131072;" & _ "Stmt=;" & _ "Database=mydb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see:  Programs Known to Work with MyODBC


For the current Oracle ODBC Driver from Microsoft

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _ "Server=OracleServer.world;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For the older Oracle ODBC Driver from Microsoft

oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _ "ConnectString=OracleServer.world;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see:  Connection String Format and Attributes

To view Microsoft KB articles related to Microsoft ODBC for Oracle, click here 


oConn.Open "Driver={Oracle ODBC Driver};" & _ "Dbq=myDBName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

Where:  The DBQ name must be defined in the tnsnames.ora file

For more information, see:  Oracle8 ODBC Driver Help, Oracle ODBC FAQs, [asporacle] listserv FAQs, and ASPDB Oracle


oConn.Open "Driver={Microsoft Paradox Driver (*.db )};" & _ "DriverID=538;" & _ "Fil=Paradox 5.X;" & _ "DefaultDir=c:\dbpath\;" & _ "Dbq=c:\dbpath\;" & _ "CollatingSequence=ASCII" 

Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update Paradox ISAM fDBF files. (Q230126).

Note: There is an extra space after "db" in the Paradox Driver name

For more information, see:  Paradox Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Paradox Driver, click here 


For Standard Security

oConn.Open "Driver={SQL Server};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

For Trusted Connection security

oConn.Open "Driver={SQL Server};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=;" & _ "Pwd=" ' Or
oConn.Open "Driver={SQL Server};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Trusted_Connection=yes" 

To Prompt user for username and password

oConn.Properties("Prompt") = adPromptAlways oConn.Open "Driver={SQL Server};" & _ "Server=MyServerName;" & _ "DataBase=myDatabaseName" 

To connect to SQL Server running on the same computer

oConn.Open "Driver={SQL Server};" & _ "Server=(local);" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

To connect to SQL Server running on a remote computer (via an IP address)

oConn.Open "Driver={SQL Server};" & _ "Server=xxx.xxx.xxx.xxx;" & _ "Address=xxx.xxx.xxx.xxx,1433;" & _ "Network=DBMSSOCN;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
   Pipes (Q238949)
 

For more information, see:  SQLDriverConnect (ODBC)

To view Microsoft KB articles related to ODBC Driver for SQL Server, click here 


If using the Sybase System 12 (or 12.5) Enterprise Open Client ODBC Driver

oConn.Open "Driver={SYBASE ASE ODBC Driver};" & _ "Srvr=myServerName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

If using the Sybase System 11 ODBC Driver

oConn.Open "Driver={SYBASE SYSTEM 11};" & _ "Srvr=myServerName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

If using the Intersolv 3.10 Sybase ODBC Driver

oConn.Open "Driver={INTERSOLV 3.10 32-BIT Sybase};" & _ "Srvr=myServerName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see: Sybase System 10 ODBC Driver Reference Guide

To view Microsoft KB articles related to ODBC Driver for Sybase, click here 


oConn.Open "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _ "DefaultDir=c:\dbpath\;" & _ "Dbf=c:\sqlany50\mydb.db;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;" & _ "Dsn="""""

Note: Including the DSN tag with a null string is absolutely critical or else you get the dreaded -7778 error.

For more information, see:  Sybase SQL Anywhere User Guide


oConn.Open "Provider=Teradata;" & _ "DBCName=MyDbcName;" & _ "Database=MyDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"

For more information, see  Teradata ODBC Driver


oConn.Open _ "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "Dbq=c:\somepath\;" & _ "Extensions=asc,csv,tab,txt" 

Then specify the filename in the SQL statement:

oRs.Open "Select * From customer.csv", _ oConn, adOpenStatic, adLockReadOnly, adCmdText

Note: If you are using a Tab delimited file, then make sure you create a schema.ini file, and include the "Format=TabDelimited" option.

For more information, see:  Text File Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Text Driver, click here 


With a database container

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBC;" & _ "SourceDB=c:\somepath\mySourceDb.dbc;" & _ "Exclusive=No"  

Without a database container (Free Table Directory)

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBF;" & _ "SourceDB=c:\somepath\mySourceDbFolder;" & _ "Exclusive=No" 

For more information, see:  Visual FoxPro ODBC Driver and Q165492

To view Microsoft KB articles related to ODBC Driver for Visual FoxPro, click here 




OLE DB Data Link Connections

For Absolute Path

oConn.Open "File Name=c:\somepath\myDatabaseName.udl" 

For Relative Path

oConn.Open "File Name=myDatabaseName.udl" 

For more information, see:  HOWTO: Use Data Link Files with ADO

Note: Windows 2000 no longer contains the "New | Microsoft Data Link" menu  anymore. You can add the Data Link menu back in the menu list by running the "C:\Program Files\Common Files\System\Ole DB\newudl.reg" reg file, then right-click on the desktop and select "New | Microsoft Data
Link" menu.

Or you can also create a Data Link file by creating a text file and change it's file extension to ".udl", then double-click the file.

To view Microsoft KB articles related to Data Link File, click here 




OLE DB Provider Connections

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 


oConn.Open "Provider=Advantage OLE DB Provider;" & _ "Data source=c:\myDbfTableDir;" & _ "ServerType=ADS_LOCAL_SERVER;" & _ "TableType=ADS_CDX"

For more information, see:  Advantage OLE DB Provider (for ADO)


oConn.Open "Provider=IBMDA400;" & _ "Data source=myAS400;" & _ "User Id=myUsername;" & _ "Password=myPassword"

For more information, see:   A Fast Path to AS/400 Client/Server


oConn.Open "Provider=SNAOLEDB;" & _ "Data source=myAS400;" & _ "User Id=myUsername;" & _ "Password=myPassword"

For more information, see:  Connection and ConnectionString Property

To view Microsoft KB articles related to OLE DB Provider for AS/400 and VSAM, click here 


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 


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: Connection, ConnectionString Property, and Q218590

To view Microsoft KB articles related to OLE DB Provider for DB2, click here 


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 


oConn.Provider = "EXOLEDB.DataSource" oConn.Open = "http://myServerName/myVirtualRootName"

For more information, see:  Exchange OLE DB ProviderMessaging, Calendaring, Contacts, and Exchange using ADO objects

To view Microsoft KB articles related to OLE DB Provider for Exchange, click here 


Actually there is no OLE DB Provider for Excel.  However, you can use the OLE DB Provider for JET to read and write data in Microsoft Excel workbooks. Or you can use the ODBC Driver for Excel.


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 


oConn.Open "Provider=MSDAIPP.DSO;" & _ "Data Source=http://mywebsite/myDir;" & _ "User Id=myUsername;" & _ "Password=myPassword"

' Or

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 


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=" %>

This assumes the MDB is in the same directory where the ASP page is running. Also make sure this directory has Write permissions for the user account.
 

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 JetQ191754, 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"""

'Then open a recordset based on a select on the actual file

oRs.Open "Select * From MyTextFile.txt", oConn, _ adOpenStatic, adLockReadOnly, adCmdText 

For more information, see:  Q262537

 
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 


oConn.Open "Provider=MySQLProv;" & _ "Data Source=mySQLDB;" & _ "User Id=myUsername;" & _ "Password=myPassword" 

For more information, see:   API - OLE DB, SWSoft, and Snippet


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 


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 


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 


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


oConn.Open "Provider=PervasiveOLEDB;" & _ "Data Source=C:\PervasiveEB" 

For more information, see:  OLE DB - ADO


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 


oConn.Open "Provider=SQLBaseOLEDB;" & _ "Data source=mySybaseServer;" & _ "Location=mySybaseDB;" & _ "User Id=myUserName;" & _ "Password=myUserPassword"

For more information, see:  Books on-line   There is a one-time free sign-up,  then select "SQLBase OLE DB Data Provider User's Guide for v7.5 (20-6220-0001)", then download the zip file and extract the document.


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"

Note: In order to connect to a SQL Server 2000 "named instance", you must have MDAC 2.6 (or greater) installed.
 

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 


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 


oConn.Open "Provider=ASAProv;" & _ "Data source=myASA"

For more information, see:  ASA Programming Interfaces Guide and ASA User's Guide


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:  Sybase Advance Search   


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.


oConn.Open "Provider=Ardent.UniOLEDB;" & _ "Data source=myServer;" & _ "Location=myDatabase;" & _ "User ID=myUsername;" & _ "Password=myPassword" 

For more information, see: Ardent Using UniOLEDB 5.1Informix Using UniOLEDB 5.2


oConn.Open "Provider=vfpoledb;" & _ "Data Source=C:\vfp7\Samples\Data\myVFPDB.dbc;" & _ "Mode=ReadWrite|Share Deny None;" & _ "Collating Sequence=MACHINE;" & _ "Password=''" 

For more information, see: Microsoft OLE DB Provider for Visual FoxPro

To view Microsoft KB articles related to OLE DB Provider for Visual FoxPro, click here.

Note: The Visual FoxPro OLE DB Provider is NOT installed by MDAC 2.x.  You must install Visual FoxPro 7.0 in order to get it's OLE DB Provider.





Remote Data Service (RDS) Connections

The following examples show how to connect to a remote database using the RDS Data Control. When using the RDS DataControl's Server/Connect / SQL properties, the RDS DataControl uses the RDS DataFactory on the remote server.  If you use the RDS DataControl's 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 configured correctly! For more information, see RDS FAQ #24 

WARNING: RDS is considered obsolete by Microsoft
 

With the RDS default handler disabled

With oRdc .Server = "http://myServerName" .Sql = "Select * From Authors Where State = 'CA'" .Connect = "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=pubs;" & _ "User Id=myUsername;" & _ "Password=myPassword" .Refresh End With 

With the RDS default handler enabled

With oRdc .Server = "http://myServerName" .Handler = "MSDFMAP.Handler" .Connect = "Data Source=MyConnectTag;" .Sql = "MySQLTag(""CA"")" .Refresh End With

The corresponding CONNECT and SQL sections in the default handler \WINNT\MSDFMAP.INI  file would be:

[connect MyConnectTag] Access = ReadWrite Connect = "Provider=sqloledb; Data Source=(local); Initial Catalog=pubs; User Id=sa; Password="  (put all of this on single line!) [sql MySQLTag] Sql = "Select * From Authors Where State = '?'"

For more information about the RDS Default Handler, see: Q243245, Q230680, and RDS Customization Handler Microsoft articles

To view Microsoft KB articles related to RDS, click here 

To get records from a remote database

With oRdc .URL = "http://myServerName/AuthorsGet.asp?state=CA" .Refresh End With 

To save, set the URL property to an ASP web page

With oRdc .URL = "http://myServerName/AuthorsSave.asp" .SubmitChanges End With
Note: You must use MDAC 2.5 (or greater) for this feature

For more information, see:  RDS URL Property

To view Microsoft KB articles related to RDS, click here 


MS Remote Provider Connections

The following connections strings use Microsoft's remote provider  (MS Remote).  The MS Remote provider tells ADO to communicate  with the remote server (via the RDS DataFactory) and to use the  remote provider that is installed on the remote server.

WARNING:  The RDS DataFactory can be a major security hole if not setup and configured correctly!  For more information, see RDS FAQ #24 

WARNING: RDS is considered obsolete by Microsoft
 

If you want to use an ODBC DSN on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Remote Provider=MSDASQL;" & _ "DSN=AdvWorks;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

If you want to use an OLE DB Provider on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\mydb.mdb", _ "admin", "" 

If you want to use an OLE DB Provider on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Handler=MSDFMAP.Handler;" & _ "Data Source=MyAdvworksConn"

The corresponding entry in the \winnt\Msdfmap.ini file would be:

[connect MyAdvworksConn] Access = ReadWrite Connect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=mydb.mdb; User Id=admin; Password=" (put all of this on single line!)
 

If you want to use an ODBC DSN on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Remote Provider=MSDASQL;" & _ "DSN=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" 

If you want to use an OLE DB Provider on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Remote Provider=SQLOLEDB;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword" 

If you want to use an OLE DB Provider on the remote machine

oConn.Open "Provider=MS Remote;" & _ "Remote Server=http://myServerName;" & _ "Handler=MSDFMAP.Handler;" & _ "Data Source=MyPubsConn" 

The corresponding entry in the \winnt\Msdfmap.ini file would be:

[connect MyPubsConn] Access = ReadWrite Connect = "Provider=SQLOLEDB; Data Source=myServerName; Initial Catalog=myDatabaseName; User ID=myUsername; Password=myPassword" (put all of this on single line!)

For more information, see:  Microsoft OLE DB Remoting Provider   and  Q240838

To view Microsoft KB articles related to MS Remote, click here 
To view Microsoft KB articles related to RDS, click here 



ADO URL Connections

ADO 2.5+ allows you to open up a Recordset based on XML returned from an ASP file over HTTP.  This feature doesn't use RDS at all.

To get records from a remote database

oRs.Open "http://myServer/AuthorsGetByState.asp?state=CA",, _ adOpenStatic, adLockBatchOptimistic

To save changes

' Save Recordset into Stream Set oStm = New ADODB.Stream oRs.Save oStm, adPersistXML ' Use MSXML's XMLHTTP object to open ASP Set oXMLHTTP = New MSXML2.XMLHTTP30 oXMLHTTP.Open "POST", "http://myServerName/AuthorsSave.asp" oXMLHTTP.Send oStm.ReadText ' If an error occurred If oXMLHTTP.Status = 500 Then Debug.Print oXMLHTTP.statusText End If

For more information, see:  ADO Recordset's Open Method




.NET Data Provider Connections

The SQL Server .NET Data Provide allows you to connect to a Microsoft SQL Server 7.0 or 2000 databases.  

For Microsoft SQL Server 6.5 or earlier, use the OLE DB .NET Data Provider with  the "SQL Server OLE DB Provider" (SQLOLEDB).

Note: The SQL Server .NET Data Provider knows which Provider it is.  Hence the "provider=" part of the connection string is not needed.

Using C#:

using System.Data.SqlClient; ... SqlConnection oSQLConn = new SqlConnection(); oSQLConn.ConnectionString = "Data Source=(local);" + "Initial Catalog=mySQLServerDBName;" + "Integrated Security=yes"; oSQLConn.Open(); 

Using VB.NET:

Imports System.Data.SqlClient ... Dim oSQLConn As SqlConnection = New SqlConnection() oSQLConn.ConnectionString = "Data Source=(local);" & _ "Initial Catalog=mySQLServerDBName;" & _ "Integrated Security=yes" oSQLConn.Open() 

If connection to a remote server (via IP address):

oSQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _ "Data Source=xxx.xxx.xxx.xxx,1433;" & _ "Initial Catalog=mySQLServerDBName;" & _ "User ID=myUsername;" & _ "Password=myPassword"

Where:
- "Network Library=DBMSSOCN" tells SqlConnection to use TCP/IP 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:  System.Data.SQL Namespace, Q308656, and .NET Data Providers

Note: Microsoft SQLXML Managed Classes exposes the functionality of SQLXML inside the Microsoft .NET Framework.

To view Microsoft KB articles related to SQLClient, click here 


The OLE DB .NET Data Provider uses native OLE DB through COM interop to enable data access.  

To use the OLE DB .NET Data Provider, you must also use an OLE DB provider (e.g.  SQLOLEDB, MSDAORA, or Microsoft.JET.OLEDB.4.0).

For IBM AS/400 OLE DB Provider

' VB.NET Dim oOleDbConnection As OleDb.OleDbConnection Dim sConnString As String = _ "Provider=IBMDA400.DataSource.1;" & _ "Data source=myAS400DbName;" & _ "User Id=myUsername;" & _ "Password=myPassword" oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() 

For JET OLE DB Provider

' VB.NET Dim oOleDbConnection As OleDb.OleDbConnection Dim sConnString As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\myPath\myJet.mdb;" & _ "User ID=Admin;" & _ "Password="  oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() 

For Oracle OLE DB Provider

' VB.NET Dim oOleDbConnection As OleDb.OleDbConnection Dim sConnString As String = _ "Provider=OraOLEDB.Oracle;" & _ "Data Source=MyOracleDB;" & _ "User ID=myUsername;" & _ "Password=myPassword"  oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() 

For SQL Server OLE DB Provider

' VB.NET Dim oOleDbConnection As OleDb.OleDbConnection Dim sConnString As String = _ "Provider=sqloledb;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User Id=myUsername;" & _ "Password=myPassword"  oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() 

For Sybase ASE OLE DB Provider

' VB.NET Dim oOleDbConnection As OleDb.OleDbConnection Dim sConnString As String = _ "Provider=Sybase ASE OLE DB Provider;" & _ "Data Source=MyDataSourceName;" & _ "Server Name=MyServerName;" & _ "Database=MyDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword"  oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open()

For more information, see:  System.Data.OleDb Namespace and .NET Data Providers

To view Microsoft KB articles related to OleDbConnection, click here 


The ODBC .NET Data Provider is an add-on component to the .NET Framework SDK. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.

For SQL Server ODBC Driver

' VB.NET Dim oODBCConnection As Odbc.OdbcConnection Dim sConnString As String = _ "Driver={SQL Server};" & _ "Server=MySQLServerName;" & _ "Database=MyDatabaseName;" & _ "Uid=MyUsername;" & _ "Pwd=MyPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() 

For Oracle ODBC Driver

' VB.NET Dim oODBCConnection As Odbc.OdbcConnection Dim sConnString As String = _ "Driver={Microsoft ODBC for Oracle};" & _ "Server=OracleServer.world;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() 

For Access (JET) ODBC Driver

' VB.NET Dim oODBCConnection As Odbc.OdbcConnection Dim sConnString As String = _ "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "Uid=Admin;" & _ "Pwd=" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() 

For Sybase System 11 ODBC Driver

// C# string myConnStr = "Driver={Sybase System 11};" + "SRVR=mySybaseServerName;" + "DB=myDatabaseName;" + "UID=myUsername;" + "PWD=myPassword"; OdbcConnection myConnection = new OdbcConnection(myConnStr); myConnection.Open(); 

For all other ODBC Drivers

' VB.NET Dim oODBCConnection As Odbc.OdbcConnection Dim sConnString As String = "Dsn=myDsn;" & _                       "Uid=myUsername;" & _ "Pwd=myPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open()

For more information, see:  ODBC .Net Data Provider

To view Microsoft KB articles related to OdbcConnection, click here 


The .NET Framework Data Provider for Oracle is an add-on component to the .NET Framework that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software.  

Using C#:

using System.Data.OracleClient; OracleConnection oOracleConn = new OracleConnection(); oOracleConn.ConnectionString = "Data Source=Oracle8i;" + "Integrated Security=yes"; oOracleConn.Open(); 

Using VB.NET:

Imports System.Data.OracleClient  Dim oOracleConn As OracleConnection = New OracleConnection() oOracleConn.ConnectionString = "Data Source=Oracle8i;" & _ "Integrated Security=yes"; oOracleConn.Open()

Note: You must have the Oracle 8i Release 3 (8.1.7) Client or later installed in order for this provider to work correctly.

Note: You must have the RTM version of the .NET Framework installed in order for this provider to work correctly.

Note: There are known Oracle 7.3, Oracle 8.0, and Oracle9i client and server problems in this beta release. The server-side issues should be resolved in the final release of the product.  However, Oracle 7.3 client will not be supported.

For more information, see:   .NET Data Provider for Oracle Beta 1

To view Microsoft KB articles related to OracleConnection, click here 


The MySQL .NET Native Provider is an add-on component to the .NET Framework that allows you to access the MySQL database through the native protocol, without going through OLE DB.

Using C#

using EID.MySqlClient; MySqlConnection oMySqlConn = new MySqlConnection(); oMySqlConn.ConnectionString = "Data Source=localhost;" + "Database=mySQLDatabase;" + "User ID=myUsername;" + "Password=myPassword;" + "Command Logging=false"; oMySqlConn.Open(); 

Using VB.NET

Imports EID.MySqlClient  Dim oMySqlConn As MySqlConnection = New MySqlConnection() oMySqlConn.ConnectionString = "Data Source=localhost;" & _ "Database=mySQLDatabase;" & _ "User ID=myUsername;" & _ "Password=myPassword;" & _ "Command Logging=false" oMySqlConn.Open() 

For more information, see:   EID's MySQL ADO.NET native provider

신고
Posted by 나비 나비:D

원문 : http://support.microsoft.com/default.aspx?scid=kb;KO;193225


요약

본 문서에는 Microsoft Internet Information Server(IIS) 및 Active Server Pages(ASP)와 함께 사용하도록 권장되는 Oracle 클라이언트 소프트웨어 버전과 ODBC 드라이버 버전이 포함되어 있습니다.

응용 프로그램을 위한 안정된 플랫폼을 만들기 위해 올바른 버전을 사용하고 있는지, 그리고 적절한 Oracle 패치가 설치되어 있는지 확인하는 것이 좋습니다.

본 문서에는 ASP 또는 ADO를 Oracle 서버와 연결하기 위해 Internet Information Server를 올바르게 설치하는 방법을 보여주는 단계별 안내 예제도 포함되어 있습니다. 본 문서에서 제공하는 설치 안내는 예제일 뿐이며 모든 Oracle 클라이언트 소프트웨어 설치에 적용되는 것은 아닙니다. 자세한 내용은 Oracle 기술 지원부에 문의하십시오.

권장되는 소프트웨어 버전

권장되는 클라이언트 소프트웨어의 버전은 아래와 같습니다.

Oracle용 Microsoft ODBC 드라이버(Msorcl32.dll) 버전 2.573.2927 이상과 Microsoft Data Access Components 2.0


아래와 같은 버전이나 그 이상의 Oracle 소프트웨어를 권장합니다.
   Oracle 서버       7.3.3.0.0  7.3.4.0.0  8.0.3.0.0  8.0.4.0.0
   -------------     ---------  ---------  ---------  ---------
   RSF               7.3.3.5.3  7.3.4.2.0  8.0.3.2.3  8.0.4.3.2
   SQL*Net           2.3.3.0.5  2.3.4.0.4  N/A        N/A
   Net8              N/A        N/A        8.0.3.0.4  8.0.4.0.3

Oracle 기술 지원부에서 Oracle 업데이트와 패치를 구할 수도 있습니다.

Microsoft Data Access Components(MDAC) 2.0 설치는 아래 웹 사이트에서 구할 수 있습니다.
http://www.microsoft.com/data/mdac2.htm

중요: Oracle 클라이언트 소프트웨어(SQL*Net, Net8)는 Microsoft와 무관한 공급업체인 Oracle이 만드는 제품으로서 Microsoft는 이 제품의 성능과 신뢰성에 대해 어떠한 보증도 하지 않습니다.

추가 정보

아래는 클라이언트에서 Oracle 데이터베이스를 액세스하는 데 필요한 소프트웨어를 설치하기 위한 단계별 설치 예제입니다. 이 예제에서는 SQL*Net 버전 2.3.3.0.0 및 필요한 지원 파일 버전 7.3.3.0.0을 Windows NT 4.0 Server 컴퓨터에 설치합니다.

참고: 최적의 결과를 얻으려면 Windows NT 4.0(SP3)을 새로 설치하고 Internet Information Server를 설치한 컴퓨터에서 아래와 같이 구성하는 것이 좋습니다.

Microsoft Data Access Components 2.0 설치

Custom 설치를 선택한 경우에는 아래의 항목을 선택해야 합니다.
  1. ODBC Components
  2. OLE DB Components
  3. ODBC Driver for Oracle Databases
  4. OLE DB Provider for Oracle Databases
  5. Microsoft ActiveX Data Objects

참고: Microsoft Data Access Components 2.0 설치를 아래 웹 사이트에서 구할 수 있습니다.
http://www.microsoft.com/data/mdac2.htm

필요한 Oracle 지원 파일 및 SQL*Net 설치

Active Server Pages는 ActiveX Data Objects(ADO)를 사용하여 ODBC(Open Database Connectivity)를 통해 Oracle과 통신합니다. 이 연결을 사용 가능하게 하려면 웹 서버에 Oracle의 클라이언트 소프트웨어를 설치합니다.
  1. Oracle 소프트웨어 CD의 루트 디렉터리에서 설치 프로그램을 실행합니다.

    참고: 네트워크 드라이브에서 Oracle 소프트웨어를 설치하려면 네트워크 드라이브를 연결해야 합니다.
  2. 언어를 선택합니다.
  3. 설치 디렉터리를 선택합니다.
설치 디렉터리 경로 정보를 레지스트리에 추가해야 한다는 메시지가 나타납니다. Oracle 설치 프로그램이 자동으로 이 정보를 레지스트리에 추가합니다. 이제 설치 프로그램이 종료되고 컴퓨터를 다시 시작해야 합니다. 위의 단계를 반복합니다.
  1. Oracle7 클라이언트 제품을 선택합니다.
  2. 응용 프로그램 사용자를 선택합니다.
  3. Oracle 설명서를 하드 디스크 드라이브에 설치할 것인지 CD에서 직접 사용할 것인지 묻는 메시지가 나타나면 적절한 위치를 선택합니다.
이제 Oracle 클라이언트 설치 프로그램이 파일을 복사하고 클라이언트 소프트웨어를 구성합니다.

ODBC를 사용하여 Oracle 데이터베이스에 연결하려면 유효한 SQL*Net 연결 문자열을 제공해야 합니다. SQL Net Easy Configuration을 이용하면 Oracle 서버에 연결하기 위해 ODBC 데이터 원본 이름(DSN)에서 참조할 수 있는 별칭을 만들 수 있습니다.

데이터베이스 별칭 추가

  1. SQL*Net Easy Configuration을 실행합니다. 이를 위해 시작을 누르고 프로그램을 가리키고 Oracle for Windows NT를 선택한 다음 SQL Net Easy Configuration을 선택합니다.
  2. Add Database Alias를 선택합니다.
  3. 별칭을 위한 이름(예: 서버 이름)을 입력합니다.
  4. 프로토콜(대개 TCP/IP)을 선택합니다.
  5. 호스트 컴퓨터 및 데이터베이스 인스턴스를 지정합니다. 이것은 Oracle 서버 및 그 서버에 있는 데이터베이스의 이름입니다. 이 값들을 모르면 Oracle 데이터베이스 관리자(DBA)에게 문의하십시오.
  6. 설정을 확인합니다.
  7. Exit SQL*Net Easy Configuration을 선택합니다.

시스템 DSN 만들기

  1. 웹 서버의 제어판에서 32비트 ODBC Administrator(관리자)를 사용하여 시스템 DSN을 추가합니다.
  2. Oracle용 Microsoft ODBC 버전 2.573.2927을 선택합니다.
  3. Microsoft ODBC for Oracle 대화 상자에서 필요한 정보를 입력합니다. 사용자 이름은 Oracle DBA가 제공하며 "서버"는 SQL Net Easy Configuration에서 만든 별칭입니다.
       데이터 원본 이름: OracleDSN
       설명            : <서버 이름>에서 Oracle 서버 테스트(옵션)
       사용자 이름     : <Oracle DBA가 제공>
       서버            : <SQL*Net Easy Configuration 별칭>
    
    
옵션들을 기본값으로 둡니다.

ASP에서 Oracle 서버에 연결

아래와 같은 코드를 포함하는 Active Server Page를 만들어 Oracle 연결을 테스트할 수 있습니다. 연결 문자열은 유효한 사용자 ID와 암호를 가져야 하고 SQL 문은 유효한 테이블을 참조해야 합니다.
   <%@ Language=VBScript %>
   <html>
   <head>
   <title>Oracle Test</title>
   </head>
   <body>
   <center>
   <%
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "dsn=OracleDSN;uid=userid;pwd=password;"

     Set objRs = objConn.Execute("SELECT * FROM DEMO.EMPLOYEE")

     Response.Write "<table border=1 cellpadding=4>"
     Response.Write "<tr>"

     For I = 0 To objRS.Fields.Count - 1
       Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
     Next

     Response.Write "</tr>"

     Do While Not objRS.EOF
       Response.Write "<tr>"

       For I = 0 To objRS.Fields.Count - 1
         Response.Write "<td>" & objRS(I) & "</td>"
       Next

       Response.Write "</tr>"

       objRS.MoveNext
     Loop

     Response.Write "</table>"

     objRs.Close
     objConn.Close
   %>
   </center>
   </body>
   </html>

Oracle과 IIS에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

189410: HOWTO: Enable or Disable Connection Pooling in IIS 4.0

178391: PRB: Oracle TNS: Authentication Service Initialization Error

참조

60781: Hardware and Software Third-Party Vendor List, L-P

본 문서의 정보는 다음의 제품에 적용됩니다.

  • Microsoft Internet Information Server 4.0
신고
Posted by 나비 나비:D

CommandType 속성

CommandType 속성은 Command 개체의 형식을 나타내며, 다음 표의 값을 지정할 수 있다.

상수 설명
adCmdUnspecified -1 CommandType 인수를 지정하지 않는다.
adCmdUnknown 8 디폴트 값, Command 개체의 타입을 알 수 없음으로 지정한다.
adCmdText 1 명령어 또는 저장 프로시저를 텍스트로 지정한다.
adCmdTable 2 테이블을 지정한다(내부적으로 생성된 SQL 문장에 의해서 필드가 생성된다).
adCmdStoredProc 4 저장 프로시저를 지정한다.
adCmdFile 256 Persisted Recordset 개체의 파일명을 지정한다.
adCmdTableDirect 512 테이블을 지정한다(모든 필드가 생성된다).

CommandType 속성의 값이 디폴트 값인 adCmdUnknown일 경우에는 CommandText 속성이 SQL 문장인지, 저장 프로시저인지, 테이블의 이름인지를 결정하기 위해서 ADO는 Provider를 호출하게 되고, CommandType 속성을 정확하게 지정한 경우 보다 Command 개체가 수행하기 위해 부하가 더 걸리게 된다.
그렇기 때문에 CommandType 속성을 정확하게 지정하고 사용하면 디폴트 값을 사용하는 것보다 빠른 속도로 명령을 실행하게 된다.
CommandType 속성에 지정한 값과 실제 실행에 사용될 명령의 형식이 맞지 않을 경우에는 Execte 메서드를 사용할 때 오류가 발생하게 된다.

Execute 메서드를 사용할 때 함께 사용할 수 있는 옵션으로 adExecuteNoRecords가 있다. 이 옵션은 명령 또는 저장 프로시저가 반환 값을 가지지 않을 경우에 지정한다.

adExecuteNoRecords 상수는 최소한의 내부 프로세스만을 거치기 때문에 속도는 빠른 반면에 항상 adCmdText 또는 adCmdStoredProc 상수와 함께 사용해야 한다.

[예제]

다음 예제는 CommandType 속성을 adCmdText로 지정해서 Command 개체를 수행하는 예이다.

Private Sub cmdCommandType_Click()
   Dim adoCn As New ADODB.Connection
   Dim adoCmd As New ADODB.Command
   Dim strConn As String

   strConn = "Provider=sqloledb;" & _
         "Data Source=(local);Initial Catalog=Pubs;User ID=sa;Password=; "

   adoCn.Open strConn

   Set adoCmd.ActiveConnection = adoCn

   adoCmd.CommandText = "Update Authors Set au_lname = 'Chris' " & _
         " Where au_id = '172-32-1176'"

   adoCmd.CommandType = adCmdText

   adoCmd.Execute

   Set adoCmd = Nothing

   adoCn.Close
   Set adoCn = Nothing
End Sub

신고
Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2017/10   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
Total : 882,093
Today : 84 Yesterday : 146