Opening access from excel opens as read only

[mrcw]

I'm running an access macro through excel and it works great the first time I do it then the second time I try to run it it always opens as read only. If I run it once then try to go to access and open the database manually it also opens as read only. After failing to fix the problem for awhile I added a function to check if the file was already open. When I try to run it a second time I get the "File already open" error.


Sub accessMacro()
Dim appAccess As Access.Application
Set appAccess = New Access.Application
Dim ObjAc As Access.Application

If IsFileOpen("Z:\Contracts\EXP\GX EXP BACKEND.accdb") Then
MsgBox ("File already open")

Else
With appAccess
.OpenCurrentDatabase "Z:\Contracts\EXP\GX EXP BACKEND.accdb"
.visible = True
.DoCmd.RunMacro "UPDATE GX EXP_SP"
.CloseCurrentDatabase
.Quit
End With

End If

End Sub

Any ideas on how to fix this are greatly appreciated.

[snb]

Use


Sub M_snb()
with getobject("Z:\Contracts\EXP\GX EXP BACKEND.accdb")


end with
End Sub

[mrcw]

Thanks for the quick response, unfortunately I still get a "Cannot update. Database or object is read only."

[snb]

Close all access-instances first.

[mrcw]

Tried the code below and still get a message saying the application is read only.

With GetObject("Z:\Contracts\EXP\GX EXP BACKEND.accdb")
.visible = True
.DoCmd.RunMacro "UPDATE GX EXP_SP"
.CloseCurrentDatabase
End With
Access.Application.Quit

[mrcw]

Figured out that if I simply run the sub above it works great, after that sub I have a sub to refresh the data connection, once the data connection sub runs the database will only open as a read only database.



Sub refreshconnectivity()
Sheets("Updating Query").Unprotect Password:="unlock"

With ActiveWorkbook.Connections("GX EXP BACKEND")
.OLEDBConnection.BackgroundQuery = False
.Refresh
End With

Sheets("Updating Query").Protect Password:="unlock"
End Sub


Any tips on how to fix this would be greatly appreciated.

[mrcw]

Incase anyone else is having this problem I added .OLEDBConnection.MaintainConnection = False and it works like a charm! Thanks for the input snb



Sub refreshconnectivity()
Sheets("Updating Query").Unprotect Password:="unlock"

With ActiveWorkbook.Connections("GX EXP BACKEND")
.OLEDBConnection.BackgroundQuery = False
.Refresh
.OLEDBConnection.MaintainConnection = False
End With

Sheets("Updating Query").Protect Password:="unlock"
End Sub