Solved

VBA DAO to Oracle...Recordset

Posted on 1998-12-02
11
2,391 Views
Last Modified: 2013-12-25
I'm trying to open a recordset with the following code.

Sub RetrieveRecOracle()
    Dim wrk As Workspace
    Dim cnn As Connection
    Dim rst As Recordset
    Dim strConnect As String
    Dim strSql As String
    Dim array1 As Variant
    Dim wksh As Worksheet
    Dim Numrecs
    Dim i As Integer
    Dim j As Integer
    Dim fld As Field
    Dim MyError As Error
    Dim qdf As QueryDef
   
   
    On Error GoTo trap
   
    strConnect = "ODBC;DSN=Oracle;UID=vc/vcowner"
   
    strSql = "select traffic_date, sum(usg) from vc_t30_daily_sum where traffic_date >= '01-NOV-98' and traffic_date <= '30-NOV-98' group by traffic_date"
   
    Set wrk = DBEngine.CreateWorkspace("OrclWrk", "vc", "", dbUseODBC)
    Set cnn = wrk.OpenConnection("Oracle", dbDriverNoPrompt, False, strConnect)
    wrk.DefaultCursorDriver = dbUseServerCursor
    Set rst = cnn.OpenRecordset(strSql, dbOpenDynamic)

    rst.MoveLast
    Numrecs = rst.RecordCount

I get an error from the MS Oracle ODBC driver "Cannot use keyset-driven cursor on join...or readonly result set"

I've tried using dbOpendynaset also.

Part 2
If I simplify the where clause to " traffic_date = '01-nov-98' and eliminate the grouping, the numrecs variable returns -1. Why?
0
Comment
Question by:gmoore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 4

Accepted Solution

by:
tomook earned 200 total points
ID: 1497938
Try using a snapshot or forward-only recordset, i.e.

    Set rst = cnn.OpenRecordset(strSql, dbOpenSnapshot)
or
    Set rst = cnn.OpenRecordset(strSql, dbOpenForwardOnly)

Now, as to why. Your table may have a key, the GROUP BY query you wrote certainly does not. Dynasets and dynamic recordsets require using a keyset-driven cursor. ODBC is probably using client-side cursors (the default). How does all this fit together? Since you need a keyset to open a dynamic cursor, and since the cursor driver in Oracle cannot deliver any keys, you may not open a dynamic cursor.
0
 

Author Comment

by:gmoore
ID: 1497939
When using Snapshot or ForwardOnly using the group by query I get an error "Execution Cancelled" for no reason at all. Using a query without the group by I get an Invalid Operation on the ForwardOnly and no error on Snapshot. So I'm going to use snapshot for now but need to know what is causing the "Execution Cancelled" error when using the group by query.

Also if recordcount is greater than a 100 that's when numrecs returns a -1, is there any property to increase that limit. I've tried RST.CACHESIZE but I get an "Operation not Supported for this Object" error.
0
 

Author Comment

by:gmoore
ID: 1497940
When using Snapshot or ForwardOnly using the group by query I get an error "Execution Cancelled" for no reason at all. Using a query without the group by I get an Invalid Operation on the ForwardOnly and no error on Snapshot. So I'm going to use snapshot for now but need to know what is causing the "Execution Cancelled" error when using the group by query.

Also if recordcount is greater than a 100 that's when numrecs returns a -1, is there any property to increase that limit. I've tried RST.CACHESIZE but I get an "Operation not Supported for this Object" error.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:tomook
ID: 1497941
>> Also if recordcount is greater than a 100 that's when numrecs returns a -1, is there any property to increase that limit. I've tried RST.CACHESIZE but I get an "Operation not Supported for this Object" error.

-1 means it does not know how many there are yet. ODBC, by default, keeps a read-ahead buffer of 100 records. That is why if there are less than 100, you see the actual number of records. If you do a MoveLast, you will get an accurate count <grin>

Which driver and what version are you using? What version of VB are you working in? RDO might work better for you. It avoids the DAO layer and allows better control. Let me know if you would like to try. If so, also let me know what version of RDO you have.
0
 

Author Comment

by:gmoore
ID: 1497942
The rest of my code:

    rst.MoveLast
    Numrecs = rst.RecordCount
    rst.MoveFirst  
   
    array1 = rst.GetRows(Numrecs)
    Set wksh = Sheets("Sheet3")
    wksh.Activate
    wksh.Cells.ClearContents
    Application.ScreenUpdating = False
    For j = 0 To Numrecs - 1
        For i = 0 To UBound(array1, 1)
            If IsNull(array1(i, j)) Then
                wksh.Range("A1").Offset(j, i).Value = ""
            Else
                wksh.Range("A1").Offset(j, i).Value = CStr(array1(i, j))
            End If
        Next i
    Next j

I do movelast before checking record count, and I still get -1 for numrecs. If numrecs = -1 then getrows() will error. The goal is to display the results to the excel spreadsheet.

Driver: Microsoft ODBC for Oracle
Version: 2.573
File: msorcl32.dll 6/1/98

VB Version: Using Excel 97 SR-2; DAO 3.51

Where do I find RDO version? Never been exposed to RDO.

0
 
LVL 4

Expert Comment

by:tomook
ID: 1497943
Go into the VBA editor in Excel and open your references (Tools->References). If RDO is installed, you will see a listing for "Microsoft Remote Data Object 2.0". If you do not have it, I think it can be downloaded free now, but I believe it came with Office. We can also look at using ADO if you do not have RDO. Your version of Excel will be fine. Let me know what you have, and I can give you an example.
0
 

Author Comment

by:gmoore
ID: 1497944
I have version 2.0
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497945
Sorry for the delay. I took the whole weekend off. I will build an example today and post it.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497946
(Add a reference to RDO 2.0)

' Note: no error handling code is included.
Function TestRDO()
Dim rdoConn As RDO.rdoConnection
Dim rdoRS As RDO.rdoResultset
Dim strConnect As String
Dim strSQL As String
Dim lNumCols As Long
Dim lCntCols As Long
Dim lCntRows As Long
Dim wksh As Worksheet


    strConnect = "ODBC;DSN=Oracle;UID=vc/vcowner"

    Set wksh = Sheets("Sheet3")
    wksh.Activate
    wksh.Cells.ClearContents

    Set rdoConn = New RDO.rdoConnection
    rdoConn.Connect = strConnect
    rdoConn.CursorDriver = rdUseServer
    rdoConn.EstablishConnection
   
    strSQL = "select traffic_date, sum(usg) from vc_t30_daily_sum where traffic_date >= '01-NOV-98' and traffic_date <= '30-NOV-98' group by traffic_date"
    Set rdoRS = rdoConn.OpenResultset(strSQL)   ' Opens read-only by default
    ' If the line above fails, a trappable error will occur
   
    lCntRows = 1
    lNumCols = rdoRS.rdoColumns.Count
    With rdoRS
        Do While Not rdoRS.EOF
            For lCntCols = 0 To lNumCols - 1
                If IsNull(.rdoColumns(lCntCols)) Then
                    ' I hope I did not switch thw row and columns
                    wksh.Range("A1").Offset(lCntCols, lCntRows).Value = ""
                Else
                    wksh.Range("A1").Offset(lCntCols, lCntRows).Value = .rdoColumns(lCntCols)
                End If
            Next lCntCols
            lCntRows = lCntRows + 1
            .MoveNext
        Loop
    End With
   
    ' Clean up
    rdoRS.Close
    Set rdoRS = Nothing
    rdoConn.Close
    Set rdoConn = Nothing
    Set wksh = Nothing
End Function

I eliminated the array entirely. With RDO, it should not be needed. I regularly pull a thousand records from an Oracle server and populate grids or spreadsheets in 1-3 seconds, depending on the speed of the computer.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497947
You might have to change the connect string to:

strConnect = "DSN=Oracle;UID=vc/vcowner"

0
 

Author Comment

by:gmoore
ID: 1497948
Thank you I will try it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question