I use a general function like this which returns a connection object, so you just need to do:
Set cn = OpenConnection
Mysql = "SELECT Bla,Bla FROM BLABLA"
Set rs = cn.Execute(Mysql)
anyway, here's the function
Code:
Public Function OpenConnection() As ADODB.Connection
On Error GoTo OpenConnection_Fail
Dim nRetryCount As Integer
Set OpenConnection = CreateObject("ADODB.Connection")
Debug.Assert Not OpenConnection Is Nothing
With OpenConnection
' ### change the next line depending on where your connection string is stored ###
.ConnectionString = GetConnectionString
Do While .State <> adStateOpen And nRetryCount < 3
.Open
nRetryCount = nRetryCount + 1
Loop
If .State <> adStateOpen Then
Err.Raise vbObjectError+1024, "OpenConnection", "Unable to connect to database using " & .ConnectionString
End If
End With
Exit Function
OpenConnection_Fail:
Err.Raise Err.Number, Err.Source, Err.Description
End Function
I also use this to close the conn and all associated objects - the following example also closes an associated command and stream object, but you can substitute recordset or whatever else you like:
Code:
Public Sub DropConnection(ByRef cn As ADODB.Connection, Optional ByRef cmd As ADODB.Command = Nothing, Optional ByRef strm As ADODB.Stream = Nothing)
'===========================================================
' Description : generic function to drop a connection and associated objects
'===========================================================
On Error GoTo DropConnection_Fail
If Not strm Is Nothing Then
If strm.State = adStateOpen Then
strm.Close
End If
Set strm = Nothing
End If
If Not cmd Is Nothing Then
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
End If
If cn.State = adStateOpen Then
cn.Close
End If
Set cn = Nothing
Exit Sub
DropConnection_Fail:
Exit Sub
End Sub
hth
Phil
Connection String
