I'm not sure what you are asking specifically, but here is an example:
Set mstrSQLConnect to your connection path, i.e:
"Provider=SQLOLEDB;Data Source=SAI-SQL-SERVER;Initial
Catalog=NEWIFL;UID=SA;PWD=SYSADMIN"
Also you would need another sub "CriticalError" in the example given.
Basically, you just create your objects, make a connection, execute
your query and then destroy your objects and connection.
You probably will have many very similar functions like this which you
should put in their own module, modSQL for example.
*********************************
Public Function GetReportsList() As String
On Error GoTo DBEr
Dim strResults As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cn.Open mstrSQLConnect
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT ReportNo, Rev FROM IFLReport"
With rs
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
strResults = rs.GetString(, , ",", ",")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
GetReportsList = strResults
Exit Function
DBEr:
MsgBox "Database error in function GetReportsList", vbCritical
CriticalError
End Function
*********************************
