Modify BE and re-link to FE

Topic

New Reply

Greetings,

I’m tasked to support an access2016 db that was created by someone else. The db is split:
the BE is housed on department shared drive and users have copies of the FEthat is linked to the BE.
Couple of questions:

1. There is request to add a new field with a drop down. Once I added the new field in the BE, what steps do I need to follow to make this field available in the FE so end-users start utilizing this field.

2. There is a status report that managers run to see if a given case is open/closed etc. when they click on open cases button, it will prompt them to enter caseworker[s name, when they enter the requested question nothing happens.

However, when I tried the same report I could view the report without any problem.
Can this be a permission issue or something else?

Regards,

Reply | Quote

Replies

1) The front end will automatically see any new fields added to a table that is already linked. If you add a new table to the back end you will have to create a new linked table in the front end before it can be used.

Queries do NOT automatically see new fields unless they are using SELECT [tablename].* to get all fields. This is not a Best Practice and should be avoided. Normally you should have to update queries to add the new field. For new table you will also have to add then to any query that needs it.

Forms and reports will have to be manually updated to add any new fields.

2) Are you testing with the same copy of the front end as the users that are have the issue?

Reply | Quote

Thanks HiTechCoach for the reply.

1. Correct, once I added the new field, my plan is to create a new query/form and report to reflect the new field.
2. Yes, everyone has the same copy. I ran the report just fine. I also watch another manager run the same report without any issues. However, there is a new manager on board that was unable to run it. That is why I’m guessing this is
a ‘permission’ issue. I’ve to investigate how the person before me set up the security.

By the way, where do I set e-mail notification so I’m notified whenever someone reply to my questions?

TIA,

Regards,

Reply | Quote

Thanks HiTechCoach for the reply.

1. Correct, once I added the new field, my plan is to create a new query/form and report to reflect the new field.
2. Yes, everyone has the same copy. I ran the report just fine. I also watch another manager run the same report without any issues. However, there is a new manager on board that was unable to run it. That is why I’m guessing this is
a ‘permission’ issue. I’ve to investigate how the person before me set up the security.

By the way, where do I set e-mail notification so I’m notified whenever someone reply to my questions?

TIA,

Regards,

The report not running issue does not sound like permissions to me. If it were permissions then I would expect they could never get to the report or the prompt to enter criteria.

At the top of the page below “What’s New?” you will see a link Forum Actions. In the dropdown select Edit Profile. Email notifications are in general settings.

Reply | Quote

Under “Thread Tools” at the top of the thread you will see a “Subscribe” option.

cheers, Paul

Reply | Quote

Thank you for the reply,

I accessed general settings >Messaging & Notification and checked Receive Email options.

In terms of permissions, I’ve couple of managers log into my PC using their own ms access credentials and run the same report that they were unable to run from their PC.
I was surprise to see they could run the report without any problem.

Can this be a network permission issue somehow attached to computer name?

How/where do I check this?

TIA,

Reply | Quote

To test the theory about computer name you could log onto one of the PC where they were unable to run the report. See itr it works for you.

Normally with a Domain (Active Directory) I set file access permissions by user at the network level. You could check Active Directory for computer permissions.

It is possible with VBA code to check the computer name and not execute stuff.

For the uses that are unable to run the report, do other parts of the Access application run OK?

Reply | Quote

OCM,

Here’s some code I use to keep front-ends up to date with the Back-End.

Code:

Option Compare Database
Option Explicit

'+---------------------------------------------------------------------------+
'| Version: 10.8                       Programmed by: The Computer Mentor    |
'| Dated  : 03/01/2016                           aka: RetiredGeek            |
'+---------------------------------------------------------------------------+
'Required References: {Only when using Early Binding - order counts!}
'                     Visual Basic For Applications
'                     Microsoft Access xx.x Object Library
'                     OLE Automation
'                     Microsoft DAO x.x Object Library
'                     Microsoft Word xx.x Object Library
'                     Microsoft Outlook xx.x Object Library
'                     Microsoft Visual Basic for Applications Extensibility x.x
'                     Microsoft ActiveX Object x.x Library

'Set Compiler Constant for Early/Late Binding conditional code
' 0 = Early Binding
' 1 = Late Binding

'Set in Tools->ARB Properties... Global Scope if set here Module Scope
'#Const LateBinding = 1

Public zDBPath        As String
Public zStatusMsg     As String
Public lTimerInterval As Long
Public Const zCodeVersionNo = "10.8 Dev"

'                            +--------------------+               +----------+
'----------------------------|  RelinkExtTables() |---------------| 01/23/15 |
'                            +--------------------+               +----------+
'Called by: Macro - AutoExec
'Calls    : [Utilities] zGetDBPath()
'Globals  : lTimerInterval
'           zStatusMsg
'Notes    : 03/04/10 - Added timed relink message vs msgbox w/user action
'           06/04/10 - Added shared access for Office Computers P-P Lan
'           06/10/10 - Added shared access for Computer Mentor P-P Lan
'           04/10/11 - Added shared access for Go-Flex NAS
'           07/01/11 - Fixed Email bills for NAS & Changed Fee to Assessement
'                      on billings.

Function RelinkExtTables()

   Dim zDBFullName    As String
   Dim zBEDBFN        As String
   Dim zTableName(7)  As String
   Dim zUserName      As String
   Dim iTblCnt        As Integer
   Dim oExcelTbl      As TableDef
   Dim oDB            As Database
   
   GoTo StartLinking

FileDoesNotExist:

   If Err.Number = 7874 Then
     Resume Next
   Else
     DoCmd.Hourglass False
     Application.Echo True
     MsgBox "Error No: " & Err.Number & vbCrLf & _
            "Description: " & Err.Description
   End If

StartLinking:

   Application.Echo False  'Screem Updating OFF
   DoCmd.Hourglass True    'Show Hour Glass

   zTableName(0) = "Docks"
   zTableName(1) = "Lots"
   zTableName(2) = "Owners"
   zTableName(3) = "PhoneDir"
   zTableName(4) = "StorageLots"
   zTableName(5) = "tblAuxNumbers"
   zTableName(6) = "tblFees"
   
   zDBPath = zGetDBPath()
   zUserName = Environ("USERNAME")

   If zDBPath = "Error" Then
     MsgBox zUserName & ": is not an authorized user!", _
                vbOKOnly + vbCritical, "Error: User Not Authorized"
     ExitDB
   End If
   
   zBEDBFN = "WPOA_be.mdb"
   zDBFullName = zDBPath & zBEDBFN
  
   For iTblCnt = 0 To UBound(zTableName) - 1
   
      On Error GoTo FileDoesNotExist
      '*** Delete TableDef from FRONT end DB
      DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=zTableName(iTblCnt)
   
      '*** Copy TableDef from BACK end DB to FRONT end DB - Keep in sync!
      DoCmd.TransferDatabase TransferType:=acLink, _
                             DatabaseType:="Microsoft Access", _
                             DatabaseName:=zDBFullName, _
                               ObjectType:=acTable, _
                                   Source:=zTableName(iTblCnt), _
                              Destination:=zTableName(iTblCnt)
      On Error GoTo 0
      
   Next iTblCnt
   
   '*** Relink WybooWebData Excel File ***
   
'   Set oDB = CurrentDb
'   Set oExcelTbl = oDB.TableDefs("WybooWebData")
'
'   With oExcelTbl
'        .Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & zDBPath & "WybooWebData.xls"
'        .RefreshLink
'   End With  'oTblDef
                             
   '*** End Relink WybooWebData Excel File ***
   
   zStatusMsg = "Tables have been Re-Linked"
   lTimerInterval = 3000    '*** 3 Seconds ***
   DoCmd.OpenForm "frmStatusMsg", acNormal
   Application.SetOption "Themed Form Controls", False
   StdMenuToggle
   DoCmd.Hourglass False    'Turn OFF Hour Glass
   Application.Echo True    'Screen Updating ON
    
End Function    'RelinkExtTables()

Option Compare Database
Option Explicit

Private Declare Function apiEnableMenuItem Lib "user32" Alias "EnableMenuItem" _
  (ByVal hMenu As Long, ByVal wIDEnableMenuItem As Long, ByVal wEnable As Long) As Long

Private Declare Function apiGetSystemMenu Lib "user32" Alias "GetSystemMenu" _
  (ByVal hWnd As Long, ByVal flag As Long) As Long
  
Public strDfltPrt As String   '*** Save Default Printer Name for resetting ***

'                         +-------------------------+             +----------+
'-------------------------|        ExitDB()         |-------------| 04/12/10 |
'                         +-------------------------+             +----------+
'Called by: HideStdMenu
'Calls    : StdMenuToggle

Function ExitDB()

   StdMenuToggle "True" '*** Turn Access Menu back on ***

   Application.Quit
   
End Function              'ExitDB

'                         +-------------------------+             +----------+
'-------------------------|     StdMenuToggle()     |-------------| 01/03/15 |
'                         +-------------------------+             +----------+
'Called by: Shift+F9
'           [RelinkExtTabless]RelinkExtTabless
'           ExitDB
'Notes:     bSwitch - True = Enabled; False = Disabled (not visible)
'           If vSwitch is missing state will toggle,
'           if present state will be set to the desired value.

Function StdMenuToggle(Optional vSwitch As Variant)

   Dim iNextRibbonState As Integer
  
   If IsMissing(vSwitch) Then vSwitch = False
   
#If conAccessVersionID < 2007 Then
     With Application
         .CommandBars("Menu Bar").Enabled = _
            IIf(IsMissing(vSwitch), _
               (Not .CommandBars("Menu Bar").Enabled), vSwitch)
     End With
#Else

     iNextRibbonState = IIf(vSwitch, acToolbarYes, acToolbarNo)
     DoCmd.ShowToolbar "Ribbon", iNextRibbonState  '** Toggle Ribbon Show/Hide
      
     If vSwitch Then                                    'Show Navigation Pane
       DoCmd.SelectObject acTable, , True
       DoCmd.ShowToolbar "Ribbon", acToolbarYes
     Else                                               'Hide Navigation Pane
       DoCmd.NavigateTo "acNavigationCategoryObjectType"
       DoCmd.RunCommand acCmdWindowHide
    End If   'vSwitch

#End If  'conAccessVersionID

End Function              'StdMenuToggle()

'                         +-------------------------+             +----------+
'-------------------------|      ListCmdBars()      |-------------| 04/06/10 |
'                         +-------------------------+             +----------+
'
'Notes:  For development purposes only!

Sub ListCmdBars()

 Dim oCmdBar As Object
 
 For Each oCmdBar In Application.CommandBars
    Debug.Print oCmdBar.Name
 Next
 
End Sub                  'ListCmdBars

'                          +---------------------+                 +----------+
'--------------------------|  SwitchPrinters()   |-----------------| 07/30/10 |
'                          +---------------------+                 +----------+
'Called by     : Report_Open()  - From any form!
'                Report_Close() - From any form!
'Calls         : N/A
'Function Calls: N/A
'Globals Used  : N/A

Sub SwitchPrinters(zSwitchToPtr As String)

  Dim prtName As Printer
  Dim iPrtNo  As Integer
  
  iPrtNo = 0
  
  For Each prtName In Application.Printers
     If prtName.DeviceName = zSwitchToPtr Then
       Exit For
     Else
       iPrtNo = iPrtNo + 1
     End If
  Next prtName

'*** Uncomment next 2 lines for testing or visual verification of switch ***
'  MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
'         " " & Application.Printers(iPrtNo).DeviceName

  Application.Printer = Application.Printers(iPrtNo)

End Sub    '*** SwitchPrinters ***

'                   +----------------------------+                 +----------+
'-------------------|      WPOAStatistics()      |-----------------| 08/05/13 |
'                   +----------------------------+                 +----------+
'Called by     : frmWOPAMainMenu
'                frmOwnerInput

Public Function WPOAStatistics() As String

  WPOAStatistics = _
    Format(DCount("[Resident]", "Owners", "[Resident] =  True"), "00#") & "  F/T Resident Owners" & vbCrLf & _
    Format(DCount("[Occupancy]", "Lots", "[Occupancy] = 'O' or [Occupancy]= 'R'"), "00#") & "  Lots With Houses" & vbCrLf & _
    Format(DCount("[Occupancy]", "Lots", "[Occupancy]= 'R'"), "00#") & "  Rented Houses" & vbCrLf & _
    Format(DCount("[Lot]", "Lots", "[Lot]  ''"), "00#") & "  Total Lots" & vbCrLf & _
    Format(DCount("[StorageLotNo]", "StorageLots", "[OwnerID]=600"), "00#") & "  Empty Storage Lots" & vbCrLf & _
    Format(DCount("[StorageLotNo]", "StorageLots", "[LeaseExpiration]= Application.Printers.Count Then
     '*** User Pressed Cancel button or entered an invalid printer no!***
     MsgBox "You either pressed Cancel," & vbCrLf & _
            "Pressed OK w/o entering a value," & vbCrLf & _
            "or entered a number not on the list." & vbCrLf & vbCrLf & _
            "No Billings will be produced and you will be" & vbCrLf & _
            "returned to the Billing Options Menu.", _
            vbInformation + vbOKOnly, _
            "Information: Process Termination!"
    UserSelectPrinterByNumber = -1   '*** Flag to Cancel Process ***
  Else
    iPrtNo = Val(zIBoxAns)
  
'    MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
'           " " & Application.Printers(iPrtNo).DeviceName
         
    Application.Printer = Application.Printers(iPrtNo)
    UserSelectPrinterByNumber = iPrtNo
  End If
    
End Function              '*** UserSelectPrinterByNumber ***

'                         +-------------------------+             +----------+
'-------------------------|       SystemInfo()      |-------------| 04/12/10 |
'                         +-------------------------+             +----------+

Function SystemInfo()

   Dim zInfo As String
   
   zInfo = "User Name:" & vbTab & Environ("USERNAME") & vbCrLf
   zInfo = zInfo & "Home Drive: " & vbTab & Environ("HOMEDRIVE") & vbCrLf
   zInfo = zInfo & "Home Path: " & vbTab & Environ("HOMEPATH")
   
   MsgBox zInfo, vbOKOnly + vbInformation, _
          "Current System Information"

End Function             'SystemInfo()

'                         +-------------------------+             +----------+
'-------------------------|    AccessVersionID()    |-------------| 10/27/12 |
'                         +-------------------------+             +----------+
'*** Code to determing the Version of Access which is running

Public Function AccessVersionID() As String

  Select Case SysCmd(acSysCmdAccessVer)
        Case 7: AccessVersionID = "95"
        Case 8: AccessVersionID = "97"
        Case 9: AccessVersionID = "2000"
        Case 10: AccessVersionID = "2002"
        Case 11: AccessVersionID = "2003"
        Case 12: AccessVersionID = "2007"
        Case 14: AccessVersionID = "2010"
        Case 15: AccessVersionID = "2013"
        Case Else: AccessVersionID = "Unknown"
  End Select

End Function            'AccessVersionID()

'                         +-------------------------+             +----------+
'-------------------------|   ShowEnvironStrings()  |-------------| 04/12/10 |
'                         +-------------------------+             +----------+
'Notes: List all possible Environ strings for SystemID() function above.
'       Environ() will also read User created Environment variables.
'       You MUST exit Access and restart it after creating the variable.
'Credits: Helen Fedema - Access Watch #12.07

Public Sub ShowEnvironStrings()

   Dim strEnvString As String
   Dim lngIndex As Long
   Dim lngPathLen As Long
   
   lngIndex = 1
   
   Do
      strEnvString = Environ(lngIndex)
      Debug.Print "Env String " & lngIndex & ": " & strEnvString
      lngIndex = lngIndex + 1
   Loop Until strEnvString = ""

End Sub

'                         +--------------------------+            +----------+
'-------------------------| AccessAndJetErrorsTable()|------------| 04/06/10 |
'                         +--------------------------+            +----------+
'
'Notes:  For development purposes only!

Function AccessAndJetErrorsTable() As Boolean
    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim rst As Recordset, lngCode As Long
    Dim strAccessErr As String
    Const conAppObjectError = "Application-defined or object-defined error "

    On Error GoTo Error_AccessAndJetErrorsTable
    ' Create Errors table with ErrorNumber and ErrorDescription fields.
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
    Set fld = tdf.CreateField("ErrorCode", dbLong) '>>>***STOPS HERE*****
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("ErrorString", dbMemo)
    tdf.Fields.Append fld

    dbs.TableDefs.Append tdf
    ' Open recordset on Errors table.
    Set rst = dbs.OpenRecordset("AccessAndJetErrors")
    ' Loop through error codes.
    For lngCode = 0 To 3500
        On Error Resume Next
        ' Raise each error.
        strAccessErr = AccessError(lngCode)
        DoCmd.Hourglass True
        ' Skip error numbers without associated strings.
        If strAccessErr  "" Then

' Skip codes that generate application or object-defined errors.
            If strAccessErr  conAppObjectError Then
                ' Add each error code and string to Errors table.
                rst.AddNew
                rst!ErrorCode = lngCode
                ' Append string to memo field.
                rst!ErrorString.AppendChunk strAccessErr
                rst.Update
            End If
        End If
    Next lngCode
    ' Close recordset.
    rst.Close
    DoCmd.Hourglass False
    RefreshDatabaseWindow
    MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
    Exit Function

Error_AccessAndJetErrorsTable:
    MsgBox Err & ": " & Err.Description
    AccessAndJetErrorsTable = False
    Resume Exit_AccessAndJetErrorsTable
    
End Function                  'AccessAndJetErrorsTable()

'                         +--------------------------+            +----------+
'-------------------------|       SetCloseBox()      |------------| 05/23/10 |
'                         +--------------------------+            +----------+
'
' Purpose: Disable or enable the control box of a form, report, or the Access parent window.

' Accepts: bEnable, which determines whether to disable or enable the control box
'          lhWndTarget (optional), if you want to use a window other than the Access parent window.
' Returns: 0 if disabled, 3 if enabled

' Example usage: lRetVal = SetCloseBox(True) to enable -OR-
'                lRetVal = SetCloseBox(False) to disable the close box of the Access window
' NOTE: If no hWnd is passed in for a specific form or report,
' the code assumes you want to enable/disable the close box of the Access parent window

Public Function SetCloseBox(bEnable As Boolean, Optional ByVal lhWndTarget As Long) As Long

  Const MF_DISABLED = &H2&
  Const MF_ENABLED = &H0&
  Const MF_GRAYED = &H1&
  Const SC_CLOSE = &HF060&

  Dim lhWndMenu As Long
  Dim lReturnVal As Long
  Dim lAction As Long

  On Error GoTo ErrorHandler

  lhWndMenu = apiGetSystemMenu(IIf(lhWndTarget = 0, Application.hWndAccessApp, lhWndTarget), False)

  If lhWndMenu  0 Then
    If bEnable Then
      lAction = MF_ENABLED
    Else
      lAction = MF_DISABLED Or MF_GRAYED
    End If

    lReturnVal = apiEnableMenuItem(lhWndMenu, SC_CLOSE, lAction)
  End If

  SetCloseBox = lReturnVal
  Exit Function

ErrorHandler:
  If Err Then
    'Trap your error(s) here, if any!
  End If
  
End Function            'SetCloseBox()

'                          +---------------------+                 +----------+
'--------------------------|   zOrdinalDate()    |-----------------| 08/08/13 |
'                          +---------------------+                 +----------+
'Called by: [RelinkExtTabless] RelinkExtTables()
'Returns  : STRING = Ex: 8th day of August, 2013

Public Function zOrdinalDate(dte_toformat As Date) As String

   Dim zDaySuffix  As String
   Select Case Day(dte_toformat)
       Case 1, 21, 31
          zDaySuffix = Day(dte_toformat) & "st "
       Case 2, 22
          zDaySuffix = Day(dte_toformat) & "nd "
       Case 3, 23
          zDaySuffix = Day(dte_toformat) & "rd "
       Case Else
          zDaySuffix = Day(dte_toformat) & "th "
   End Select

  zOrdinalDate = zDaySuffix & "day of " & Format(dte_toformat, "mmmm") & ", " & _
                 Format(dte_toformat, "yyyy")

End Function               'zOrdinalDate

'                          +---------------------+                 +----------+
'--------------------------|    zGetDBPath()     |-----------------| 07/16/14 |
'                          +---------------------+                 +----------+
'Called by: [RelinkExtTabless] RelinkExtTables()
'Returns  : STRING = Path to backend DB based on user name and machine name.

Public Function zGetDBPath() As String

   Dim zUName         As String
   Dim zCompName      As String
   Dim zNWPaths       As String
   Dim zUNCs(1 To 2)  As String
   Dim zPath(1 To 2)  As String
   Dim iCntr          As Integer
   Dim iUNCSet        As Integer
   
   zUNCs(1) = "\GOFLEX_HOME"
   zUNCs(2) = "\MyBookLive"
   zPath(1) = "GoFlex Home PersonalWPOA Files"
   zPath(2) = "CMSharedWPOA Files"
   iUNCSet = 0
   
   zUName = Environ("USERNAME")
   zCompName = Environ("COMPUTERNAME")
   zNWPaths = CreateObject("WScript.Shell").Exec("Net View").StdOUt.ReadAll
   
'*** Office No Longer Using Network Drive ***
'   For iCntr = 1 To UBound(zUNCs)
'      If InStr(zNWPaths, zUNCs(iCntr)) > 0 Then
'        iUNCSet = iCntr
'        Exit For
'      End If
'   Next iCntr
   
   If iUNCSet > 0 Then
   
     On Error Resume Next
     zGetDBPath = Dir$(zUNCs(iUNCSet) & zPath(iUNCSet), vbDirectory)
     If zGetDBPath  "" Then
       zGetDBPath = zUNCs(iUNCSet) & zPath(iUNCSet) & ""
       On Error GoTo 0
     Else
      zGetDBPath = "Error"
     End If
   
   Else
     
     Select Case zUName
      
           Case "Bruce"
               zGetDBPath = "G:BEKDocsWPOA Files"
               
           Case "WPOA1"
               zGetDBPath = "G:WPOADocsDocumentsWPOA Files"
               
           Case Else
               zGetDBPath = "Error"

     End Select   '*** Select Case zUName ***
      
   End If   '*** iUncSet > 0
    
End Function    '*** zGetDBPath() ***

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Might create making a new version compiled version of the front end from the master/source version of the front end.

Release the new version into production.

See if that fixes the issue for the ones that can’t run the report.

Reply | Quote

You would have machine permissions on something like a cash register system, where only certain machines are to be used, then you add user access.

cheers, Paul

Reply | Quote

Thank you all for your reply post.

HiTechCoach

re: To test the theory about computer name you could log onto one of the PC where they were unable to run the report…
Do I log in to their PC (windows) using my credentials, or just application level using my ms access credentials?

re: For the uses that are unable to run the report, do other parts of the Access application run OK?
Yes, as far as I know other parts of the access application works ok.

RetiredGeek
If I understand it correctly, your code can be used every time modification is made to the BE & the FE will automatically be updated and end users will have the latest updates next time they access the FE. Correct?

Paul T
Is this done by right click my db (BE) > Security and set permission etc.?

TIA

Regards,

Reply | Quote

Is this done by right click my db (BE) > Security and set permission etc.?

Sorry, don’t know how to do it in Access.

cheers, Paul

Reply | Quote

Thank you all for your reply post.
RetiredGeek
If I understand it correctly, your code can be used every time modification is made to the BE & the FE will automatically be updated and end users will have the latest updates next time they access the FE. Correct?

OCM,

Actually, the code is designed to be run EVERY TIME the DB is used by calling it in an AutoExec Macro.

46679-Access2010-AutoExec-Macro

Yes, it is a little inefficient to check every time but it does insure that it is done whenever it is needed and does not rely on typically unreliable users actions IMHO!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

The approach RG proposes is what most designers end up with, and it is a reliable method. If you end up with a very large (say > 10MB) front-end, and you have a number of users (say >6) then you might want to look at a deployment manager such as the Total Access Startup deployment manager. But it seems very likely that the issue you have with the one user is a permissions issue – check to make sure his installation of Access shows the network drive as a Trusted Location – File/Options/Trust Center/Trusted Locations.

Reply | Quote

OCM,

You did not mention if you deployed(copied) a new working front end to the user’s computer having an issue.

What, if any, error message displays for the users that are unable to run? Does the report run but only blank?

Reply | Quote

OCM,

Another technique I’ve used to keep the Front End up to date on networked machines is to create a shortcut to a batch file to automatically download the latest version of the FE from a file server.

AccessUPD.cmd

Code:

@echo off
Title [COLOR="#0000FF"]ASCC[/COLOR] -- Update Database Software From Server
rem Programmed by: The Computer Mentor -- aka: RetiredGeek on WSL
rem Updated on:    07/08/2008
set DEST="[COLOR="#0000FF"]C:Documents and SettingsWindows UserMy DocumentsAccess[/COLOR]"
set SRC="[COLOR="#0000FF"]\Ccas02shareddocsAccess[/COLOR]"
cls
echo.
echo. -- Updating [COLOR="#0000FF"]ASCC[/COLOR] Access Database Software --
echo.
echo. -- Copying Access Database Program File   --
xcopy %SRC%"[COLOR="#0000FF"]ASCCDB[/COLOR].mdb" %DEST%* /y
echo.
echo. Press ENTER to continue
Pause

Changes items in BLUE to meet your needs and current Windows version.

Guess I really should rewrite this in POWERSHELL!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

So here’s the PowerShell version. It’s a good example why you would want to use PS over the old Batch language.

Code:

Clear-Host

$Src  = [COLOR="#0000FF"]"G:BEKDocsAccess"[/COLOR]
$Dest = [COLOR="#0000FF"]"\MYBOOKLIVECMSharedTest"[/COLOR]
$DBN  =[COLOR="#0000FF"] "Currency Collection.mdb"[/COLOR]

 

Add-Type -AssemblyName PresentationFramework

$Buttons = [Windows.Forms.MessageBoxButtons]
#Values: OK, OkCancel, AbortRetryIgnore, YesNo, YesNoCancel, RetryCancel

$MBIcons = [Windows.Forms.MessageBoxIcon]
#Values: None Question, [Stop | Hand], 
#        [Warning | Exclamation], [Information | Asterisk]

$MsgBox = [Windows.Forms.MessageBox]

Try {   #Check existance of the source front end database
  $SrcDate  = (Get-ChildItem -Path "$Src$DBN" -ErrorAction Stop).LastWriteTime 
}
Catch {
   $MsgBox::Show("$Src$DBN was not found","Error: Source Database:", 
                 $Buttons::OK, $MBIcons::Stop)
  Exit
}

#Check the existance of the destination directory.

If (-not (Test-Path -Path "$Dest")) {
  $MsgBox::Show("Destination path [ $Dest ] does not exist!",
                "Error: Database Destination:", 
                $Buttons::OK, $MBIcons::Stop)
  Exit
}

Try { #Check existance of the FE database at destination location
   $NoDestFile = $False
   $DestDate  = 
    (Get-ChildItem -Path "$Dest$DBN" -ErrorAction Stop).LastWriteTime 
}
Catch {
  Write-Host "$Dest$DBN was not found"
  $MsgBox::Show("FE Database does not exist at:`n" +
                "[ $Dest ]`n`nFile will be copied!",
                "Warning: Database Destination:", 
                $Buttons::OK, $MBIcons::Information)
  $DestDate = 0
  $NoDestFile = $True
}


If (($SrcDate -gt $DestDate) -or $NoDestFile) {
  Copy-Item -Path "$Src$DBN" -Destination "$Dest$DBN" -Force
  $MsgBox::Show("$DBN copied: `nfrom:`t$Src`n" +
                "to:`t $Dest",
                "Success: Database Copied:", 
                $Buttons::OK, $MBIcons::Information)
}
Else {
  $Msg = "Source & Destination file timestamps for " +
         " [ $DBN ] match.`n`nNo files copied!"
  $MsgBox::Show($Msg,"Warning: Database Destination:", 
                $Buttons::OK, $MBIcons::Information)

}

Copy the file into notepad and save as “Update-AccessFEDB.ps1” include the quotes so Windows doesn’t change the file type to .txt!

To run from a shortcut set the target to: C:WindowsSystem32WindowsPowerShellv1.0powershell.exe -File “C:UsersUserIDDocumentsScriptsUpdate-AccessFEDB.ps1″

Change blue parts to match your machine and where you saved the file! Don’t forget to change the script parts in blue with your data!

Sample output messages:
46685-SourceFIleNotFound

46687-FEMDBnotfound

46686-DestDirNotFound

46688-FENotCurrentlyatSource

46689-MatchingTimestamps

46691-FECopied

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you all for the reply post.

I’ll definitely look into the method suggested by RG to adopt in our environment.

HiTechCoach,
No, the same FE was deployed to everyone at the same time, as they all are in the same program/department.
And, the way the report is set up: first they click ‘Reports’ button  ‘Open Case Report  they will then be prompted to enter a ‘case worker name’
so far so good. After they enter the name, nothing happens (no error either)

I’m not sure if the following test I performed this morning give us any lead the cause(s) of this issue:

1. I can run the report on my PC fine.
2. I’ve managers who are unable to run the report at their PC come to my desk, have them login into ms access (using their credentials) the report runs fine for them.
3. I then went to one of the manager’s office, used my ms access credentials to log into ms access, but I was unable to run the report.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards

Reply | Quote

Thanks for the reply post.

HiTechCoach,

re: You did not mention if you deployed(copied) a new working front end…
The FE that is linked to the BE was deployed prior to I came aboard
re: What, if any, error message displays….
No error. Users click run report > Open Case Report > Prompted to enter a name > click OK, nothing happens

I’m not sure if the following test I performed give us any lead the cause(s) of this issue:
1. I can run the report fine from my computer.

2. Few managers are unable to run the report: But, when I’ve them come to my desk and have them login using their MS access credentials on my PC, the report runs fine for them.

3. I then used my ms access credentials to log into manager’s PC, I was unable to run the report.

4. I checked the reference file on managers’ pc, and determined to be the same as mine.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards,

Reply | Quote

Thanks for the reply post.

HiTechCoach,

re: You did not mention if you deployed(copied) a new working front end…
The FE that is linked to the BE was deployed prior to I came aboard

That long, then it could be a corrupted front end.

That is exactly why I suggested that you copy clean/fresh font end to the users having an issue.

Until you replace the frotn end to see if that fixes the issue. Until you do that it is really a wasit of time to do anything else.

FWIW: All my Access apps automatically copy a clean/fresh copy of the front end to each PC every 7 days at minimum.

re: What, if any, error message displays….
No error. Users click run report > Open Case Report > Prompted to enter a name > click OK, nothing happens

I’m not sure if the following test I performed give us any lead the cause(s) of this issue:
1. I can run the report fine from my computer.

2. Few managers are unable to run the report: But, when I’ve them come to my desk and have them login using their MS access credentials on my PC, the report runs fine for them.

3. I then used my ms access credentials to log into manager’s PC, I was unable to run the report.

4. I checked the reference file on managers’ pc, and determined to be the same as mine.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards,

#1, #2 – you could have a clean, healthy, none corrupted front end.

#3 – they probably have a corrupted front end that needs replaced.

$4 bad reference would probably cause nothing to work.

Reply | Quote

Thanks for the reply post.

HiTechCoach,

re: You did not mention if you deployed(copied) a new working front end…
The FE that is linked to the BE was deployed prior to I came aboard

re: What, if any, error message displays….
No error. Users click run report > Open Case Report > Prompted to enter a name & click OK, nothing happens

I’m not sure if the following test I performed give us any lead to the cause(s) of this issue:

1. I can run the report fine from my computer.

2. When I’ve the managers who are unable to run the report come to my desk and log in to ms access using their own credentials, the
report runs fine for them.

3. When I’m at the managers’ machine (they logged in windows) I attempted to run the report using ms access credentials, I was unable
to run the report.

4. I then used my windows login at the managers’ machine, log into ms access using my credentials, I was unable to run the report

I checked the reference file on managers’ pc, and determined to be the same as mine.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards,

Reply | Quote

Thanks for the reply post.

HiTechCoach,

re: You did not mention if you deployed(copied) a new working front end…
The FE that is linked to the BE was deployed prior to I came aboard

re: What, if any, error message displays….
No error. Users click run report > Open Case Report > Prompted to enter a name & click OK, nothing happens

I’m not sure if the following test I performed give us any lead to the cause(s) of this issue:

1. I can run the report fine from my computer.

2. When I’ve the managers who are unable to run the report come to my desk and log in to ms access using their own credentials, the
report runs fine for them.

3. When I’m at the managers’ machine (they logged in windows) I attempted to run the report using ms access credentials, I was unable
to run the report.

4. I then used my windows login at the managers’ machine, log into ms access using my credentials, I was unable to run the report

I checked the reference file on managers’ pc, and determined to be the same as mine.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards,

#1, #2 – you could have a clean, healthy, none corrupted front end.

#3 – they probably have a corrupted front end that needs replaced.

When you log onto their machine, are you using the same front end database file or do you get your own fresh/clean copy?

$4 a bad reference would probably cause any VBA code using built-in functions to stop working.

Reply | Quote

HiTechCoach,

I converted the production FE in accde format, re-linked to the production BE etc., and copied it to the user’s PC who is having an issue. The user still is unable to run the report: when prompt appears, input a name, click OK, nothing happens & no error.

Other than that, I noticed the benefit of converting it to accde format. Users used to get ‘debug’ window, and vba code etc. This is not the case now.

When you log onto their machine, are you using the same front end database file or do you get your own fresh/clean copy?

Yes, I’m using the same FE (everyone uses the same FE)

TIA

Regards,

Reply | Quote

… Users used to get ‘debug’ window, and vba code etc. This is not the case now.

That could be part of the issue. That would indicate there are lots of bugs/issues that make they application unstable.

This may be difficult to solve until they are fixed.

Reply | Quote

HiTechCoach,

Thank you for feedback. At this moment i’m running the report for the user as needed.

Regards,

Reply | Quote