DB Management, Switchboard (1 Viewer)

[mjdemaris]

I have a couple of questions to post here. I am working on a sizable project and would like to be able to switch between Dev mode and Prod mode easily. I am considering creating a table with version numbers and using a module descriptor such as 'INV' for the main Inventory objects. Then, I think I could build a form that would allow me to select/enter which module of the app I want to test in Dev/Prod mode - which would then enable or disable certain code so I can open forms without going through user login/validation, etc.

Then I remembered seeing posts on Switchboards. I just used the wizard to create a Switchboard form, but there is nothing on it. It's continuous form, no code... I've never used a Switchboard and don't really know what it's supposed to do.

On an old thread, Gemma states he uses the 2003 Switchboard, even in later versions.

So, what are your thoughts on creating something to switch between Dev/Prod mode easily?

And, does someone have an example Switchboard or management system they use whilst building and testing?

Thanks,
Mike

 

[theDBguy]

Hi Mike. I am not sure you're really looking for a switchboard (unless that term applies to more than one application).

The built-in switchboard is merely a menuing system to quickly allow the user to go through the different parts of the database.

I am thinking what you need is more like a form or code to quickly switch the data connection between dev and prod BE. I have seen several demoes for that.

However, if you're trying to test new functionalities of your FE, I would think it is better to simply have two separate copies of your files. One for dev and another for testing. You could put them in separate folders. You could then put a copy of the test or prod BE into those folders.

So, when you need to add new features, you open the dev FE. When you want to test it, you create a copy and put it in the test folder and run it from there. When you're happy with it, you can deploy it to prod environment.

Just my 2 cents...

 

[jdraw]

I suggest you keep Dev and Prod separate. If the only thing saving a terrible/accidental change is you at the keyboard, then keep them separate. Don't put you PROD CODE AT RISK. You could have a third area Test/Maintenance, and move/import materials from either DEV or PROD for "testing" as needed. Ensure you have a good procedure(s) for starting test; importing objects; review/document tests; replace PROD modules/objects carefully. Also, make it simple to BACKUP materials being tested.
There's nothing worse than the "ooops did that just update prod" feeling!!!!

 

[mjdemaris]

Yeah, I get that!

However, I am not currently running a production version yet. Have not even split it yet. I suppose I should preface this with 'I basically go with the rapid design/development (prototyping?) idea.' Meaning - I design, build and test all at the same time, basically.

And I must admit I do not have this:

good procedure(s) for starting test; importing objects; review/document tests; replace PROD modules/objects carefully

Honestly, I am not sure how to develop such procedures. I usually find something to add/missing/errors, etc and fix it.

So going back and forth between Dev/Prod, at this point, means I want to open some forms without a block of code running that would normally run in my envisioned production. One issue with having two separate files whilst I am still building is that I would need to comment/uncomment code in several forms before testing.

For instance, my user name is set to provide special access, but if I want to test with someone else's, then I need to comment out a few lines in the forms I want to test.

Thinking this over, I could use some good tips on creating such procedures and on how to build a library of modules that I could easily import and black box test, so to speak. I noticed a while back someone shared a pic of a million folders with various utilities, made me jealous, lol.

 

[Gasman]

You could just set a global variable or tempvar to show what mode you are running?

 

[jdraw]

I agree with Gasman --use some Tempvars (or some very simple set up) so you can differentiate GenUser from AdminUser.
I would not be naming anything PROD based on your description of what you are doing. You may have DEV and ACCeptable. But you need some parameters for your own good. My guess is you are working in trial and error mode.

You may get some ideas from my Stump The Model. I recommend a high level model to ensure scope is correct; then more detailed model(s) as specific areas/info evolve.
Do you have a naming convention (alphas, no spaces in object names)?
Do you describe fields along with meaningful names?
Do you have test data (good and bad)? You need it to successfully validate things
Do you have some sort of business rules?
Do you have documentation/reference material on Who does what, when, how much, how often...?
You should determine and document what makes "something Acceptable"/quality assured.
Do you have any separate/objective testers/accepters?

There are lots of articles/tutorials in the Database Planning and Design link in my signature.

You may find this thread helpful.

 

[Pat Hartman]

However, I am not currently running a production version yet. Have not even split it yet.

Today would be a good day to split the db. Saving this until the end just makes more work for you because you have to completely retest the app once you split it AND release it to the users so they can test it in a multi-user environment. Then and only then is it ready for "production"

Your procedures, as you are envisioning them, are going to get you into trouble.

It is very important to keep production and development versions completely separate and for larger releases such as the initial one, you should have a third environment to be used for multi-user testing. The point of this is so you can keep developing and give the users incremental, stable versions.

So, you need THREE FE's AND THREE BE's. Switching from one BE to another is easy enough with the linked tables manager.. Just to keep people aware, I have a big box on the menu that says test or QA (quality assurance) in red and is hidden for production. Plus you need a robust backup plan. I have that hardcoded in most of my apps so if it is "me" and I'm closing the version linked to the test tables, it asks if I want to create a backup. So during the day, I make sure I close the FE i am working on at least once an hour and after I complete something major before I start something new. I clean up the backups after a couple of weeks. I don't delete them immediately. I always want a bunch of incremental backups hanging around in case the copy I'm working on gets corrupted. I have other backup plans that actually export all objects to text rather than just creating a copy of the .accdb. This is actually a much safer way of making backups because a database that seems to be working fine might not open up next time and you could loose a lot if you are in a heavy development cycle.

Rather than hardcoding the rules for YOUR userID, you should create a security system that lets you define rules in tables. Your code then takes the logged in UserID and determines by looking in the table if that person is authorized for what he is trying to do.

I can post a sample app (just ask) that does this using a custom switchboard. It lets you assign read, add, change, delete permissions to each user and then assigns the same permissions to each form/report opened by the switchboard. This is rudimentary but sufficient for many systems. A better version would allow users to be assigned to groups and then have permissions assigned to groups.

 

[mjdemaris]

@Pat Hartman It sounds like you have a form open (like a menu/switchboard) while you are developing; and when it closes, your code backs up your files. I could probably find a way to do that as long as I don't hit the 'Close all'.

I would like to know how you export your objects to text, and how would you recover using it?

For security, and thinking along the lines of @Gasman, I do have user tables in place for permissions, though the group thing is a bit of a headache around here. There are some things that only a manager should do, but if he takes time off, he then asks one of his team members to do it, who would normally have lower permissions. Anyway, getting to Gasman's point, I was thinking of using a table to store a value that would indicate if I'm running forms in dev mode for me, or wanting to test other user's permissions, then assign that to a temp variable so each form could check that value and run the corresponding code.

I might be overthinking this a bit, now that I reread it and your guys' replies. idk.

@jdraw I do have naming conventions, and some sort of business rules. As far as who does what, etc. most of that is in my head. And as I mentioned above, some of it changes if people are on vacation, and those other people wouldn't normally perform those actions/duties.

As far as what makes it acceptable...not real sure. Since I am one of the main end users, I do have an idea of what should be there and what kinds of things I don't want users to enter in, though I am still working on validation as there are some exceptions to the rule.

And as far as testers, not really. Just me. I do ask for feedback and ideas, though. Almost everyone else just wants something that works, and if it don't, well I hear about it!

I will have a look at those links as well, jdraw.

 

[Pat Hartman]

Here is raw code from an old app.
1. the first procedure is in the Unload event of the Login form. The login form stays open as long as the app is open. When the user logs in successfully, the switchboard form is opened but instead of closing, the login form just sets its visible property to False so it stays loaded. Since it is the first form loaded, it is the last form unloaded whenever the app closes. Nothing stop it from running short of pulling the plug or having windows cancel the app.
2. the second procedure saves all object types. I separated this from the actual save so I could use the save code in a different way from other procedures.
3. the last two subs are samples of importing or exporting a single object.

Most of the time, only a single object is the problem so I just imported tht object specifically. However, I am actually working on standardizing the whole thing and making it available as entire piece of code that you can just copy and add to any app but it's not complete so I'm giving you code I'm pretty sure will work for you as long as you change the hardcoded bits with directories and userIDs and as long as you have an initial form that remans open all the time.

Private Sub Form_Unload(Cancel As Integer)
    If Environ("username") = "phartman" Or Environ("username") = "Pat" Then
        If MsgBox("Save as text?", vbYesNo) = vbYes Then
            Call ExportOnClose
        End If
    End If
    
End Sub

Public Sub ExportOnClose()
    Dim strPath As String
    Dim objFSO As Object
    Dim strMsg As String

On Error GoTo ErrProc
    
    'create new folder
    
    strPath = "C:\Pat\RAS\TextFiles" & Format(Date, "yymmdd")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If Not objFSO.FolderExists(strPath) Then
        objFSO.CreateFolder (strPath)
    Else
        strPath = strPath & "A"
        If Not objFSO.FolderExists(strPath) Then
            objFSO.CreateFolder (strPath)
        Else
            strPath = InputBox("enter path please")
            objFSO.CreateFolder (strPath)
        End If
    End If
    'export all objects
    MsgBox "This may take a few minutes.  Please wait for the count message.", vbOKOnly
    Call ExportDatabaseObjects("forms", strPath)
    Call ExportDatabaseObjects("reports", strPath)
    Call ExportDatabaseObjects("modules", strPath)
    Call ExportDatabaseObjects("QueryDefs", strPath)
    Call ExportDatabaseObjects("scripts", strPath)
    strMsg = "Exported Forms = " & iCountForms & vbCrLf
    strMsg = strMsg & "         Reports = " & iCountReports & vbCrLf
    strMsg = strMsg & "         Modules = " & iCountModules & vbCrLf
    strMsg = strMsg & "         Queries = " & iCountQueries & vbCrLf
    strMsg = strMsg & "         Macros = " & iCountScripts & vbCrLf
    Debug.Print strMsg
    MsgBox strMsg, vbOKOnly
    
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
    End Select
End Sub




Public Sub ExportDatabaseObjects(ExportType As String, Optional ExpLoc As Variant)
On Error GoTo Err_ExportDatabaseObjects
    
    'Dim db As Database
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim D As Document
    Dim C As Container
    Dim i As Integer
    Dim sExportLocation As String
    
    Set db = CurrentDb()
''import from text =
''application.Application.LoadFromText acForm, "frmRisks","C:\Temp\TextRiskReview070615\Form_frmRisks.txt"

    If ExpLoc & "" = "" Then
        sExportLocation = "C:\Pat\RAS\TextFiles\" 'Do not forget the closing back slash! ie: C:\Temp\
    Else
        sExportLocation = ExpLoc
    End If
    If Right(sExportLocation, 1) = "\" Then
    Else
        sExportLocation = sExportLocation & "\"
    End If
    Select Case ExportType
        Case "TableDefs"
            For Each td In db.TableDefs 'Tables
                If Left(td.Name, 4) <> "MSys" Then
                    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
                End If
            Next td
        Case "Forms"
            Set C = db.Containers("Forms")
            iCountForms = 0
            For Each D In C.Documents
                Application.SaveAsText acForm, D.Name, sExportLocation & "Form_" & D.Name & ".txt"
                iCountForms = iCountForms + 1
            Next D
        Case "Reports"
            Set C = db.Containers("Reports")
            iCountReports = 0
            For Each D In C.Documents
                Application.SaveAsText acReport, D.Name, sExportLocation & "Report_" & D.Name & ".txt"
                iCountReports = iCountReports + 1
            Next D
        Case "Scripts"
            Set C = db.Containers("Scripts")
            iCountScripts = 0
            For Each D In C.Documents
                Application.SaveAsText acMacro, D.Name, sExportLocation & "Macro_" & D.Name & ".txt"
                iCountScripts = iCountScripts + 1
            Next D
        Case "Modules"
            Set C = db.Containers("Modules")
            iCountModules = 0
            For Each D In C.Documents
                Application.SaveAsText acModule, D.Name, sExportLocation & "Module_" & D.Name & ".txt"
                iCountModules = iCountModules + 1
            Next D
        Case "QueryDefs"
            iCountQueries = 0
            For i = 0 To db.QueryDefs.Count - 1
                Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
                iCountQueries = iCountQueries + 1
            Next i
        Case Else
    End Select

    Set db = Nothing
    Set C = Nothing
    
    'MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    
Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects   
End Sub



Public Sub ImportForm()
    Application.Application.LoadFromText acForm, "sfrmReconcilePayments", "C:\Pat\RAS\TextFiles120926A\Form_sfrmReconcilePayments.txt"
End Sub
Public Sub ExportForm()
    Application.SaveAsText acForm, "frmReconcilePayments", "C:\Pat\RAS\TextFiles120927A\Form_frmReconcilePaymentsOLD.txt"
End Sub

Rather than hardcoding/ i use the login form. that way, If I want to operate as someone else, I log out and then log back in again. It is dangerous to have the type of code you want in the app because it is too easy to not clear it when you export to production. All the security checking in the app references the userID in the hidden login form and his security settings there.

 

[jdraw]

Here's a write-up on Business rules to help with the kind of things involved. There are some helpful and humorous short videos on Business Analysis by BA_Experts. I really recommend that you build a model and vet/exercise it with sample data and sample scenarios. It's difficult to underestimate the "tweaks" you will discover in the process AND it is much easier to make changes at the logical model level than in a physical database. This is more a journey than an event, but it is a learning experience. The vetted model becomes a blueprint for your database.

...though the group thing is a bit of a headache around here..

Using the group approach and assigning userIds to Groups may resolve that headache. It can be a flexible approach to simplify changes and additions.

 

[Pat Hartman]

I'm not going to argue with jdraw, modeling helps you work out the kinks before they cause you days of rewrites. So, this is especially important for the less experienced because they are more likely to make serious design mistakes and since they think that programming is hard, they are always reluctant to trash what they have and start again. I've been doing this for over 50 years and have the scars to prove it. When an expert advises you to start again, the advice comes from experience. It is always easier, cleaner, faster to build from scratch than to try to put Band-Aids on something that isn't quite right. You'll be amazed at how much better the second or even the third iteration is than the first. Just don't feel that a line of code that is written must be preserved at all costs.

On the other hand, I really like the instant gratification of development, especially with Access so I jump in fairly early and use Access as my modelling tool. The difference though is that, I've made pretty much all the major mistakes you might imagine and thousands of the little ones so my initial designs are probably close to what will be released in the end and I rarely make actual schema design mistakes which are the hardest to fix. Why do you think I can answer so many questions? I have painful memories of all the mistakes I ever made plus the new once I see here all the time

I do remember one painful mistake I made about four years ago. I was doing a project for a company that marks lines on roads and parking lots. So their inventory was paint and reflective particles that they spread on the wet paint for certain types of roadwork. The paint was in 5-gallon cans, bigger barrels, tanks, and the tanks on the trucks themselves. Every day when the trucks came back to the plant, they had to measure the paint in the onboard tanks so they knew how much they had used and calculate that they had actually laid the right thickness for the miles they drove. They used measuring sticks to do that, similar to your oil dipstick. Being a girl and one to leave the oil changes to the garage, I had no idea that the dipstick told you how much you needed to ADD rather than how much was left. So, all of my calculations were backwards. The readings they were giving me were "air" rather than "paint". Of course, the error was discovered immediately by the user. Luckily, the calculations were in a called procedure so there was only one place to change the code. That's the good and the bad of it. I was embarrassed at such a simple mistake but the fix was trivial. The reason the fix was trivial was because of good defensive programming techniques learned over years of developing. I had consolidated the calculations from the beginning because I knew I would need to use them from different places. A novice might not have recognized that and might have ended up with multiple pieces of code that needed to be changed. But here is where the pro in you develops. When you find yourself in this kind of pickle, don't change each piece of code, do what you now know you should have done at the beginning, replace the code with a common called procedure.

 

[mjdemaris]

Current Login setup:
1. open app
2. Autoexec checks for me
3. If normal users, open Welcome form.
a. check version info, update if necessary
b. initialize temp vars
c. get user name, id, dept
d. hide nav pane, ribbon
e. open Logout timer form, which stays open
f. if the login is a generic maintenance, open another form to allow them to select
their name. close this form
g. open the Menu form, which allows users to do various things, depending on their duties
h. close welcome form
i. Logout timer form checks for a boolean value if i need to close the app for an update.
4. If I open the app, Logout check form opens if I need to force logout and shutdown, then the timer form,
then a login form opens that allows me to 'login' as another user.
a. check version info
b. initialize temp vars
c. get user info
d. open Menu form

So the Logout Timer form is the one that stays open fulltime. (I don't remember who I got this setup from,
I should have entered it in a module header somewhere when I first used it. (Scott, Allan, Pierce...)

The main reason I hardcoded my user info into the forms was so that I wouldn't have to go through several forms each time I
wanted to make design changes and test how it works.

Being a girl and one to leave the oil changes to the garage, I had no idea that the dipstick told you how much you needed to ADD

One of the first things I teach them when they want to drive, check the fluids and other basic driver maintenance. (They don't usually stick to it tho, lol.)

50 years is a long time to be doing this...my hat's off to you, Pat!

Jdraw: Going through the business rule/data modeling stuff...my head spins. I've done reading before on this topic and have tried to formulate some of it, and even attempting to create some graphic models. But man! I tend to get turned around and lost real quick. Then I tend to jump in and build, then I forget about the design docs, lol.

 

[jdraw]

...Then I tend to jump in and build, then I forget about the design docs, lol....

We've all been there. It's very satisfying to write some code and run a test and confirm that it "works as planned". If, as Pat has mentioned, you have 50 years experience and intimate knowledge of the business, and explicit requirements, and independent reviewers to vet interim results..... then by all means jump right into coding. But without those things better to work on incremental pieces in a prototyping way to ensure and confirm the purpose, design and operation of the piece meets the requirements. With experience you'll find that happy middle ground that works for you. Just remember there's nothing worse than a misunderstanding of basic requirements that results in a system that doesn't do what was expected.

Totally agree --50 years is a long time to be doing this.

 

[Pat Hartman]

One helpful thing during the design phase is to use Access to keep track of attributes as you discover them to make a data dictionary. Record the name the user uses so you can use that for labels, the data type, and the length if it is fixed. Have columns for entity so you can collect the attributes into groups that will later become tables. Keep notes on business rules. If you do this in a standardized enough way, you can use the dictionary to build your tables when the time comes.

 

[digitioo]

Good information you solve my problem. Thank you!

 

[Pat Hartman]

One of the first things I teach them when they want to drive, check the fluids and other basic driver maintenance.

I'm sure I learned that when I learned to drive but back then, there was actual "service" at a service station and young men always happy to check my oil and top it off. There were lots of years in between and although at some level, I did know how a dip stick worked, It just never occurred to me that the paint tanks would work the same way,