ORACLE CONNECTION SAMPLES

DSN-LESS CONNECTION TO ORACLE WITH ADO

This sample simply presents a message box indicating the Record Count to demonstrate a DSN-Less Connection. The sample included in the project documented in my Oracle Tutorial Web Site is more detailed and extensive.

Private Sub Command1_Click()

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

cn.CursorLocation = adUseClient

cn.Open "Driver={Microsoft ODBC for Oracle}; " & _

"CONNECTSTRING=orcl;uid=scott;pwd=tiger;"

rs.Open "select * from emp", cn

MsgBox rs.RecordCount

rs.Close

cn.Close

End Sub

A COUPLE OF NOTES RELATING TO THE DSN-LESS CONNECTION:

It is not necessary to use Server= or Database= in your connection. The UID and PWD will reflect the database to which you have access to connect.

Notice that the CONNECTSTRING=orcl rather than the name of the server on which Oracle is installed (VBResource). orcl is an Alias for the database. If you used CONNECTSTRING=vbresource, it would result in the following error:

Run Time Error -2147467259(80004005)
MS ODBC For Oracle ORA-12154 "TNS Could Not Resolve Service Name

The Service Name referred to in this error is the name of the database on the server.