Results 1 to 8 of 8
  1. #1
    jamesdeuce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Location
    Houston, TX
    Posts
    22

    Why does Access open Read Only after closing then reopening a file shortly thereafter?


    I have some VBA code in Excel that opens an Access Database to perform some function. After the Access Database closes via the VBA code, and the VBA code in Excel continues...the code then opens that SAME Access Database again to do more work in it...AND sometimes the Access Database opens "Read Only" and my code stops...because the code won't continue if Access is "Read Only" nor do I want it to continue "Read Only". It's almost as if you open, close, and re-open an Access Database without leaving enough time between closing it and opening it this causes it to be "Read Only". If I close the Access Database and let it "rest" then it's no longer "Read Only" but my code isn't designed to let the Access Database "rest". Is there a way to prevent this from happening programmatically? It's very very annoying. And I don't want to build in some 5 minute Delay Function to allow it to "rest"...that's pointless. Why does Access do that? It's really dumb. Thank you very much for any thoughts or ideas.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not see how your code would be able to maintain your DB in Exclusive or Edit mode if it closes the DB. Perhaps another file that is open, like an Excel file, is linking to your DB.

  3. #3
    rhodesengr is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2007
    Posts
    7
    I get that read only thing too if i close and re-open quickly. Now and then I have tried to figure it out. Yesterday I found a post about little 1k-size files that sometimes get left in the directory with your access file. I think it those little 1k-size files that make Access open your db in read-only. I do not know how to make it stop doing that though. I guess they usually get deleted after some period of time, so then the file opens normally.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It could be that the lacddb file does not have a chance to update and is giving old info. You could pause your code by calling 3 or 4 seconds with a function like this

    Code:
    Function BeginStop(intTime As Integer)
    
     Dim dblCount As Double
    
     dblCount = DateAdd("s", intTime, Now)
    
     While DateDiff("s", Now, dblCount) > 0
     Wend
    
     MsgBox "Time is up"
    
     End Function
    Another option may be to not have the DB in Exclusive mode when closing. A third option may be to look for the lacddb file's existence using FileSystemObject. Since the issue is "Exclusive" the existence of the lacddb would indicate you cannot open the DB yet.

  5. #5
    jamesdeuce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Location
    Houston, TX
    Posts
    22
    My database is 130 MB. I don't know what Exclusive means. I open the Access database from Excel with code that looks like this...

    Set appAccess = CreateObject("Access.Application")

    With appAccess
    .OpenCurrentDatabase strDBPath
    .Visible = False
    .DoCmd.SetWarnings False
    .DoCmd.OpenModule strDBModule, strDBMacro
    .Run strDBMacro, arrForecastData
    .CloseCurrentDatabase
    .Quit
    End With

    Set appAccess = Nothing

    Sorry I forget how to tag the code. After this code, Excel continues then re-opens the Database again calling Access again with similar code structure.

  6. #6
    jamesdeuce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Location
    Houston, TX
    Posts
    22
    I have built in 15 second delays, 30 second delays and sometimes it works and sometimes it doesn't. The Access Database itself has tables that it links to but none of them are open. Nothing is open except for the Excel file that's calling this specific Access Database. This isn't some company. It's just me at home on my personal computer so it's not like other users are locking it. If I knew how to upgrade these Access Databases to SQL Databases and code there instead it might eliminate my problems but I don't know how to do that nor do I want to do that right now. I just want my code to work in Access for the time being so that I can get some data results and maybe some day I will looking into upgrading later so to speak to SQL.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by jamesdeuce View Post
    ... I open the Access database from Excel with code that...
    See post #2

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Going the other way, using Access to automate Excel, I have had Excel hang. I added a line "DoEvents" and it seems to have helped.

    What was happening is that the code would close the Excel instance, but Excel was not actually quitting before the Access procedure ended, leaving the Excel process still running. I used Taskmanager to look at the processes to see if the Excel instance had closed. After adding the DoEvents line, I didn't have any more problems closing Excel.

    Have taskmanager visible when you code is executing to see if the Access process ends or not.

    Try adding the line in blue:
    Code:
         <snip>
           .Quit
            DoEvents
        
        End With
        
        Set appAccess = Nothing
    My $0.02....FWIW

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-03-2014, 07:36 AM
  2. Access Form Requery (F5 on keyboard or reopening) handler?
    By superfury in forum Code Repository
    Replies: 0
    Last Post: 08-23-2013, 03:17 AM
  3. Replies: 3
    Last Post: 05-23-2012, 03:05 AM
  4. Open Access Database in Read Only Mode
    By praloy in forum Programming
    Replies: 2
    Last Post: 03-28-2012, 10:50 PM
  5. Replies: 3
    Last Post: 06-02-2011, 07:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums