Solved

VBA DAO to Oracle...Recordset

Posted on 1998-12-02
11
2,349 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
  • 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
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.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 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