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