' My Provider-driven Connection Strings Template
' (with assist from the Excel XP Data Import Wizard)
' ==================================================

' Last Updated 05-29-2003, 11:00 AM

' Data Shaping Provider

  With a
    .Provider = "MSDataShape"
    .Properties("Data Provider") = "SQLOLEDB.1"
    .Properties("Data Source") = "asqlserver\prod"
    .Properties("Initial Catalog") = "database"
    .Properties("Integrated Security") = "SSPI"
    .Open
  End With

' LDAP Directories

strConn  = "provider=ADSDSOObject"
strQuery = "select * from 'LDAP://NW/OU=End Users,OU=Accounts,DC=nw,DC=big,DC=bigger,DC=biggest' " & _
           "where objectcategory='person'"

' Indexing Service

strQuery = "select {[fields]|*} from scope('""[d:][path]\""')"
strConn = "provider=MSIDXS;" & _
          "Data Source=System"

' Access Connection

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=[d:][path]filename.mdb;" & _
          "Mode=Read;" & _
          "Extended Properties="


' Provider-driven connections
' ===========================

' SQL Server

  strConn  = "Provider=SQLOLEDB.1;" & _
             "Integrated Security=SSPI;" & _
             "Persist Security Info=True;" & _
             "Data Source=BETA35\DEV1;" & _
             "Initial Catalog=Pudniksoft;" 

  Set objConn = Server.CreateObject("ADODB.Connection")
  With objConn
    .ConnectionString = strConn
    .ConnectionTimeout = 3000
    .CommandTimeout = 3000
    .Open


' Oracle

strConn = "Provider=MSDAORA.1;" & _
          "User ID=[];" & _
          "Password=[];" & _
          "Data Source=SID.WORLD"


' DB2 (mainframe)
  
strConn = "Provider=MSDASQL.1;" & _
          "Persist Security Info=True;" & _
          "Extended Properties=" & Chr$(34) & _
          "UID=[];" & _
          "PWD=[];" & _
          "MODE=SHARE;" & _
          "DBALIAS=someserver;" & _
          "PATCH1=525440;" & _
          "PATCH2=126;" & Chr$(34) & _
          "Initial Catalog=somedb"

strConn = "DRIVER=IBM DB2 ODBC DRIVER;" & _
          "DBALIAS=someserver;" & _
          "Initial Catalog=somedb;" & _
          "MODE=SHARE;" & _
          "DESCRIPTION=RQS Counts Data Source;" & _
          "UID=[];" & _
          "PWD=[];" & _
          "PATCH1=525440;" & _
          "PATCH2=126"

'Web (ASP Pages)

strConn = "DRIVER={SQL Server};" & _
                      "SERVER=SQLSERVER\Instance1;" & _
                      "Database=database;" & _
                      "UID=[MachineAccount];" & _
                      "PWD=[MachineAccount]" 

' DTS, VBA Clients

strConn = "DRIVER={SQL Server};" & _
                      "SERVER=someserver;" & _
                      "DATABASE=somedb;" & _
                      "Regional=Yes;" & _
                      "Trusted_Connection=Yes"

'Comma-delimited Flat Files, no header row
' sample query: "select * from filename.csv"

strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
          "DBQ=[d:][path];" & _
          "UserCommitSync = Yes;" & _
          "Threads = 3;" & _
          "Extensions=txt,csv,tab,asc"

' XML Documents

' NOTE: the better way is to just open the file into a Recordset object directly:

objXMLRS = "[d:][path]filename.xml", , adOpenForwardOnly

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=[d:][path]filename.xml;" & _
                      "Extended Properties='text;HDR={NO | YES};FMT=XML';" & _
                      "Trusted_Connection=Yes"