Test environment

Topic

New Reply

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,

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

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

--Joe

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

Did you set up trusted locations for your dev folders?

This required to enable any VBA code.

Reply | Quote

HiTechCoach,

Many thanks. Setting up trusted locations solved the issue.

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

Regards,

Reply | Quote

You’re welcome.

Glad we could assist.

Wishing you all the best with the project

Reply | Quote

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.

Reply | Quote

Mark,

Thank you for the code,

Regards,

Reply | Quote