• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1828
  • Last Modified:

Not enough storage error

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
jhazard
Asked:
jhazard
  • 11
  • 9
1 Solution
 
Valliappan ANSenior Tech ConsultantCommented:
How do you export to Excel?
0
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Valliappan ANSenior Tech ConsultantCommented:
Hope, you can use the code, to convert to Excel, HTML, Access, CSV, etc.
0
 
jhazardAuthor Commented:
which menu is this under?  I can't see it...
0
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
Note that Data Source is the Oracle Server name, and password and User ID, you can change as you require.
0
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
>> 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
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
jhazardAuthor Commented:
yes it does work, the plot thickens....
0
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
jhazardAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now