Excel & Access 2007: Read-Only Error when executing Access Macro from Excel VBA

Hi

You might have to upload the files so we can test it and debug it.

I don't know everything about step 2 but if:
1) You want to run it automatically then you can change refresh values to do it automatically (the link can be set up manually)
2) If you want to run it manually then your code is fine and if you upload the files I can look into it, although I would normally create a recordeset using adodb and use range("A5").CopyFromRecordset myRecordSet but this is only my preference for importing data. I can provide the code.

Regards
Emil

Try changing step 2 to this:

Sub Data_Retrieve()
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\My Documents\Work\test.accdb" _
        , _
        ";Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLE" _
        , _
        "DB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" _
        , _
        "Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=" _
        , _
        "False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:" _
        , "Support Complex Data=False"), Destination:=Range("$A$5")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("tbl_01-01_Output_Billings")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "C:\My Documents\Work\test.accdb"
        .ListObject.DisplayName = "Table_test.accdb"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Thanks for your help.  I don't know how but I was able to get it to work.  It looks like you only changed "Share Deny Write" to "Share Deny None" in your codes.  Either one works with my file.  Does it matter which one I use?

In addition, is there a way to simplify the codes?  I need to repeat it a few times for other retrievals in the same file.  It would be nice to make it more simple so it is easier to update.

Thanks.

Using Share Deny None means the database should not be locked for others to write to, so your Access macro should be able to work.

For the rest, it depends what you mean by simplify?

The db will be accessed by multiple users.  I think it would be better that it is locked during the time the Excel macro is being executed so no update by another user can be made during that time.  If so, which mode should I use?

Also, it seems like there is issue with the db being locked even after a user is done with the Excel macro.  The db is released for edit only after the Excel file is closed.  How do I fix this?

Regarding simplifying the codes, I was thinking about deleting the following.  Do you see an issue with it?

        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True

I don't think you can have both. You either lock it for the duration of the Excel file's session, or you don't lock it at all. YOu may also want to set the Database Locking Mode part to 1 rather than 0.

Thanks.

In this case, I would want to try to have it available to anyone at all time.  However, it is not working using "Share Deny None" and the db is still locked until after exiting the Excel file.  What am I missing?

Did you change the locking mode?

The database locking mode was set to 1 earlier.  I just tried 0 and the db is still locked.

My guess would be that you hung an object creating a locked state. The application may visibly quit (the application closes), but an artifact or remnant of the Application remains (if you go to Task Manager, you will see it is still running). If you are running the automation a few times in a row, you will end up getting quite a few instances of the Application invisible but open (as shown by Task Manager). To avoid these types of problems, I always fully qualify my VBA automation code. For example...

Dim xl As Excel.Application

Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
        If Err = ERR_APP_NOTRUNNING Then
            Set xl = New Excel.Application
        End If

    'Excel.Application.ScreenUpdating = False
    xl.Visible = False
    xl.Application.DisplayAlerts = False
    xl.Application.ScreenUpdating = False

What does the codes do and how do I use it?  I tested it and it closed the Excel application for me.  I would like the file to stay opened.

To clarify, I think you hung the Access application which resides in the Excel instance. This code will pick up the active access instance if it already exist. If you create an new instance of the Access object without fully terminating the first object, the first obj can lock the new object. This theory can be verified by looking in the task manager as mentioned above. (control+shift+escape)

Try this code for runAccessMacro()

Sub runAccessMacro()

      Dim acObj As Object
          'Set acObj = CreateObject("Access.Application")
      On Error Resume Next
      Set acObj = GetObject(, "Access.Application")
              If Err <> 0 Then
                        Set acObj = New Access.Application
              End If
      acObj.OpenCurrentDatabase "C:\My Documents\Work\test.accdb"
      acObj.DoCmd.RunMacro ("Macro1")
      acObj.CloseCurrentDatabase
      Set acObj = Nothing

End Sub

If that doesn't work post your code for your Macro1 so I can look at it.

It doesn't work and the following code is highlighted.

                Set acObj = New Access.Application

Here is the situation.  First, I ran the Access macro using DoCmd.RunMacro.  Then I opened the Access db and try to modify a query.  when I open the query in design mode I received error message "You do not have exclusive access to the database at this time.  If you proceed to make changes, you may not be able to save them later.  The problem went away after I closed the Excel file.