Results 1 to 9 of 9
  1. #1
    Access12345 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5

    User shows up twice in Record Locking Information


    We run our automated reporting environment on Excel and Access. We built a scheduler in Excel that launches update macro's in respective Access db's.

    This has worked fine for the past 3 years. We recently migrated to Windows 10 + Access 2016. The scheduler still works, but something strange happens for one of the Access db's.

    When it opens the db it opens it once (there is only one version of Access open), but in the record locking information file the computer that opens the db is shown twice. As such, it locks everything up because it thinks there are two people using the db.
    This only happens for this db, not for the other 8 or so. And they all follow the same code / logic. I can't figure out why this specific DB is causing issues. When I manually open the db (not through our Excel scheduler), it works just fine.

    It is a trusted DB, it does not have any code running OnOpen, it is not set to read-only. Compacting it does not work. Restoring a back up does not work.

    What the hell is going on?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Is it a split DB?
    Does each person have their own copy of the FE?
    On the machine with issues, does the lock file get deleted when the Access app is closed?
    If not, use Task Manager to close Access, delete the lock file and try again.

    If still an issue, decompile the FE file or replace it
    As a last resort, repair or reinstall Access.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Access12345 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Is it a split DB?
    No

    Does each person have their own copy of the FE?
    Nope, no front-end. Purely for storage.

    On the machine with issues, does the lock file get deleted when the Access app is closed?
    Jup.

    If not, use Task Manager to close Access, delete the lock file and try again.

    -

    If still an issue, decompile the FE file or replace it
    -

    As a last resort, repair or reinstall Access.
    It works just fine for a multitude of other Access db's. Tested it on other machines as well, same issue.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    So its one standalone PC that duplicates the username no matter which PC its run on & no matter who is running it?

    In that case there must be some form of corruption or other code issues.
    Newer versions of Access are (rightly) less forgiving of code errors
    You've already compacted with no change

    So check the following in turn - apologies if you already know about all of the following

    1. First do a backup
    2. Do you have "require Variable Declaration" ticked in VBE Options. If not, tick it
    As you may know that will add the line 'Option explicit' to all new code modules BUT not to existing ones
    So go through and add that as the second line (usually after Option Compare Database) of each code module if it is missing
    Now click Debug...Compile & fix each declaration error that is picked up.
    Continue until it is fully compiled (the Compile menu item will be disabled)
    3. Now try opening again. Is the problem fixed? If not go to step 4
    4. Next try decompiling as I suggested http://www.fmsinc.com/microsoftacces.../decompile.asp.
    It only takes a couple of seconds and will fix any compilation errors.
    Compile again. Compact
    5. Now try opening again. Is the problem fixed? If not go to step 6
    6. Create a new blank database. Import all items from the old database into that.
    Check there are no missing references. Compact
    7. Now try opening again. Is the problem fixed? If not reinstall Access (or just give up!!!!!)

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    re #7 - no way! Post a db copy so we can
    a) see if it breaks in a different environment (i.e. not on your network)
    b) check code to see if anything obvious comes to light. I'd consider anything that might now be causing one opening attempt to open the db hidden.
    It is a trusted DB
    Are you going from memory, or did you ensure that the upgrade maintained your trust center settings?
    What older versions of programs/windows did you upgrade from?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    #7
    Just for clarity, I meant only that as your database still works, perhaps just put up with the behaviour if you had tried everything else first!
    However I accept my chastisement with good grace!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Access12345 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by ridders52 View Post
    So its one standalone PC that duplicates the username no matter which PC its run on & no matter who is running it?

    In that case there must be some form of corruption or other code issues.
    Newer versions of Access are (rightly) less forgiving of code errors
    You've already compacted with no change

    So check the following in turn - apologies if you already know about all of the following

    1. First do a backup
    2. Do you have "require Variable Declaration" ticked in VBE Options. If not, tick it
    As you may know that will add the line 'Option explicit' to all new code modules BUT not to existing ones
    So go through and add that as the second line (usually after Option Compare Database) of each code module if it is missing
    Now click Debug...Compile & fix each declaration error that is picked up.
    Continue until it is fully compiled (the Compile menu item will be disabled)
    3. Now try opening again. Is the problem fixed? If not go to step 4
    4. Next try decompiling as I suggested http://www.fmsinc.com/microsoftacces.../decompile.asp.
    It only takes a couple of seconds and will fix any compilation errors.
    Compile again. Compact
    5. Now try opening again. Is the problem fixed? If not go to step 6
    6. Create a new blank database. Import all items from the old database into that.
    Check there are no missing references. Compact
    7. Now try opening again. Is the problem fixed? If not reinstall Access (or just give up!!!!!)

    HTH
    Tried it all. Thanks for the tips. The decompiling was new to me. But alas, it did not work. Neither did creating a blank database...

    I guess all that is left is posting the code. But like I said, the code works for 8 other databases that follow the same logic.

    Here is the VBA in our Excel launcher, at certain times of the day it starts a macro event in respective DB's:

    Code:
     'Initialise the database. strCellPath is the database location.
    InitDB_PPR strCellPath
                        
    'Print the starttime
    ws.Cells(c.Row, ws.Range("colScriptStart").Column) = Now
                        
    'Clear error cell
    ws.Cells(c.Row, ws.Range("colScriptError").Column) = ""
                        
    'Launch the instruction. Opens the database and starts the macro called M_AutomaticRefresh. 
    strConnect = "MsAccess " & Chr$(34) & ws.Range(strCellPath) & Chr$(34) & " /x M_AutomaticRefresh"
    AppId = Shell(strConnect, vbMaximizedFocus)
                        
    db.Close
                        
    'print end time in column 'Ended'
    ws.Cells(c.Row, ws.Range("colScriptEnd").Column) = Now
    Then in the database itself I have this code:

    Code:
    Public Function refresh() As String
            DoCmd.SetWarnings False
            
            Dim FName As String
            Dim FNames() As String
            Dim FType As String
            Dim i As Integer
            Dim GetDBPath As String
            Dim GetCurrentFile As String
            Dim NewDBPath As String
            Dim NewDBfile As String
            Dim naam As String
            Dim naam2 As String
            
            
            ' Declare the dir and file settings we are using
            FType = "*.txt"
            GetDBPath = "W:\PPEDC\From Mainframe\LPCVTXTbestanden\"
            FType = GetDBPath & FType
            FName = Dir(FType)
            
            ' Run the loop as long as there are files in the dir
            Do Until FName = ""
            i = i + 1
            ReDim Preserve FNames(1 To i)
            
            ' Add the current file to the array
            FNames(i) = FName
            FName = Dir
            Loop
            If i <> 0 Then
            
            ' Keep getting data from the array as long as there is some
            For i = 1 To UBound(FNames)
            
            ' Make sure that the path is set from the file
            ' we are importing
            
            GetCurrentFile = GetDBPath & FNames(i)
            naam = GetCurrentFile
            naam = Replace(naam, ".", "")
            naam = Left(naam, Len(naam) - 3) & ".txt"
            
            Name GetCurrentFile As naam
            
            GetCurrentFile = Replace(GetCurrentFile, ".", "")
            GetCurrentFile = Left(GetCurrentFile, Len(GetCurrentFile) - 3) & ".txt" ' Now we import the current select file
            
            
            DoCmd.TransferText acImportFixed, "LPCV", "LPCV", GetCurrentFile, False
            
            naam2 = FNames(i)
            naam2 = Replace(naam2, ".", "")
            naam2 = Left(naam2, Len(naam2) - 3) & ".txt"
            
            NewDBPath = "W:\PPEDC\From Mainframe\LPCVTXTbestanden\Geimporteerd\"
            NewDBfile = NewDBPath & naam2
            FileCopy GetCurrentFile, NewDBfile
            Kill (GetCurrentFile)
            
            
            ' And we move to the next file
            Next i
            End If
            ' Let user know we are finished
            DoCmd.SetWarnings True
    End Function

  8. #8
    Access12345 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5
    I found a fix!

    I noticed that my other DB's are of the .mdb format. The initDB function tries this code on the database:

    Code:
    sDbName = wsActive.Range(strCellPath)
        Set db = OpenDatabase(sDbName)
    I'm no VBA guru but this DAO function apparently doesn't work for .accdb databases? It initializes the databases but keeps it in memory or something?

    I change the code to follow a different path for .accdb formats, a path without a db initializer and that seemed to do the trick.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What function, InitDB? I don't see where you posted it. I just noticed what I'd consider to be a no-no - using Refresh as a procedure name. Probably not the issue, but wouldn't it be odd if the name was causing something to happen twice or more, which is your problem, yes?
    See this list of reserved names:
    http://allenbrowne.com/AppIssueBadWord.html
    You can usually avoid this problem without having to look at that list over and over again by avoiding single word names. Besides, refresh isn't all that intuitive to those following in your footsteps. If it's a series of files you're refreshing/replacing, then "RefreshFiles" is not only more informative, it should keep you out of reserved name troubles. This goes for anything in the db: txtDate (control); Dte or MtgDate, StartDate, StartDte etc etc for table Date fields - never Date, or Name and so on...

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

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2018, 09:02 PM
  2. Replies: 3
    Last Post: 02-28-2017, 03:18 PM
  3. Replies: 1
    Last Post: 04-11-2015, 10:31 AM
  4. Record Locking Multi User Environment
    By praetorianprefect in forum Database Design
    Replies: 5
    Last Post: 04-16-2012, 02:31 PM
  5. Replies: 6
    Last Post: 05-04-2011, 06:17 PM

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