Re: Problem with x64 Oracle OLE DB Provider, MS ADO, server side forward cursor

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 4 Mar 2009 15:00:44 -0800 (PST)
Message-ID: <b5158616-3b26-4ac3-93eb-51ad557b8403_at_s36g2000vbp.googlegroups.com>

On Mar 4, 5:50 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> This looks like an interesting problem.  You are using an implicit
> connection on the recordset, and I usually use a client side
> connection.  Try this to see if it works for you:
> Dim strSQL
> Dim strUsername
> Dim strPassword
> Dim strDatabase
> Dim snpData
> Dim dbDatabase
> Dom cnt
>
> Set snpData = CreateObject("ADODB.Recordset")
> Set dbDatabase = CreateObject("ADODB.Connection")
>
> strUsername = "user-here"
> strPassword = "password-here"
> strDatabase = "database-here"
>
> dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source="
> & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword
> & ";FetchSize=100;"
> dbDatabase.Open
> dbDatabase.CursorLocation = adUseClient
>
> strSQL = "SELECT * FROM MYTABLE"
>
> snpData.Open strSQL, dbDatabase
>
> cnt = 0
> If snpData.State = 1 Then
>     Do While Not(snpData.EOF)
>         cnt = cnt + 1
>         snpData.MoveNext
>     Loop
> End If
>
> MsgBox CStr(cnt)
>
> Note that the FetchSize=100 in the connection string would be
> equivalent to snpData.CacheSize = 100 - almost the same any way (a
> 10046 trace will show why they are not the same).
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Small correction (must set the CursorLocation first before opening): dbDatabase.CursorLocation = adUseClient
dbDatabase.Open

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Mar 04 2009 - 17:00:44 CST