vb程式改寫方法。

1。經常出現的寫法

在VB中實現對資料庫的訪問，我們可能會通過一個全域函數來實現，例如以下的代碼：

Public Function DBSqlOpen(strName As String, strPWD As String, _
                            strIP As String, strDatabase As String) As ADODB.Connection
    'PurPose:   串連SQl資料庫
    'Accepts:
    '           strName-------登陸伺服器使用者名稱
    '           strPWD--------登陸伺服器密碼
    '           strIP---------伺服器位址
    '           strDatabase---資料庫名
    'Return:    連線物件
    Dim madocon As ADODB.Connection       '///////////////////////////////
    Set madocon = New ADODB.Connection
    madocon.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" _
   
& strName & ";pwd=" & strPWD & ";Initial Catalog="
& strDatabase & ";Data Source=" & strIP & ""
    madocon.Open
    Set DBSqlOpen = madocon
End Function

然後在其它地方要用到Ado串連的時候就通過該函數來返回。比如:
Private Sub Form_Load()
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.ActiveConnection = DBSqlOpen(strLocalName, strLocalPwd, "(local)", strLocalDb)
    ........
End Sub

程式中如果採用這種方式來實現串連資料庫，效率非常的低下。你每用到一次DBSqlOpen這個全域函數，全域函數裡面聲明的
AdoDB.Connection就會Open一次，假如你的程式頻繁調用這個全域函數，可想而之程式的效率將會多麼的低下。如何避免這種設計方式呢？我
們採用下面的方式。

2.改進的寫法

假如我們設計一個類來實現資料庫訪問就會避免這樣的問題。具體代碼如下：

Private m_adoConn As New ADODB.Connection
Private m_adoRecordSet As New ADODB.Recordset

Public Function DBInit()
    Set m_adoConn = Nothing
    Set m_adoRecordSet = Nothing
End Function

Public Function DBEnd()
    m_adoRecordSet.Close
    m_adoConn.Close
    Set m_adoRecordSet = Nothing
    Set m_conn = Nothing
End Function

Public Function DBSqlOpen(strName As String, strPWD As String, _
                            strIP As String, strDatabase As String)
    m_adoConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" _
   
& strName & ";pwd=" & strPWD & ";Initial Catalog="
& strDatabase & ";Data Source=" & strIP & ""
    m_adoConn.ConnectionTimeout = 5
    m_adoConn.Open
End Function

Public Function DBGetConn() As ADODB.Connection
    DBGetConn = m_adoConn
End Function

我們在模組裡面可以聲明一個Public m_DBClass As DBClass來實現對資料庫的訪問。

Private Sub Command1_Click()
    Dim rs1 As ADODB.Recordset
    rs1.ActiveConnection = m_DBClass.DBGetConn
End Sub

Private Sub Form_Load()
    Dim strName As String
    Dim strPWD As String
    Dim strIP As String
    Dim strDatabase As String
   
    strName = "sa"
    strPWD = ""
    strIP = "192.168.0.37"
    strDatabase = ""
   
    m_DBClass.DBInit
    m_DBClass.DBSqlOpen strName, strPWD, strIP, strDatabase
End Sub

Private Sub Form_Unload(Cancel As Integer)
    m_DBClass.DBEnd
End Sub

每一次要用到資料庫連接的時候我們就通過類裡面的DBGetConn方法來返回一個ADODB.Connection。