Member Avatar for tgifgemini

I am trying to display/open MS Access report and I get: RunTime error 2486 - You can't not carry out this action at the present time on this line of code:

DoCmd.OpenReport stDocName, acViewNormal

Below is my module:

Set rsUnclaimedRptqry = dbUnclaimed.OpenRecordset("SELECT DISTINCT tblUnclaimed.L1L2, tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, tblUnclaimed.Status " _
& "FROM tblUnclaimed LEFT JOIN EmployeeInfo ON (tblUnclaimed.PassNumber = EmployeeInfo.Pass_Number) AND (tblUnclaimed.L1L2 = EmployeeInfo.L1L2) " _
& "WHERE (((tblUnclaimed.Status) In ('A','E','U')) AND ((Len([EmployeeInfo].[Status]))=1)) " _
& "ORDER BY tblUnclaimed.L1L2, tblUnclaimed.PassNumber;")

'Populate Report Table:
'----------------------
Set rsUnclaimedtbl = dbUnclaimed.OpenRecordset("tblUnclaimedChk_Msg", dbOpenDynaset)


rsUnclaimedRptqry.MoveFirst

Do Until rsUnclaimedRptqry.EOF = True
    rsUnclaimedtbl.AddNew
    rsUnclaimedtbl![L1L2] = rsUnclaimedRptqry![L1L2]
    rsUnclaimedtbl![PassNumber] = rsUnclaimedRptqry![PassNumber]
    rsUnclaimedtbl![EmployeeName] = rsUnclaimedRptqry![EmployeeName]
    rsUnclaimedtbl![Status] = rsUnclaimedRptqry![Status]
    rsUnclaimedtbl.Update
    rsUnclaimedRptqry.MoveNext
Loop
    

Ans = MsgBox("Would you like to View this report?", vbYesNo, "FTP Transmission")

If Ans = vbYes Then GoTo ViewReport

ViewReport:
'------------
Dim stDocName As String

stDocName = "RptUnclaimedChk_Msgs"

'Note: "RptUnclaimedChk_Msgs" is the name of the report in my database 

DoCmd.OpenReport stDocName, acViewNormal

Any insight will be appreciated.
tgifgemini

  • 2 Contributors
  • 5 Replies
  • 562 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by tgifgemini

Dani AI

Generated

This thread shows a common automation pitfall with Access: Run-time error 2486 (“You can’t carry out this action at the present time”) usually means the requested DoCmd action needs an interactive Access instance (a UI/context) that the current process does not have. As discovered, merely ticking the Access reference in VB6 does not create that instance at runtime; an Access.Application must be created and the target database opened before calling UI-bound methods like OpenReport.

Key technical points: method names and library references provide compile‑time types but do not instantiate an application. acViewNormal sends the report straight to the printer (no preview), so printer-related problems or a hidden/non-initialized UI can also trigger failures. Actions that require windows or printer dialogs are fragile when called from an external process or a service.

Practical checklist and best practices (short, implementable):

  • Instantiate an Access.Application (early or late binding) and call OpenCurrentDatabase with the full path; set Visible = True while debugging.
  • For unattended output prefer DoCmd.OutputTo (export to PDF) or acViewPreview instead of printing with acViewNormal.
  • Avoid row-by-row recordset copying for bulk data; clear the target table first if duplicates are a problem, then use a single INSERT ... SELECT or Execute for performance and atomicity.
  • Wrap large updates in a transaction where possible, release COM objects with Quit/Set Nothing, and handle errors (use dbFailOnError on Execute calls).

Credit to for the efficient INSERT approach; the combination of creating a real Access.Application instance (as did) and replacing loops with set-based SQL gives the most reliable and performant result. Also note: automating Office/Access from server-side services is generally discouraged—desktop automation works best from desktop apps or scheduled tasks.

Recommended Answers

All 5 Replies

Member Avatar for QVeen72

Hi,

I guess, it is a memory Issue, and why you open such a complicated sql query, and again add all the records, to the same table....?
Your table must have become huge... Every time this report is viewed, Total records in that table(tblUnclaimed ) Doubles..
Is this what you want...?

Regards
Veena

Member Avatar for tgifgemini

Hi QVeena.
Is it a really a memory issue? No, I don't want the recordds to double each time I run the sql and populate the table.

Are you suggesting I delete the records in the table before adding new data to the table?

Any help will be appreciated.
Thanks.
tgifgemini

Member Avatar for tgifgemini

Problem resolution:
Even though I have MS Access 9.0 Object library in my References, I have to physically create and instatiate a new Access object inside my vb code.

Dim MsAxs As Access.Application
Set MsAxs =  New Access.Application
MsAxs.OpenCurrentDatabase("Mydb")

stDocName = "MyReportName"

MsAxs.DoCmd.OpenReport stDocName,  acViewNormal

And that solved the problem.

I was under the impression that having the Access reference checked with the rest of my references will do the trick but I guess not.

Thanks for you help.
tgifgemini

Member Avatar for QVeen72

Hi,

I Got confused with the code there...
One more suggestion, Instead of
Opening >> Loop >> Add recordsets, Just run one SQL Statement:

Dim sSQL As String
ssql="Insert Into tblUnclaimedChk_Msg " _
& " (L1L2,PassNumber,EmployeeName,Status) " _
& " SELECT DISTINCT tblUnclaimed.L1L2, tblUnclaimed.PassNumber, " _
& " tblUnclaimed.EmployeeName, tblUnclaimed.Status " _
& "FROM tblUnclaimed LEFT JOIN EmployeeInfo ON " _
& " (tblUnclaimed.PassNumber = EmployeeInfo.Pass_Number)" _
& " AND (tblUnclaimed.L1L2 = EmployeeInfo.L1L2) " _
& "WHERE (((tblUnclaimed.Status) In ('A','E','U')) AND "  _
& " ((Len([EmployeeInfo].[Status]))=1)) " 
dbUnclaimed.Execute sSQL

Regards
Veena

Member Avatar for tgifgemini

Thanks Veena. That worked too.
Have a wonderful weekend.
tgifgemini

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.