[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

How to prevent getting a Runtime Error '-2147467259 Not Enough Disk Space when executing rs.Open "SELECT tblCostCenter.[COSTCENTER] FROM tblCostCenter GROUP BY tblCostCenter.[COSTCENTER];", c ?

Public Function prtFaLedgerExcel()
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim fname As String
    Dim sql As String
    Dim fpath As String
   
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
   
   
    fpath = "C:\FALEDGER\REPORTS\OUTPUTS\"
     
    MsgBox ("The process of conversion FA Ledger Report to excel format is started. Please wait ... ")
   
'1. Open cost center table and pass cost center as a parameter

    rs.Open "SELECT tblCostCenter.[COSTCENTER] FROM tblCostCenter GROUP BY tblCostCenter.[COSTCENTER];", cn
    Do Until rs.EOF

    fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
   
'2. insert records to tblFaLedgerBranch only for selected cost center

sql = "INSERT INTO tblFaLedgerBranch ( COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, LOCATION_ID, DESCRIPTION, ADDRESS2, CITY, STATE, ZIP,REPORT_RUN_DATE,COMMENTS )" & _
"SELECT  COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, " & _
           "LOCATION_ID , Description, ADDRESS2, CITY, State, ZIP, REPORT_RUN_DATE, '' " & _
           "FROM dbo_FA_LEDGER_LOCATION " & _
           "WHERE (dbo_FA_LEDGER_LOCATION.COSTCENTER = '" & rs(0) & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL sql

'3. Convert query to excel format

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Fa_ledger_report", fname, True

'4. Drop temp table

 sql = "DELETE tblFaLedgerBranch FROM tblFaLedgerBranch"

    DoCmd.RunSQL sql
 sql = ""

    rs.MoveNext
    Loop
    rs.Close

    Set rs = Nothing
    Set cn = Nothing
0
zimmer9
Asked:
zimmer9
1 Solution
 
peter57rCommented:
Hi zimmer9,
How big is your mdb file?

Pete
0
 
zimmer9Author Commented:
The mdb is 177 MB (186,339,328 bytes)
0
 
peter57rCommented:
zimmer9,
... and I assume that you DO have plenty of disc space, yes?

Pete
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
zimmer9Author Commented:
19GB USED SPACE
18GB FREE SPACE
0
 
zimmer9Author Commented:
I closed down the application and reran it.

This time the compiler is hightlighting in yellow the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Fa_ledger_report", fname, True

and I get the same error as follows

Runtime Error '-2147467259   Not Enough Disk Space
0
 
nico5038Commented:
Hmm, are you sure that the result is less than 65,000 rows as excel does have a max.
Further more make sure that your temporary files are indeed pointing to the disk with the free 18Gb as I've experienced this too running an application on the D: drive an having less than 150Mb free on the C: drive with the temp files.

Nic;o)
0
 
speak2abCommented:
Verify that you are not hitting the MSaccess Limits.

Make a copy of your DB and replace the one you are using presently or create a new DB and import your tables into the new DB

Ab
0
 
peter57rCommented:
You could try this - it looks like it might be relevant :
http://support.microsoft.com/?id=286153

Pete
0
 
peter57rCommented:
Would you post the solution you found please, so that others might benefit.

Pete
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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