查询在不同Web服务器上的数据库

使用ADO

dim cn as adodb.connection
dim cmd as adodb.command
dim rs as adodb.resultset

    Set cn  = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = gstrConnectionString
        .Open
    End With
    Set adoCommand = New ADODB.Command
    With adoCommand
        .ActiveConnection = gadoConnection
        .CommandType = adCmdStoredProc
        .CommandText = "<stored proc name>"
        Set adoRS = .Execute()
        adoRS.CacheSize = 100
    End With

for SQL Server the connection string would look something like the following:
Provider=SQLOLEDB.1;Password=<password>;Persist Security Info=True;User ID=<username>;Initial Catalog=<db_name>;Data Source=<servername>

使用DAO方式

Sub ConnectionObjectX()

    Dim wrkJet as Workspace
    Dim dbsNorthwind As Database
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim conPubs2 As Connection
    Dim conLoop As Connection
    Dim prpLoop As Property

    ' Open Microsoft Jet Database object.
    Set wrkJet = CreateWorkspace("NewJetWorkspace", _
        "admin", "", dbUseJet)
    Set dbsNorthwind = wrkJet.OpenDatabase("<full UNC path>\<databasename.mdb>")

    ' Create ODBCDirect Workspace object and open Connection
    ' objects.
    Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
        "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
    Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
        True, "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    Debug.Print "Database properties:"

    With dbsNorthwind
        ' Enumerate Properties collection of Database object.
        For Each prpLoop In .Properties
            On Error Resume Next
            Debug.Print "  " & prpLoop.Name & " = " & _
                prpLoop.Value
            On Error GoTo 0
        Next prpLoop
    End With

    ' Enumerate the Connections collection.
    For Each conLoop In wrkODBC.Connections
        Debug.Print "Connection properties for " & _
            conLoop.Name & ":"

        With conLoop
            ' Print property values by explicitly calling each
            ' Property object; the Connection object does not
            ' support a Properties collection.
            Debug.Print "  Connect = " & .Connect
            ' Property actually returns a Database object.
            Debug.Print "  Database[.Name] = " & _
                .Database.Name
            Debug.Print "  Name = " & .Name
            Debug.Print "  QueryTimeout = " & .QueryTimeout
            Debug.Print "  RecordsAffected = " & _
                .RecordsAffected
            Debug.Print "  StillExecuting = " & _
                .StillExecuting
            Debug.Print "  Transactions = " & .Transactions
            Debug.Print "  Updatable = " & .Updatable
        End With

    Next conLoop

    dbsNorthwind.Close
    conPubs.Close
    conPubs2.Close
    wrkJet.Close
    wrkODBC.Close

End Sub