ＶＢ中数据库的常用连接方式

Option Explicit

Public Function ExecuteSQL(ByVal sql As String) As ADODB.Recordset                        'whd_project.dsn
Dim connectstring As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo executeSQL_Error
connectstring = "FileDSN=whd_project.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
sTokens = Split(sql)
If InStr("INSERT,DELETE,UPDATE,EXECUTE", VBA.UCase$(sTokens(0))) Then
cnn.Execute (sql)
Else
Set rst = New ADODB.Recordset
rst.Open VBA.Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
End If
executeSQL_Error:
Exit Function
End Function

Public Function ExecuteSQLGld(ByVal sql As String) As ADODB.Recordset                     'whd_gld.dsn
Dim connectstring As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
On Error GoTo executeSQL_Error
connectstring = "FileDSN=gld.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
cnn.Execute (sql)
Set rst = New ADODB.Recordset
rst.Open VBA.Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQLGld = rst

executeSQL_Error:

Exit Function
End Function

Public Sub ExecuteINsSQLGld(ByVal sql As String)                                           'whd_gld.dsn(礚)
Dim connectstring As String
Dim cnn As ADODB.Connection

On Error GoTo executeSQL_Error
connectstring = "FileDSN=gld.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
cnn.Execute (sql)

executeSQL_Error:

Exit Sub
End Sub

Public Function ExecuteSQLBom(ByVal sql As String) As ADODB.Recordset                       'whd_bomtab.dsn
Dim connectstring As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'On Error GoTo executeSQL_Error
connectstring = "FileDSN=whd_bomtab.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
cnn.Execute (sql)
Set rst = New ADODB.Recordset
rst.Open VBA.Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQLBom = rst

executeSQL_Error:
Exit Function
End Function

Public Function ExecuteSQLb(ByVal sql As String) As ADODB.Recordset                          '场更家遏(whd_bomtab)
Dim connectstring As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo executeSQL_Error
connectstring = "FileDSN=whd_bomtab.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
sTokens = Split(sql)
If InStr("INSERT,DELETE,UPDATE,EXECUTE", VBA.UCase$(sTokens(0))) Then
cnn.Execute (sql)
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQLb = rst
End If
executeSQL_Error:
Exit Function
End Function

Public Function ExecuteSQLBaoZh(ByVal sql As String) As ADODB.Recordset                         'whd_baozh.dsn
Dim connectstring As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'On Error GoTo executeSQL_Error
connectstring = "FileDSN=whd_baozh.dsn;uid=sa;pwd="
Set cnn = New ADODB.Connection
cnn.Open connectstring
cnn.Execute (sql)
Set rst = New ADODB.Recordset
rst.Open VBA.Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQLBaoZh = rst

executeSQL_Error:

Exit Function
End Function

Public Function existVal(ByVal table As String, ByVal ziduan As String) As Boolean
 Dim sql As String
 Dim rst As ADODB.Recordset
 sql = "select *  from " & table & " where xm='" & ziduan & "'"
 Set rst = ExecuteSQLGld(sql)
 If rst.RecordCount = 0 Then
 existVal = False
 Else
 existVal = True
 End If
 End Function