Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    24

    Answered: Code Check Request

    Hello, I was hoping I could get a second, or third, or fourth, or fifth (you get the picture) , opinion on my code. I copied this code from a database built by the guy before, it was built in an .mdb database (access 2003 I believe). I am using it in an .accdb ( 2013) database. The code is to look at a version number, if they differ, copy a new database from server and replace, if they are the same open my form. The problem I am having is that sometimes I execute the code and it works perfectly, no issues, other times, when I open the newly copied database I get a "database is in an inconsistent state" error. The purpose of the code is that I am constantly being asked to make changes to the design and features of the database, and the database needs to be house on the users desktop so they can access it offline. I would like the users to be able to open the database check if I made changes and update. I need this to be done as automatically as possible because the majority of my end-users are very computer "challenged" to say the least. Can anyone look through my code and tell me if they see anything that might be causing this occasional error and how to stop it? Thanks in advance, Tim

    Code:
    Public Function UpdateDBFE()
    
    On Error GoTo err_handler
    
        Me.TimerInterval = 0
        DoCmd.Hourglass True
        DoCmd.SetWarnings False
        Dim fname, newfname, path, remotefile, username As String
        Dim strSQL As String
        Dim fs As Object
        Dim lu As Date
        Dim WAIT As Double
        Set fs = CreateObject("Scripting.FileSystemObject")
        fname = CurrentDb.Name
        path = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
        newfname = path & "Temp.mdb"
    
        Me.txtStatus.Caption = "Installing...": Me.Repaint
        
        remotefile = "\\claimsfield.nwie.net\Shared\MWCZ\NILC-FPMPer\NILC-FPM PER.accdb"
        fs.CopyFile remotefile, newfname 'make online db = Temp
        
        'Copy data currently in NILC  to new local db.
    '    strSQL = "INSERT INTO [NILC] IN '" & newfname & "'" & _
    '    "SELECT NILC.* FROM NILC"
    '    DoCmd.RunSQL strSQL
    
    
        fs.CopyFile newfname, fname 'make temp copy replace local copy
        fs.DeleteFile newfname 'delete Temp
        Set fs = Nothing
    
        Me.txtStatus.Caption = "Completed Successfuly.  Closing...": Me.Repaint
    
    
        WAIT = Timer + 3
        While Timer < WAIT
            DoEvents  'do nothing (for 3 seconds)
        Wend
        DoCmd.Quit
    err_handler:
        MsgBox "Update was unsuccessful due to the following error:" & Chr(10) _
        & Err.Description & Chr(10) & "Please contact " & AdmInfo(0) & " at " & AdmInfo(1) & "."
        Set fs = Nothing
        DoCmd.Quit
    
    
    End Function
    Last edited by tharless; 02-25-16 at 10:33.

  2. Best Answer
    Posted by healdem

    "there is a different way of doing this in the code bank. code provided by PKstormy IIRCD it runs a VBS script that copies the most recent version of the app for a server then fires up your copy of Access to opent he app.

    doing it your way means there is always going to be a risk that copying over a file that is already open may happen. AS you don't (fully) control what Access does when it opens an app file there is alwasy a potential for changes to be being made in the existing app before it gets copied."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You are presuming its this code that is causing the problem...
    Is it possible someone has the db open when they attempt to copy the file, either the soyrce or the destination?
    Is it possible a third party is either changing the design and or writing data at the same time .
    Has the db been compacted before releasing to users.

    This sort if problem requires a detailed investigation. Are there common elements where it works or fails. Is it an os issue, an Access version issue a 32/64 bit issue. Is thete a common time when problems occur
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Apr 2011
    Posts
    24
    Hello Healdem,

    Thanks for the response, I apologize, I should have clarified in the original post, current state, I make changes then send a new copy to be placed on the users desktop. This code is something I am trying to implement in the next update, I am just in the testing phase so no one but me is doing anything with it, I have the server copy separate from the in use copy for my testing purposes. I have just been going in and executing the code to see if anything goes wrong, and its seems random, I will execute, download a copy just fine, go back in 5 minutes later execute and get the inconsistent state message. Microsoft has recovered each time and then I go into the database again and execute, downloads no problem, I execute 5 - 10 more times, no problem, then all of a sudden I get the issue. that is why I am thinking that it is in this code.

    Again I have only been testing on my pc so the times it works and does not have all been on my local system, and I have not compacted it yet..

    Thanks for the help so far

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this code running INSIDE the application you are trying to deploy?
    ..if so I suspect the solution is to use the code in the code bank to do this.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Apr 2011
    Posts
    24
    yes, the plan is to have the database open on a blank form that runs a load event, that checks the version number against the version number on a text document located on the server, if they differ then it opens my update form which then runs this code on the on timer event, if the same it opens my user form. I am not married to this setup by no means, it is just how it was set up in the database I copied the code from, and given my novice skill set seemed to be the most simple and straight forward. Forgive my ignorance but by "code bank" do you mean to put my code in a separate application to perform the actions or do you mean to use a different code in my application that is stored on the db forums code bank? and if the later could you point me in the right direction of this?

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is a different way of doing this in the code bank. code provided by PKstormy IIRCD it runs a VBS script that copies the most recent version of the app for a server then fires up your copy of Access to opent he app.

    doing it your way means there is always going to be a risk that copying over a file that is already open may happen. AS you don't (fully) control what Access does when it opens an app file there is alwasy a potential for changes to be being made in the existing app before it gets copied.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Apr 2011
    Posts
    24
    Sorry for the delayed response, I was pulled away for other projects, unfortunately that solution would not work for me as my front ends are set up to be stand alone database with a set of queries that copy the data and send it to the back end upon a button push, this is so the user can work offline ( they are mostly field workers and are not always connected to the internet). But the code in the bank did provide me some good ideas that I was not thinking about previously so I marked it best answer. In the end what worked for me, so far, was just slowing down the code in a few areas allowing it to process before moving to next section of code, I ran it a few dozen times with success afterwards so keeping my fingers crossed. Thanks again healdem for all the assistance.

    Also in case any one is having a similar situation and needs to slow there code down from time to time I used this code to accomplish it,

    Code:
    Function Pause(x)
    'pause execution for x seconds (x may also be fractions of a second e.g. 0.1 = 1/10 second)
        WAIT = Timer + x
        While Timer < WAIT
            DoEvents  'do nothing (for x seconds)
        Wend
    End Function
    then I would call it anywhere I needed to slow down the code for a few seconds
    There where a lot of post on this on the internet, but this one was simple and works quiet well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •