Solved

Not enough storage error

Posted on 2001-09-17
20
1,771 Views
Last Modified: 2013-11-15
I am exporting some data to an excel file.  My code works perfectly for the first couple of exports and then I get this error:

-2147024882(8007000e)
Not enough storage is available to complete this operation.

If I reboot my pc it begins to work but only for a couple of times until it errors again.

Excel isn't being opened at any time and I am closing my recordset and setting it to nothing after each export.

Any clues anyone?
0
Comment
Question by:jhazard
  • 11
  • 9
20 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6487234
How do you export to Excel?
0
 

Author Comment

by:jhazard
ID: 6487303
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6487345
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.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6487347
Hope, you can use the code, to convert to Excel, HTML, Access, CSV, etc.
0
 

Author Comment

by:jhazard
ID: 6487361
which menu is this under?  I can't see it...
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6488079
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. :(


0
 

Author Comment

by:jhazard
ID: 6500145
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

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6504584
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.


0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6504586
Note that Data Source is the Oracle Server name, and password and User ID, you can change as you require.
0
 

Author Comment

by:jhazard
ID: 6505044
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6505061
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?
0
 

Author Comment

by:jhazard
ID: 6505077
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6505095
>> 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
0
 

Author Comment

by:jhazard
ID: 6505144
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.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 50 total points
ID: 6505230
ok, just try rs.MoveFirst after this line:

rs.ActiveConnection = Nothing

Does it work? If not, there is some problems with the recordset opening for Oracle - may be type or cursor or lock. Change the part of recordset locktype and/or locktype in that case.

0
 

Author Comment

by:jhazard
ID: 6505430
yes it does work, the plot thickens....
0
 

Author Comment

by:jhazard
ID: 6505645
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.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6507887
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?
0
 

Author Comment

by:jhazard
ID: 6508222
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!
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6508394
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:
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20187440

Cheers
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
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 tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

708 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

18 Experts available now in Live!

Get 1:1 Help Now