Trouble with Connection To Oracle DB

[trc]

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.-->

 

[bjd4jc]

are you getting an error message?

 

[trc]

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.

http://www.tek-tips.com/faqs.cfm?&rat1=7&fid=43

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.-->

 

[BobRodes]

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.

 

[trc]

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.-->