Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble with Connection To Oracle DB

Status
Not open for further replies.

trc

MIS
Nov 27, 2000
126
CA
I am having trouble with connecting to an oracle DB. I have tried to follow several of the FAQs but have had no success.

Here is my code. What am I doing wrong? Thanks in advance.


Dim strCnn As String
Dim strSQL As String
Dim rsData As ADODB.Recordset
Dim cnnADODB As ADODB.Connection
Dim Acommand As New ADODB.Command

strCnn = "dsn=usertest;Driver={Microsoft ODBC for Oracle};Uid=cook;Pwd=barkm3;"
strSQL = "select * From drlhl_ex" ' where drlhl_ex_ref_no like '%MW%' order by drlhl_ex_ref_no asc

Set cnnADODB = New ADODB.Connection
cnnADODB.ConnectionString = strCnn

cnnADODB.Open strCnn
Acommand.ActiveConnection = cnnADODB
Acommand.CommandType = adCmdTable
Acommand.Execute
Set rsData = New ADODB.Recordset

rsData.Open strSQL, cnnADODB, adOpenDynamic, adLockOptimistic


MsgBox "done " & rsData.RecordCount

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->
 
Ok. I found my mistakes. It relly helps if you follow the faq to a T. I had two mistakes. First the open type was adOpenDynamic not adOpenKeyset and commad type was adCmdTable not adCmdText.

Here is the FAQ. It is very good. Thank you to for it.

Here is my corrected code.

Dim strCnn As String
Dim strSQL As String
Dim rsData As ADODB.Recordset
Dim cnnADODB As ADODB.Connection
Dim Acommand As New ADODB.Command

strCnn = "dsn=usertest;Driver={Microsoft ODBC for Oracle};Uid=cook;Pwd=barkm3;"
strSQL = "select * From drlhl_ex" ' where drlhl_ex_ref_no like '%MW%' order by drlhl_ex_ref_no asc

Set cnnADODB = New ADODB.Connection
'cnnADODB.ConnectionString = strCnn

cnnADODB.Open strCnn
Acommand.ActiveConnection = cnnADODB
Acommand.CommandText = strSQL
Acommand.CommandType = adCmdText
Acommand.Execute
Set rsData = New ADODB.Recordset

rsData.Open strSQL, cnnADODB, adOpenKeyset, adLockReadOnly ' , adOpenDynamic, adLockOptimistic


MsgBox "done " & rsData.RecordCount

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->
 
Just so you know, you'll see an abbreviated link like this one faq222-43 at the top of each faq and thread, which you can cut and paste into your post.
 
Thank you bjd4jc for the help.

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top