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:
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
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:
Code:
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