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
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.