I connect to an Oracle database by the code below. Is it possible to improve the code? What could a function which returns data based on a ID in a Excel sheet look like =getName(id)? Is it bad syntax to make a function with a sql string-parameter like =getValue("SELECT * FROM db WHERE Id = id")? I am very new at db connections through vba.
Option Explicit
Public objConnection As New ADODB.Connection
Public Function Connect() As ADODB.Connection
Const strNavn As String = "DB"
Const strBrugernavn As String = "user"
Const strKodeord As String = "pass"
With objConnection
.Open "DSN=" & strNavn & "; UID=" & strBrugernavn & "; PWD=" & strKodeord & ";"
.CursorLocation = adUseServer
End With
Set Connect = objConnection
End Function
Public Function SelectQuery(strSQL As String) As ADODB.Recordset
If objConnection.State <> 1 Then
Set objConnection = Connect
End If
Set SelectQuery = New ADODB.Recordset
With SelectQuery
.CursorLocation = adUseServer
.Open strSQL, objConnection, adOpenForwardOnly
End With
End Function
Public Function InsertQuery(strSQL As String) As Boolean
If objConnection.State <> 1 Then
Set objConnection = Connect
End If
objConnection.Execute strSQL
End Function
Option Explicit
Public objConnection As New ADODB.Connection
Public Function Connect() As ADODB.Connection
Const strNavn As String = "DB"
Const strBrugernavn As String = "user"
Const strKodeord As String = "pass"
With objConnection
.Open "DSN=" & strNavn & "; UID=" & strBrugernavn & "; PWD=" & strKodeord & ";"
.CursorLocation = adUseServer
End With
Set Connect = objConnection
End Function
Public Function SelectQuery(strSQL As String) As ADODB.Recordset
If objConnection.State <> 1 Then
Set objConnection = Connect
End If
Set SelectQuery = New ADODB.Recordset
With SelectQuery
.CursorLocation = adUseServer
.Open strSQL, objConnection, adOpenForwardOnly
End With
End Function
Public Function InsertQuery(strSQL As String) As Boolean
If objConnection.State <> 1 Then
Set objConnection = Connect
End If
objConnection.Execute strSQL
End Function
