Solved

VBA DAO to Oracle...Recordset

Posted on 1998-12-02
11
2,289 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now