Open Microsoft Visual Basic for Applications (Alt+F11)
Click Tools – References
Add “Microsoft ActiveX Data Objects 2.7 Library”
Create a new module (I tend to keep all my SQL code in a
module of its own e.g. mod_SQL)
Then create a new function or sub routine
Function myFunctionName(ByVal s_myString As String) As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
'Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _
"Initial Catalog=DBName;" & _
"User Id=Username; Password=Password"
'Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("SQL Statement")
'Check we have data.
If Not rs.EOF Then
myFunctionName = rs("Return Value")
' Close the recordset
rs.Close
Else
myFunctionName= -1
End If
'Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Function
