?
Solved

Do you know how I can prevent the Run time error '3010'  table 'FA_ledger_report' already exists ? Actually the 'FA_ledger_report' is a Union query.

Posted on 2006-04-23
5
Medium Priority
?
1,046 Views
Last Modified: 2012-08-14
Do you know how I can prevent the Run time error '3010'  table 'FA_ledger_report' already exists

FA_ledger_report is actually the following Union query:

SELECT  COSTCENTER AS [COST CENTER],
SAR_CATEGORY AS  [SAR],
ASSET_NUMBER AS [ASSET NUMBER],
ASSET_DESC AS [ASSET DESCRIPTION],
VENDOR_NAME AS [VENDOR NAME],
INVOICE_NUMBER AS [INVOICE NUMBER],
Format(BOOK_COST, "#,##0.00;(#,##0.00)") AS [BOOK COST],
Format(BOOK_ACCUM_DEPR, "#,##0.00;(#,##0.00)") AS [BOOK ACCUM DEPR],
Format(CURR_DEPR, "#,##0.00;(#,##0.00)") AS [CURR DEPR],
Format(YTD_DEPR, "#,##0.00;(#,##0.00)")AS [YTD DEPR],
Format(NET_BOOK_VALUE, "#,##0.00;(#,##0.00)") AS [NET BOOK VALUE],
Format(BEGIN_DEPR, "mm/yyyy") AS [BEGIN DEPR],
Format(DEPR_LIFE, "yy/mm") AS [DEPR LIFE],
LOCATION_ID, DESCRIPTION, ADDRESS2, CITY,STATE, ZIP, REPORT_RUN_DATE,
"" AS [Remarks/Comments]
from tblFaLedgerBranch
UNION SELECT  "", "", "", "","","",
"___________________",
"___________________",
"___________________",
"___________________",
"___________________",
"", "", "", "", "", "", "", "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION SELECT  "", "", "", "","","",
Format(sum(BOOK_COST), "#,##0.00;(#,##0.00)"),
Format(sum(BOOK_ACCUM_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(CURR_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(YTD_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(NET_BOOK_VALUE), "#,##0.00;(#,##0.00)"),
"", "", "", "", "", "", "",  "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION select "","",
"", INSTRUCTIONS,"", "", "", "","","", "", "", "", "", "",  "", "", "", "","",""
from tblInstuctions
GROUP BY INSTRUCTIONS
ORDER BY 1 DESC , 2;

--------------------------------------------------------------------------------------------------------------

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  <--- the compiler stops here on this hightlighted line in yellow


If isFileExist(fname) Then StartDocLN fname
'4. Drop temp table

 sql = "DELETE tblFaLedgerBranch FROM tblFaLedgerBranch"

 DoCmd.RunSQL sql
 sql = ""
 
    rs.MoveNext
    Loop
    rs.Close

    Set rs = Nothing
    Set cn = Nothing

MsgBox ("The process of conversion FA Ledger Report to excel format is completed.")
'If isFileExist(fname) Then StartDocLN fname
DoCmd.SetWarnings True
End Function

Private Sub StartDocLN(filename)
 Dim xlApp As Excel.Application
 Dim xlWB As Excel.Workbook
 Dim xlWS As Excel.Worksheet
 
 'open excel template
 Set xlApp = New Excel.Application
 'xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Open(filename)
 Set xlWS = xlWB.Worksheets(1)
 xlWS.Columns.AutoFit
 xlWS.Range("G2:K16635").NumberFormat = "#,##0.00"
 xlWS.Range("G2:K16635").Value = xlWS.Range("G2:K16635").Value
 
 xlApp.ScreenUpdating = True

End Sub
0
Comment
Question by:zimmer9
  • 3
  • 2
5 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16520044
Try with:
    kill  fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
    fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
 to make sure the .xls doesn't exist before execution or perhaps better use:
    fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyy/mm/dd hh:nn:ss") & ".xls"

Nic;o)
0
 

Author Comment

by:zimmer9
ID: 16520522
The first approach:

Try with:
    kill  fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
    fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"

This results in Run time error '70':
Permission denied

--------------------------------------------------------------------------------------------
The second approach:

to make sure the .xls doesn't exist before execution or perhaps better use:
    fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyy/mm/dd hh:nn:ss") & ".xls"

This results in Runtime error '3044':

C:\FALEDGER\REPORTS\OUTPUTS\fa_ledger_report_1001002006\04\:15:57:38.xls is not a valid path. Make sure that the pathname is spelled correctly and that your are connected to the server on which the file resides.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16520559
Hmm, sorry try:
 fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyymmdd_hhnnss") & ".xls"

Nic;o)
0
 

Author Comment

by:zimmer9
ID: 16520799
How 'bout if the compiler now stops at the following line ?

Set xlApp = New Excel.Application

with a Run time error '429':
ActiveX component can't create object

I already went into Tools -->  References and checked off the following boxes:

Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 11.0 Object Library

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16520818
Perhaps the answer of this Q holds the solution:
http://www.experts-exchange.com/Databases/MS_Access/Q_20223106.html

Nic;o)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

850 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