Access Opens with Ready-Only Access when opened via VBA

Shanan212
Shanan212 used Ask the Experts™
on
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TEMP", "C:\Users\userA\Desktop\" & iName, True

Hi,

I have the above code in a VBA module inside Access.

I also have read/write access to the database (and its folder) that was opened prior to this code via the following code

    Dim Ac As Object
    
    Set Ac = CreateObject("Access.Application")

    Ac.Visible = False
    
    Ac.OpenCurrentDatabase ("\\Fileserver\Everyone\Engineering Database\Database\NM Probill Database\NM Probill Database.accdb")

Open in new window


My problem is, 1/10 times, the database is opened as 'Read-Only' while no-one else is accessing it. This is creating problems for the appending query above.

How do I resolve this? How do I make it force open as 'read-write'?

Thanks for your time!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
try opening the db in exclusive mode, and set the .usercontrol to true

    Dim Ac As Object, strDb as string
   
strDb="\\Fileserver\Everyone\Engineering Database\Database\NM Probill Database\NM Probill Database.accdb"

    Set Ac = CreateObject("Access.Application")

    Ac.Visible = False
   
    Ac.OpenCurrentDatabase (strDb, True)

    Ac.usercontrol=true

Commented:
This could be caused by phantom hidden access sessions that can be created if you don't release your ac object variable, or if you don't ac.quit when you are done.

Author

Commented:
That worked! Thanks all!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial