Open and Close an Access Database from Excel using VBA - Access Macro Error

Question

Running a Excel VBA loop to perform following: (1) Open Access Database (2) Run macros in Access database to import data (3) Close Access database completely

Excel VBA loop works fine on first iteration but on 2nd iteration stops at first Access macro. If disable the following 2 Excel VBA steps, loop runs as desired (except database not closed at end of each loop): (1) oAccess.DoCmd.Close (2) oAccess.DoCmd.Quit

Any idea how to completely close database (no shell open) at end of each loop iteration and get macros to run on following iterations? Thanks.

Dim DataBaseLoc As String
Dim oAccess As Access.Application
For K = First To Last 
     Set oAccess = New Access.Application
     oAccess.Visible = True
     If Z = "" Then DataBaseLoc = "C:\AutoInsight.mdb"
     If Z <> "" Then DataBaseLoc = "C:\AutoInsight_X.mdb"
     oAccess.OpenCurrentDatabase DataBaseLoc
     If Z = "" Then Set ObjAccess = GetObject("AutoInsight.mdb")
     If Z <> "" Then Set ObjAccess = GetObject("AutoInsight_X.mdb")
     With ObjAccess
          DoCmd.RunMacro "mcr_Import_Parts"
     End With
     With ObjAccess
          For i = 1 To 2
               If i = 1 Then DoCmd.RunMacro "mcr_Import_C"
               If i = 2 Then DoCmd.RunMacro "mcr_Import_I"
          Next i
     End With
     oAccess.DoCmd.Close
     oAccess.DoCmd.Quit
Next K

barrowc · Accepted Answer · 2015-10-09 22:08:46Z

0

Try this instead:

    End With

    Set ObjAccess = Nothing
    oAccess.DoCmd.Close
    oAccess.DoCmd.Quit
    Set oAccess = Nothing
Next K

VBA should eventually notice that the objects which ObjAcess and oAccess referred to are no longer valid and dispose of them but it can be helpful to make things explicit by setting the variables to "Nothing". This is especially the case when the variables don't go out of scope (which is the case here)

answered Oct 9, 2015 at 22:08

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill B Over a year ago

Added the "Nothing" steps, still receive run-time error 2486, "you can't carry out this action at the present time"

Collectives™ on Stack Overflow

Open and Close an Access Database from Excel using VBA - Access Macro Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related