ActiveX Data Object Simple Routines

These groups of Procedures will allow you to open databases, run SQL statements, as well as execute Stored Procedures without hassle...

'Be sure to include Microsoft ActiveX Data Objects Library (any version)

Global dbLocation As String <--- if using dsn less connection and referring to physical location of Database
'dblocation = App.path & "\database.mdb" or App.path & "\database\database.mdb" or any location string

Private Conn As New ADODB.Connection
Private isOpen As Boolean

Sub connOpen()
   Set Conn = New ADODB.Connection
'    connString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" <---choose connection type
'    connString = "PROVIDER=MSDataShape;dsn=dsnName"
'    connString = "dsn=dsnName"
   Conn.Open connString & dbLocation
   isOpen = True
End Sub

Sub rsOpen(ByRef RecSet, ByVal SQL As String)
   If Not isOpen Then Call connOpen
   Set RecSet = New ADODB.Recordset
   RecSet.CursorLocation = adUseClient
   RecSet.Open SQL, Conn, adOpenDynamic, adLockOptimistic
End Sub

Sub rsExec(ByRef RecSet, ByVal SQL As String)
   If Not isOpen Then Call connOpen
   Conn.CursorLocation = adUseClient
   Set RecSet = New ADODB.Recordset
   Set RecSet = Conn.Execute(SQL)
End Sub

Sub rsClose(ByRef RecSet)
   On Error Resume Next
   RS.CancelUpdate
   RecSet.Close
   Set RecSet = Nothing
End Sub

Sub connClose()
   If isOpen Then
       Conn.Close
       Set Conn = Nothing
       isOpen = False
   End If
End Sub

You might also like...

Comments

Macky M

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”