ADO内外网连接SQLSERVER得到数据集方法

时　间:2009-12-04 14:31:16
作　者:小继   ID：8060  城市：江阴
摘　要:通过内网不通则试外网地址，如全不通则退出，先内后外，保证最快连接。
正　文:

首先有个SQLSERVER服务器，其次内网外网都能联通，外网指不在公司通过公司外的网络可以联通此服务器。
Public SqlConn As New ADODB.Connection

一、分别创建通过内网地址和外网地址连接的方法
Function openDbnat() As Boolean '通过内网地址连通
On Error GoTo SQLConnErr
'Dim natConn As New ADODB.Connection
Dim strConnectnat As String
strConnectnat = "Provider=Microsoft.Access.OLEDB.10.0;" & _
                "Data Provider=SQLOLEDB.1;" & _
                  "Data Source=内网服务器地址;" & _
                 "Initial Catalog=数据库名;" & _
                 "User Id=数据库用户名;" & _
                  "Password=数据库密码;"
If SqlConn.State <> 0 Then SqlConn.Close
          SqlConn.ConnectionTimeout = 15
          SqlConn.Open strConnectnat
          openDbnat = True
          Debug.Print openDbnat
          Exit Function

' 建立选择语句.
SQLConnErr:
          openDbnat = False
          If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
          Debug.Print openDbnat
End Function

Function openDbwww() As Boolean  ’'通过外网地址连通
On Error GoTo SQLConnErr
'Dim natConn As New ADODB.Connection
Dim strConnect As String
' 使用Microsoft Data Shaping Service for OLE DB provider连接到sql server.
strConnect = "Provider=Microsoft.Access.OLEDB.10.0;" & _
                "Data Provider=SQLOLEDB.1;" & _
                  "Data Source=外网服务器地址;" & _
                 "Initial Catalog=数据库名;" & _
                 "User Id=数据库用户名;" & _
                  "Password=数据库密码;"
If SqlConn.State <> 0 Then SqlConn.Close
          SqlConn.ConnectionTimeout = 15
          SqlConn.Open strConnect
          openDbwww = True
          Debug.Print openDbwww
          Exit Function

'SQLConnErr:
          openDbwww = False
          If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
          Debug.Print openDbwww
End Function

Function PublicdbClose() '关闭连接
          If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
End Function

二、连接主方法

Function rstsets(strsql As String) As ADODB.Recordset  ‘联接主方法
'Dim strSQL As String
Dim rst As ADODB.Recordset
' 初始化ADO记录集.
If Not openDbnat Then   ’判内部连接成不成功
    Debug.Print "内部连接不成功"
    If openDbwww Then   ‘如内部连接不成功则判外部连接成不成功，如果成功则连接后取纪录集
    Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = SqlConn.SqlConn
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open strsql
    End With
   ' Debug.Print rst.RecordCount
   Set rstsets = rst
    Else
        Debug.Print "连接全没效，退出"   '如都不成功则退出
        Call PublicdbClose
        Exit Function
    End If
Else
    Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = SqlConn
        'SqlConn.ConnectionString
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open strsql
    End With
   ' Debug.Print rst.RecordCount
   Set rstsets = rst
End If
End Function