Access & SQL Server)

'Project-reference Microsoft ActiveX Data Objects 2.8 Library

'Datamodule Module

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.exe cute (SQL)
End sub

 

'Call example

Private sub commandateclick () '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