Not enough storage error

How do you export to Excel?

Like this:

            'create an excel file
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set txtfile = fso.CreateTextFile("c:\Armadillo" & strProject & ".csv", True)
            'execute oracle query passing projectid and lifecycleids
            qsql = "{call ProjectExport2.PExport(" & iproj & "," & lcid & ",{resultset 10000, issue_id,lifecycle_name,date_closed,date_opened,status_name,summary,team_leader,blocking_users,priority,impl_dt,fix_mgr,raised_in,sir_area,raisers_area,cycle,subcycle,analysed_by,analysed_dt,reviewed_by,reviewed_dt,concluded_by,concluded_dt})}"
            Set rs2 = cn.Execute(qsql)
            'write retrieved data into excel file
            txtfile.WriteLine ("ISSUE ID,LIFECYCLE NAME,DATE CLOSED, DATE OPENED, STATUS NAME, SUMMARY, TEAM LEADER, BLOCKING USERS, PRIORITY, IMPLEMENTATION DATE, FIX MANAGER, RAISED IN, SIR AREA,RAISERS AREA, CYCLE, SUB CYCLE, ANALYSED BY, ANALYSED DATE, REVIEWED BY, REVIEWED DATE, CONCLUDED BY, CONCLUDED DATE")
            Do Until rs2.EOF
              txtfile.Write rs2.Fields("issue_id") & ","
              txtfile.Write rs2.Fields("lifecycle_name") & ","
              txtfile.Write rs2.Fields("date_closed") & ","
              txtfile.Write rs2.Fields("date_opened") & ","
              txtfile.Write rs2.Fields("status_name") & ","
              txtfile.Write rs2.Fields("summary") & ","
              txtfile.Write rs2.Fields("team_leader") & ","
              txtfile.Write rs2.Fields("blocking_users") & ","
              txtfile.Write rs2.Fields("priority") & ","
              txtfile.Write rs2.Fields("impl_dt") & ","
              txtfile.Write rs2.Fields("fix_mgr") & ","
              txtfile.Write rs2.Fields("raised_in") & ","
              txtfile.Write rs2.Fields("sir_area") & ","
              txtfile.Write rs2.Fields("raisers_area") & ","
              txtfile.Write rs2.Fields("cycle") & ","
              txtfile.Write rs2.Fields("subcycle") & ","
              txtfile.Write rs2.Fields("analysed_by") & ","
              txtfile.Write rs2.Fields("analysed_dt") & ","
              txtfile.Write rs2.Fields("reviewed_by") & ","
              txtfile.Write rs2.Fields("reviewed_dt") & ","
              txtfile.Write rs2.Fields("concluded_by") & ","
              txtfile.WriteLine rs2.Fields("concluded_dt")
              rs2.MoveNext
            Loop
            txtfile.Close
     
            Set txtfile = Nothing
            Set fso = Nothing
            rs2.Close
            Set rs2 = Nothing

Hope you are not writing data to excel file, but to a csv file. You can do this in another way, but I am not sure, if it is possible, in your case.

Also, do you need specific fields or all fields from the recordset?

Whatever, you can give a try to this:

http://www.smithvoice.com/vbfun.htm
(Look for "Oracle to Excel?  No sweat!")

You might need to change the codings, to suit your needs.
I have used code from this to convert SQL Server to other ISAMs.

Hope it helps.

Hope, you can use the code, to convert to Excel, HTML, Access, CSV, etc.

which menu is this under?  I can't see it...

oh, sorry...

Database Tips --> NEW! Big Iron to ISAM Converter Class

I dont know if emotionally or in a hurry, i typed the bold title on the page. :(

Hi
I've tried using the code you suggested but not sure how to translate this bit:
(the connectionstring needs to point to Oracle as I'm using a view, unless theres another way of course...)


Private Function GetDBConn() As ADODB.Connection
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\NWIND.MDB;Persist Security Info=False"
        .Open
    End With
    Set GetDBConn = cn
    Set cn = Nothing
   
End Function

Hi try like this:
Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=OraServer;Persist Security Info=True

The above one is by using OLE DB provider, and, you can also use ODBC to connect to Oracle from VB.

Note that Data Source is the Oracle Server name, and password and User ID, you can change as you require.

I'm connecting to this db ok but when the code begin to run 'RunJetInserts' proc I get the error 'Operation is not allowed when the object is closed'.  

Is this because one of my connections is to oracle and the other to access?  I could change them all to oracle but not sure if i'm missing the point here as from reading the article the jet engine plays a big part in this.  Confused.

No, RunJetInserts should be Jet only. Because, it inserts records from Oracle to Access.

Do you have the latest MDAC installed in your system, I hope that should have the Jet 4.0 one, if your problem is on this line?

Did you change or remove any of the code there?

Can you able to trace at which line you get the error?

I have set a reference to the Microsoft Data Access Components Installed Version which I assume is what is required re MDAC.

The line I get the error on is this:
m_rsToWorkWith.MoveFirst

So for some reason my records aren't getting passed through I would imagine.  This is all of my code (not including the class which remains the same):

Private Sub Command1_Click()
    Dim rs As ADODB.Recordset
    Dim sSQL As String
    Dim rsConv As CRSConverter
   
    'On Error Resume Next
   
    sSQL = "SELECT * FROM PROJECT"
    Set rs = New ADODB.Recordset
    With rs
        'the demo uses a local jet fiel
        'but the class's real power is in CS data work as with SQLServer or Oracle
        'in those cases we need to amke sure that we are doing a client side disconnected recordset
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Source = sSQL
        .ActiveConnection = GetDBConn1
       
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & " occured getting recordset" & vbCr & _
            "(" & Err.Description & ")"
       
        Exit Sub
    End If
    'if no errors, disconnect it
    'rs.ActiveConnection = Nothing
    End With
   
    'now pass it to the converter
    Set rsConv = New CRSConverter
    With rsConv
        .ConvertRSToFile rs, effExcel97, App.path, "testthing"
        MsgBox "done!"
    End With
   
    rs.Close
    Set rs = Nothing
    Set rsConv = Nothing
End Sub

Public Function GetDBConn1() As ADODB.Connection
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    With cn

        .ConnectionString = "Provider=MSDAORA.1;Password=dillo_pc;User ID=dillo_pc;Data Source=PANEL;Persist Security Info=True"
        .Open
    End With
    Set GetDBConn = cn
    Set cn = Nothing
   
End Function

>> I have set a reference to the Microsoft Data Access Components Installed Version which I assume is what
is required re MDAC.

No need for this. What I mentioned was for:
Microsoft ActiveX Data Objects 2.1 Library (or whatever version you have)

I wished to check if you are able to run Jet 4.0, thats it.

>>The line I get the error on is this:
m_rsToWorkWith.MoveFirst

Replace the line with:

If m_rsToWorkWith.RecordCount <> 0 Then
   m_rsToWorkWith.MoveFirst
End If

'This seems to be a bug, that is, if no records are there in recordset, then it won't work.

And in your case, I think you have commented out one line:

'rs.ActiveConnection = Nothing

should be

rs.ActiveConnection = Nothing

if I am right. Check with the code from the downloaded file.

HTH

I added in statements you suggested but i'm still getting the same error.

This example works with the nwind database on my pc but doesn't appear to like it when I connect to oracle.  

I have tried with the demo app leaving it exactly as it is and just changing the sql query and the dbconn connection but it falls over.

yes it does work, the plot thickens....

I had commented out on error resume next, I put it back in and everything works like a dream!  Not too sure why though but there you go.  Can't give you the points yet as the option isn't there, you may have to reply.

Thanks, Jenny.

What option isn't there?

Do you mean to say, that if you put On Error Resume Next statement, then MoveFirst works, or else, it does not work?

Or,was there any problem with the connection opening?

No I meant there isn't an option to 'accept comment as answer' like there usually is... there still isn't, I've no idea how to give you the points.  

Can you see it when you go in... any clues?  Maybe I should open another question about how to allocate points!

o, i thought u needed me to reply, how it worked :>.

I have posted a comment to the Community Support regdg the problem. You may like to just put a comment there, so that, if some moderator/EE peolep respond to it, you might be able to know of it.

Put a comment like, "ping.." on:
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20187440

Cheers