VB数据访问通用模块(Access & SQL SERVER)

' 工程－引用 Microsoft ActiveX Data Objects 2.8 Library

' DataModule 模块

Function  GetAccessConn( ByVal  FilePath  As   String )  As  ADODB.Connection  ' Access DB Connection
     Dim  conn  As   New  ADODB.Connection
    conn.Open  " Driver={Microsoft Access Driver (*.mdb)};DBQ= "   &  FilePath
     Set  GetAccessConn  =  conn
End Function

Function  GetSQLServerConn( ByVal  DBName, Server, UserID, Password  As   String )  As  ADODB.Connection  ' SQL Server DB Connection
     Dim  conn  As   New  ADODB.Connection
    conn.Open  " Driver={SQL Server};DataBase= "   &  DBName  &   " ;Server= "   &  Server  &   " ;UID= "   &  UserID  &   " ;PWD= "   &  Password
     Set  GetSQLServerConn  =  conn
End Function

Function  GetRecordSet( ByVal  SQL  As   String ,  ByRef  DBConn  As  ADODB.Connection)  As  ADODB.Recordset
     Dim  rs  As   New  ADODB.Recordset
    rs.CursorLocation  =  adUseClient
    rs.Open SQL, DBConn, adOpenDynamic, adLockOptimistic
     Set  GetRecordSet  =  rs
End Function

Sub  ExecuteSQL( ByVal  SQL  As   String ,  ByRef  DBConn  As  ADODB.Connection)
    DBConn.exeCute (SQL)
End Sub

 

' 调用示例

Private   Sub  Command1_Click()  ' Access
Dim  conn  As  ADODB.Connection
Dim  rs  As  ADODB.Recordset

Set  conn  =  DataModule.GetAccessConn( " C:aa.mdb " )
Set  rs  =  DataModule.GetRecordSet( " select top 10 * from [user] " , conn)

Set  DataGrid1.DataSource  =  rs
End Sub

Private   Sub  Command2_Click() ' SQL SERVER
Dim  conn  As  ADODB.Connection
Dim  rs  As  ADODB.Recordset

Set  conn  =  DataModule.GetSQLServerConn( " pubs " ,  " (local) " ,  " sa " ,  " 123 " )
Set  rs  =  DataModule.GetRecordSet( " select top 10 * from authors " , conn)

Set  DataGrid1.DataSource  =  rs

End Sub