News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Test environment

    This topic contains 3 replies, has 3 voices, and was last updated by  WSHiTechCoach 2 years, 7 months ago.

    • Author
      Posts
    • #508252 Reply

      WSOCM
      AskWoody Lounger

      Greetings,

      We have a ms access 2016 split db (BE on a shared drive, FE on users’ PCs).
      I copied both BE/FE locally onto my desktop as a test environment so I can apply my modifications, and test to make sure that it’s working before I distribute the latest updates.
      I just made a very minor change to the FE (login form) but when I tried to test it nothing happens. Are there any steps that I’m missing? And, what is the best practice in creating a test environment?

      TIA

      Regards,

    • #1591785 Reply

      RetiredGeek
      AskWoody MVP

      OCM,

      Since you Moved the BE to your local drive did you make the change in the FE to relink the tables?

      I have code in my FEs that checks the userid and if it sees my userid it changes the location where it looks for the BE and then automatically relinks the tables.

      Code:
      Option Compare Database
      Option Explicit
      
      '+---------------------------------------------------------------------------+
      '| Version: 10.8                       Programmed by: The Computer Mentor    |
      '| Dated  : 03/01/2016                           aka: Bruce E. Kriebel       |
      '+---------------------------------------------------------------------------+
      '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"
      
      '                            +--------------------+               +----------+
      '----------------------------|  RelinkExtTables() |---------------| 01/23/15 |
      '                            +--------------------+               +----------+
      'Called by: Macro - AutoExec
      'Calls    : [Utilities] zGetDBPath()
      '           [Utilities] HideDBWinsdow()
      '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 Glas
         Application.Echo True    'Screen Updating ON
          
      End Function    'RelinkExtTables()
      
      '                          +---------------------+                 +----------+
      '--------------------------|    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

      • #1592492 Reply

        WSMarkLiquorman
        AskWoody Lounger

        RG,
        How did you insert the code like that? Is it an image?

        • #1592511 Reply

          joep517
          AskWoody MVP

          RG,
          How did you insert the code like that? Is it an image?

          Use code tags. See Outputting code or fixed width data.

          --Joe

        • #1592539 Reply

          WSHiTechCoach
          AskWoody Lounger

          RG,
          How did you insert the code like that? Is it an image?

          Mark, if you click the “Go Advanced” button in the lower right it will give you a lot more formatting options on the the toolbar. There you will see the # button for inserting the the code tags. This is were you will find the option to add attachments.

    • #1591837 Reply

      WSHiTechCoach
      AskWoody Lounger

      Note: This has been cross posted here Set Up A Test Environment, Access 2016

      TIP: Make lots of back ups. I make a ZIP back up of every database before I open it.

      I keep a master copy of the front end that I never use for development.
      I always develop on copies. Both front end and back end.
      I use a development copy of the front end to make design changes and testing. It is ONLY ever linked to a development back end.

      I use multiple development and testing back ends. Besides using a copy of the production back end, I have a back end with “dirty” data for testing error handling. I have back end with a lot of data (500,000 to 1,000,000+ records) added to transaction tables to load/stress test.

      I keep all the development back ends on the server in a dev folder. This helps identify performance issues much faster.

      I will NEVER update a front end while linked to production/live data. I will only update a front end while linked to a development back end.

      Once my changes are fully tested, I import the changes into the master copy of the front end. Where a final test is done while it is still linked to the development back end.

      When ready to release I compile the master front end into a mde/accde. The mde/accde is opened and linked to the production data. This is also when code is run to lock it down.

      If I was making the change to the login form, I would have renamed the original form then “Save As” it back to the original name. If I break it, I can make another or compare to the original. If I did not make a copy of the object, I could import it back from my master copy of the front end. I could also unzip the copy of the database I just made to restore the object or compare them.

    • #1591838 Reply

      WSHiTechCoach
      AskWoody Lounger

      Note: This has been cross posted here: Set Up A Test Environment, Access 2016

      See: A message to forum cross posters

      … And, what is the best practice in creating a test environment?

      TIP: Make lots of back ups. I make a ZIP back up of every database before I open it.

      I keep a master copy of the front end that I never use for development.
      I always develop on copies. Both front end and back end.
      I use a development copy of the front end to make design changes and testing. It is ONLY ever linked to a development back end.

      I use multiple development and testing back ends. Besides using a copy of the production back end, I have a back end with “dirty” data for testing error handling. I have back end with a lot of data (500,000 to 1,000,000+ records) added to transaction tables to load/stress test.

      I keep all the development back ends on the server in a dev folder. This helps identify performance issues much faster.

      I will NEVER update a front end while linked to production/live data. I will only update a front end while linked to a development back end.

      Once my changes are fully tested, I import the changes into the master copy of the front end. Where a final test is done while it is still linked to the development back end.

      When ready to release I compile the master front end into a mde/accde. The mde/accde is opened and linked to the production data. This is also when code is run to lock it down.

      If I was making the change to the login form, I would have renamed the original form then “Save As” it back to the original name. If I break it, I can make another or compare to the original. If I did not make a copy of the object, I could import it back from my master copy of the front end. I could also unzip the copy of the database I just made to restore the object or compare them.

    • #1591849 Reply

      WSOCM
      AskWoody Lounger

      Thank you both for your feedbacks.

      RetiredGeek,
      re: … did you make the change in the FE to relink the tables?
      No, I didn’t. That is probably my main issue.
      I appreciate for sharing your code. Where can I add this code, and do I need to modify it?

      HiTechCoach
      This is exactly the type of best practice I was looking for.

      Regards,

    • #1591850 Reply

      WSHiTechCoach
      AskWoody Lounger

      OCM,

      Are you already using the the built-in Link Table mange to switch back ends?

      You can always use the built-in Link Table manager to swap back ends. Code that etiredGeek posted is not required but is for your convenience.

      Since you are learning, I would recommend using the built-in tools that are always available within Access. Once you understand how the relinking process works then use a VBA Code shortcuts. There will be times you will need the Link Table Manger to do something that the VBA code can’t handle without modifying the code.

    • #1591899 Reply

      RetiredGeek
      AskWoody MVP

      OCM,

      Look at the: Public Function zGetDBPath() As String

      This function, once modified for your setup, determines, via user name, which BE to link up. So there is no need to make any adjustments after the initial setup.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1592008 Reply

      WSOCM
      AskWoody Lounger

      RetiredGeek /HiTechCoach

      I’m not sure where I went wrong, but using the built-in Link Table Manager, I did re-link my FE to the BE, but I was unable to open the form successfully.

      My folder structure locally (desktop) is as follows

      two folders

      a. Copy folder –> I copied both BE/FE from the shared drive to this folder (just to be safe)

      b. Development folder –>  Another copy of the BE/FE where I perform my mods.

      And, below are steps I took to re-link:

      1. Open the FE (shift + open)
      2. Right Click the table, select ‘Linked Table Manager’
      3. Select All & check ‘Always prompt for new location’
      4. Browse to desktop to the ‘Development folder’ and select the BE, click open

      I got a message saying ‘All selected linked tables were successfully refreshed” click ok and close.

      When I opened the FE, the login form opened, I typed in my credentials and clicked ok. Nothing happened.

      TIA

      • #1592035 Reply

        WSHiTechCoach
        AskWoody Lounger

        Have you tried restoring the original logon that work before you made any changes? Does it work. If it does not work, then you have other issues.

    • #1592153 Reply

      WSOCM
      AskWoody Lounger

      HiTechCoach,

      Actually, I used the original logon form, not the modified logon form. I did this to see if I can logon to my newly created dev. environment.

      TIA,

    • #1592158 Reply

      WSHiTechCoach
      AskWoody Lounger

      Did you set up trusted locations for your dev folders?

      This required to enable any VBA code.

    • #1592161 Reply

      WSOCM
      AskWoody Lounger

      HiTechCoach,

      Many thanks. Setting up trusted locations solved the issue.

      Now, I should be able to make mods & test locally.

      Regards,

    • #1592170 Reply

      WSHiTechCoach
      AskWoody Lounger

      You’re welcome.

      Glad we could assist.

      Wishing you all the best with the project

    • #1592490 Reply

      WSMarkLiquorman
      AskWoody Lounger

      I use a different technique for linking:

      Code:
         Set dbData = DBEngine.Workspaces(0).OpenDatabase(strDataLocationPath)
         txtConnection.Visible = True
         For Each mytdf In mydb.TableDefs
            If Left(mytdf.Name, 1)  "~" And Len(mytdf.Connect) > 0 And Left(mytdf.Connect, 1) = ";" Then
               strTableName = mytdf.Name
               txtConnection.Value = strTableName       'just shows progress
               Me.Repaint
               mytdf.Connect = ";Database=" & strDataLocationPath
               mytdf.RefreshLink
            End If
         Next
         txtConnection = "Linking Complete!"
         mydb.Close
         Set mydb = Nothing
         dbData.Close
         Set dbData = Nothing
      

      This technique automatically finds all linked tables; the assumption is that all linked tables are connected to the same backend (a little extra coding if that is not the case). “strDataLocationPath” is that backend location, which may come from different places. Usually I have a linking form that contains the path to the Backend; often a choice between a network location (for production) and a local location (for testing). I believe this technique of refreshing the connection string is faster especially if you have a lot of tables.

      • #1592610 Reply

        WSOCM
        AskWoody Lounger

        Mark,

        Thank you for the code,

        Regards,

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Test environment

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.