Here is a snippet that I use.
Public cnnADO As ADODB.Connection
Public Sub bOpenADOConnection(bADOcnnOpened As Boolean, sPassword as string, sUserID as string, sDSN as string)
On Error GoTo err_Proc
Dim sCnnADO As String
'establish connection to oracle
sCnnADO = "Provider=MSDASQL.1;Password=" & sPassword & ";Persist Security Info=True;User ID=" & sUserID & ";Data Source=" & sDSN
With cnnADO
.CursorLocation = adUseClient
.ConnectionString = sCnnADO
End With
cnnADO.Open
bADOcnnOpened = True
exit_Proc:
Exit Sub
err_Proc:
'create error handling routine
bADOcnnOpened = False
Resume exit_Proc
End Sub