Database Changes not seen by users 1

[fredk]

I have a database that is used for approval letters for our company - Every once in a while, I have to update. The database is copied to the users computer and links to a SQL database to pull in general information to populate some of the fields on a form.

The user searches for the record they want and it uses the sql tables and one of my own tables (housed in the database)

The user clicks a button and it runs the letter (just a report) For some reason, after this update, the users cannot see the changes I made on the letter. Also, on the form I have a label that says "last updated on 1/25/06" The users still see the old version.

What is stranger is that I post this database on a shared drive and the users copy it from the shared drive to their computer. When I open up the version on the shared drive I can see the updates. Other uses open the same database from the same place and do not see the updates.

We do have some users with access 97 and some with 2000 - I thought it was just the access 2000 users, however, I just found an access 97 user that can't see the updates.

One other thing that might be a cause - I have both access 97 and access 2000 on my computer

Any help would be greatly appreciated!!!

Fred

 

[psemianonymous]

It's apparent that they're using an old version of the frontend. The label saying "last updated etc" is clear evidence. The next step is finding out why your update isn't taking and/or if this is a training issue with your users.

 

[fredk]

Actually, that is what you would think - but even when I emailed the updated version to another user, went to that users computer, and updated it myself. Still not there.

It is definately not because they are not updating correctly because of the above.

If you have any more ideas I would appreciate it - It has me stumped!

Fred

 

[fredk]

I was able to fix my problem - I was wrong in saying that one of the users had access 97 - all of the users that were unable to see the changes in the file had access 2000 - I think what happened is that one user actually opened the version on the shared drive - they received the prompt do you want to convert the database to 2000 or use as is. They selected to use as is and that is what caused everyone who copied the file not to be able to see the changes.

I am speculating here but I think it makes sense. Basically, I had to add convert a version of the datbase to access 2000 (therefore, having a seperage version for 97 and 2000) Now everyone who has 2000 is ok.

Thanks for the help!

Fred

 

[Wolfen02]

One thing you might try is distributing a launch program to your users. I set up a small database containing a copydown VBA module run from AutoExec. The user installs this launcher on their computer's C drive and creates a desktop shortcut. Each time they open the shortcut, the latest version of the frontend database is copied onto their C drive and opened. This way I don't have to depend on the end user opening the latest version or having conflicts with multi-users opening the same frontend.

To avoid re-copying the frontend everytime regardless of version, add a "CurrentVersion" table to your frontend and a "LatestVersion" table to your backend. Link both into your launcher database. If the versions do not match (or the frontend is not found on the C drive), copy the latest frontend overwriting the one on the C drive. Just remember to update both tables after any updates are made.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[fredk]

Thanks so much for the info wolfen02 - Do you happen to have that code and directions so I can implement? If you could provide it would really help!

Thanks!!!!

Fred

 

[Wolfen02]

Here's the basic code. I've included optional coding for authorization and version checks.

You'll notice that the code utilizes the filecopy method, checks for file on the drive and then uses the copyfile method as needed. I've had several instances where filecopy malfunctions, so this is my workaround.

Function CopyDown()
Dim sSRC, sDST As String
Dim strUser As String
Dim iRET As Integer
Dim fs As Object
Dim r As String

'\\(Function) get network ID from computer
Call UserNameGrabber(strUser)

On Error GoTo NO_ACCESS
    
'\\(Process01)Define source location - Use actual drive info
sSRC = "\\DRIVE\FOLDER\SOURCE_DATABASE.mdb"
    
'\\(Process02)Define destination location - Use same database name as original
sDST = "C:\SOURCE_DATABASE.mdb"

'\\NOTE:  AUTHORIZATION PROCESS IS OPTIONAL
'\\(Process03) Open authorization form - Linked table from backend with login ID
DoCmd.OpenForm "AuthCk", , , , , acHidden
Forms![AuthCk].[Text0].Value = strUser '\\Compares to login ID used
Forms![AuthCk].Requery

'\\(Check01) valid authorization? - Add your contact information where indicated
If IsNull(Forms![AuthCk].[Text2].Value) Then
   '\\(Process03a) error msg
   MsgBox "The User is not on the Authorized List for this database." & _
       Chr$(10) & Chr$(10) & "If you feel you have reached this message in error, " & _
       "please contact:" & Chr$(10) & _
       Chr$(10) & "YOUR CONTACT INFO"
   DoCmd.Close acForm, "AuthCk", acSaveNo
   GoTo ExitIt
End If
DoCmd.Close acForm, "AuthCk", acSaveNo
'\\END OF OPTIONAL AUTHORIZATION PROCESS

'\\(Process03b) set scripting object
Set fs = CreateObject("scripting.filesystemobject")

'\\NOTE: VERSION CK OPTIONAL
'\\(Check02) Latest Version? - Open version on C drive if no update needed
   '\\Uses form "Ver" to check if the C drive version number matches the link to the backend database
If fs.FileExists(sDST) = True Then
    DoCmd.OpenForm "Ver", , , , , acHidden
    If Forms![Ver].[Text8].Value = "N" Then    '\\N = no need to copy
        DoCmd.Close acForm, "Ver", acSaveNo
        Set fs = Nothing
        '\\(Process04)Open database from harddrive - Access 2003 shown - adjust version as needed
        Set appAccess = CreateObject("Access.Application.11")
        appAccess.OpenCurrentDatabase sDST
        appAccess.UserControl = True
        GoTo ExitIt
    End If
End If
'\\END OF OPTIONAL VERSION CK

DoCmd.OpenForm "Main"  '\\Msg shown on screen stating that latest version being downloaded
DoCmd.Maximize

    '\\(Process05)filecopy source to destination
    FileCopy sSRC, sDST
    '\\(Check03)file copied?
        If r = "N" Then

            '\\(Process05a) Use copyfile method
            fs.copyfile sSRC, sDST, True
                DelayTime 4
        End If
            '\\(Check04) file copied?
            If fs.FileExists(sDST) = False Then
                '\\(Process05b) error msg
                MsgBox "File Copy Failed"
                GoTo ExitIt
            End If
    '\\(Process06) clear file scripting obj
    Set fs = Nothing
    
    '\\(Process07)Open database from harddrive
    Set appAccess = CreateObject("Access.Application.11")
    appAccess.OpenCurrentDatabase sDST
    appAccess.UserControl = True

    '\\(Process08)Close launcher
    DoCmd.Close acForm, "Main", acSaveNo
    Quit

NO_ACCESS:
r = "N"
Resume Next
        
ExitIt:
Quit
        
End Function

Public Function DelayTime(PauseTime As Double)

  Dim start
  start = Timer
  '\\Loop thru time specified
  Do While Timer < start + PauseTime
     DoEvents    '\\Yield to other processes.
  Loop
        
End Function

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UserNameGrabber(strUser As String)
    Dim strBuffer As String
    Dim intLength As Integer
    
    strBuffer = Space$(20)
    
    intLength = GetUserName(strBuffer, Len(strBuffer))
    strUser = strBuffer
     
End Function

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[fredk]

Thanks so much for taking the time to help me! I look forward to using the code - thanks again!!!!

Fred