Thread: Opening an Access Table using Excel VBA

Morning All

I am currently trying to develop a piece of code which will connect to an MS Access database and then kick off a Query on the table

I am using the following code:

Public strDB As String
Public A As Object
Public Function runaccessmacro()
Application.ScreenUpdating = False
Set A = CreateObject("Access.Application")
strDB = "S:\MI_Portal\TMNT\TMNT (TESTING).mdb"
A.OpenCurrentDatabase strDB
A.Visible = False
A.DoCmd.RunMacro "mcrUpdateNumberStatus"
A.Quit
Set A = Nothing
Application.ScreenUpdating = True
End Function

Sub run_access_macro()
Call runaccessmacro
End Sub

But this does not work and am presented with error message '7866', which suggests the table can not be found or is locked by another user

I have also tried to following code, but get the same error message:

Global oApp As Object

Sub OpenAccess()

    Dim LPath As String
    Dim LCategoryID As Long

    'Path to Access database
    LPath = "\\RLBYCCMPFS06\swap\MI_Portal\TMNT\TMNT (TESTING).mdb"
    
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application.11")
    oApp.Visible = True

    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath, False

    'Open form called Categories filtering by CategoryID
    oApp.Visible = False
    oApp.DoCmd.RunMacro "mcrUpdateNumberStatus"
    oApp.Quit

End Sub

Any suggestions??

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here