首先有个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