Run-time error '3125'

pmjg23
pmjg23 used Ask the Experts™
on
I am using the following code to export data from an MS Access db to Excel:

DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_ABS", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_PriorYear_ABS", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_Eqty", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_PriorYear_Eqty", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_LTDbt", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_PriorYear_LTDbt", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_STDbt", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_PriorYear_STDbt", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
    Call Shell("""C:\Program Files\Microsoft Office\Office10\EXCEL.EXE"" ""J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", 1)

When I run the code I receive the following error "Run-time error '3125'. It is odd to me since I am using the same exact code in various other MS Access db's that I have created without any issues. I have the db set to compact and repair upon closing, and have created a new mdb file and imported all objects and still receive the same error. Any assistance or thoughts would be very helpful. I am finishing up this project on my current assignment and need to finish by this Friday.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
have you set a breakpoint to see where the code fails? which line does it occur?

check that all the queries exists and try removing the last argument as it relates to an xl range which is usually used for import not export.
ie
change
DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_ABS", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True, ""
to
DoCmd.TransferSpreadsheet acExport, 8, "qryRpt_Cntry_CurrentYear_ABS", "J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\rptCountry_Securities1.xls", True

Author

Commented:
Hi frankytee,

It occurs for all lines of code, and have already checked naming conventions for queries. Will try what you suggested for fix and will let you know results. Thanks

Commented:
J:\REG REP\JPMREPORT\Treasury Surveys\Database\Excel\

Is the J: drive mapped to this computer?

Do you have write permissions to "REG REP\JPMREPORT\Treasury Surveys\Database\Excel\" ?

Is the read-only attribute on for the spreadsheet file?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi frankytee,
What you suggested did not work. Do you have any other suggestions relating specifically to the error? "Run-time error '3125': "is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

I have tested by changing the name of the queries to "qrytest" and I still receive the error?

Author

Commented:
I found the solution, I needed to minimize some of the field names in the main table, since when exporting data into excel if within the query the joins go over a certain total it will produce that error.
PAQed with points refunded (500)

Computer101
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial