VBA Commands randomly unavailable in Access (1 Viewer)

[cjh7111]

Hey all,
Ive come across a problem while working on VBA in excel that pulls data in from an Access Database. It was working yesterday but for some reason im getting errors today. The VBA pastes data into the database and then runs some queries and then copies the query and pastes it back into excel. The section of the code that is as follows:

Sub DBPull()

Dim acapp As Object
Set acapp = CreateObject("Access.Application")
acapp.UserControl = True
acapp.OpenCurrentDatabase ("C:\Documents\Database\New_Database.accdb")
With acapp
    .Echo False
    .DoCmd.SetWarnings False
    .DoCmd.RunSQL "DELETE * FROM [Paste Table]"
    .DoCmd.OpenTable "Paste Table"
    .DoCmd.GoToRecord , , acLast
    .DoCmd.RunCommand acCmdPasteAppend
    .DoCmd.Close acTable, "Paste Table"
    .DoCmd.OpenQuery "Brand Query", acViewNormal, acReadOnly
    .DoCmd.Requery
    .DoCmd.OpenQuery "Output", acViewNormal, acReadOnly
    .DoCmd.Requery
    .DoCmd.RunCommand acCmdSelectAllRecords
    .DoCmd.RunCommand acCmdCopy
End With

Worksheets("Engine").Select
Range("A5").Select
ActiveCell.PasteSpecial xlPasteValues

With acapp
    .DoCmd.OpenQuery "Brand EVP", acViewNormal, acReadOnly
    .DoCmd.Requery
    .DoCmd.RunCommand acCmdSelectAllRecords
    .DoCmd.RunCommand acCmdCopy
    .DoCmd.CloseDatabase
    .Echo True
    .Application.Quit
    
            
  End With

The strange part is that 'SelectAllRecords' works in the first section and pastes it corrently into excel but then it fails when it tries to select the 'Brand EVP' query. There error messages are as follows:

"The command or action 'SelectAllRecords' isn't available now.
-You May be in a read-only database or an unconverted database from an earlier version of Microsoft Access.
-The type of object the action applies to isn't currently selected or isn't in the active view.
Use only those commands and macro actions that are currently available for this database."

"Run-time error '2046: The command or action 'SelectAllRecords' isn't available now.


I tried removing 'acreadonly' but then it just fails at the Copy command. Same error messages just with 'Copy'

Any assistance would be greatly appreciated because I'm lost.

Thanks

 

[JHB]

Only an idea, maybe because you moved the focus away from the database.
Try by comment out the below 3 lines:

[COLOR=Red][B]'[/B][/COLOR]Worksheets("Engine").Select 
[B][COLOR=Red]'[/COLOR][/B]Range("A5").Select 
[B][COLOR=Red]'[/COLOR][/B]ActiveCell.PasteSpecial xlPasteValues

Only for error finding, try by running the below part first:

With acapp     
   .DoCmd.OpenQuery "Brand EVP", acViewNormal, acReadOnly       
   .DoCmd.Requery     
   .DoCmd.RunCommand acCmdSelectAllRecords     
   .DoCmd.RunCommand acCmdCopy     
   .DoCmd.CloseDatabase     
   .Echo True     
   .Application.Quit                     
End With

 

[GinaWhipp]

In addition to what JHB said, I would use Automation to *send* the records to Excel instead of trying to copy them there. That way Access shouldn't lose the focus.