Rob Kraft's Software Development Blog

Software Development Insights

Efficient Software Development Surpasses Both Waterfall and Agile Development

Posted by robkraft on May 10, 2021

A product owner asked a developer to add a date picker to the web site in place of a text box. The product owner said it would be nice if the calendar just displayed the five weekdays. The developer knew he could add a date picker but didn’t know if he could exclude Saturday and Sunday from the calendar. He didn’t find any options in the date picker control to exclude the weekend, so he created a new control. It did not render as well across all browsers, and behaved a little quirky sometimes, but it showed only weekdays. This took the developer two weeks to complete, after which he showed it to the product owner. She was not happy with the rendering problems and was very disappointed to learn it cost two weeks to develop. The product owner said she would rather have a control that worked better even though it included weekends, especially if that control only cost a few hours to implement.

The parable tells a tale of software development. It is a sad tale, and a common tale. Many would consider it an example of a project failure. The product owner paid more money than expected for a solution that was not as good as she hoped it would be. This type of problem occurs often in waterfall software development, but it also occurs in Agile software development.

Yet, this problem could have been prevented. The developer and product owner should have communicated more frequently. The product owner should have made it clear that she would like a calendar that excluded the weekdays, but only if it could be completed by adding no more than two additional hours of development. And the developer should have understood that the business is usually willing to settle for less than the asked for if the price of getting exactly what they asked for increases the cost exponentially.

Teams that desire to develop software more efficiently will identify this communication problem and make attempts to reduce the chance it will happen again on future projects. The teams will do this as part of their goal to improve their processes, not because the Agile principles provide this guidance. Software developers should strive to develop software more efficiently, and if following Agile principles helps facilitate that improvement then software developers will follow the Agile principles, but it is important to avoid putting the practice of following Agile principles ahead of efficient software development. Agile principles are a tool we can use to develop software more efficiently, but Agile principles are not the goal of our software development process. Great software developers don’t care if someone considers their processes to be agile or not agile, as long as they are efficient. I feel it is important to stress this last point, because it is the key takeaway from this article: Our goal is not to become an Agile software development team; our goal is to develop software more efficiently.

Developers that already practice Agile development may recognize that the characters in the parable were not following Agile principles, and if they had done so, they would have experienced better outcomes. That is certainly true if the teams were following the principles of Agile, not just some of the common practices. Too often, companies, project managers, scrum masters, and developers adopt practices such as “Daily Standup Meetings”, “Two-week Iterations”, “Retrospectives”, and “Measurement of Velocity” and believe that makes them Agile. But none of these practices would have solved this problem. Following these practices does not make a team is Agile, nor is being Agile the goal. The goal is efficient software development. Agile principles can help you get them, but don’t let your focus on Agile principles cause you to lose site of the goal.

Posted in Coding, Process, Project Management | Leave a Comment »

C# .Net LDAP Injection Prevention

Posted by robkraft on April 30, 2021

OWASP is a great resource for writing secure code, but some of there examples are outdated. For .Net, OWASP, as of this writing, recommends using LinqToAD (which appears to be outdated and no longer supported) or the AntiXSS tool which also appears to be outdated and a bit unreliable.

OWASP LDAP Injection Prevention Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/LDAP_Injection_Prevention_Cheat_Sheet.html

A good example of how attacks on LDAP can occur via injection: https://www.synopsys.com/glossary/what-is-ldap-injection.html

In most cases, you probably just need to valid your input with a white list or a black list. White lists are always more secure than black lists, but if you are adding this check to an existing application you may prefer to start with a black list until you can identify and handle all the special characters you need to support.

Below is a bit of code I wrote to validate data against LDAP Injection risks. I found the Blacklist example here (https://stackoverflow.com/questions/53862391/how-does-ldapdistinguishednameencode-work-with-the-c-sharp-directoryservices-lib) and I added ‘|’, ‘(‘, and ‘)’ to it. Please let me know if you find an error in it!

using System.Text.RegularExpressions;
public class LDAPValidation
{
	static readonly string whitelist = @"^[a-zA-Z\-\.']*$";
	static Regex whiteListRegex = new Regex(whitelist);
	public static bool IsNameValidForLdapQueryWhiteList(string strUserName)
	{
		strUserName = strUserName.Trim();
		if (whiteListRegex.IsMatch(strUserName))
		{
			return true;
		}
		return false;
	}
	
	public static bool IsNameValidForLdapQueryBlackList(string strUserName, bool allowWildCard = false)
	{
		char[] illegalChars = { ',', '\\', '#', '+', '<', '>', ';', '"', '=', '|', '(', ')' };
		if (strUserName.IndexOfAny(illegalChars) == -1)
		{
			if (allowWildCard == false && strUserName.Contains("*"))
				return false;
			return true;
		}
		return false;
	}
	public static void RunTests()
	{
		bool result = false;
		result = IsNameValidForLdapQueryWhiteList("rkraft");
		if (result == false) throw new Exception();
		result = IsNameValidForLdapQueryWhiteList("*");
		if (result == true) throw new Exception();
		result = IsNameValidForLdapQueryWhiteList("#");
		if (result == true) throw new Exception();

		result = IsNameValidForLdapQueryBlackList("rkraft");
		if (result == false) throw new Exception();
		result = IsNameValidForLdapQueryBlackList("*");
		if (result == true) throw new Exception();
		result = IsNameValidForLdapQueryBlackList("#");
		if (result == true) throw new Exception();

	}
}

Posted in Coding, Security | Leave a Comment »

Fix Unable to Resize Microsoft Access Navigation Bar

Posted by robkraft on April 25, 2021

When you create a new Microsoft Access database from a template you may discover that you are unable to see very much of the Navigation Bar on the left. Perhaps this only happens when your monitor is a High DPI monitor such as mine. The fix is simple, simply close and re-open the Access database and the arrows to make the Navigation Bar wider should return as in this dem0: https://www.screencast.com/t/Ig5YErfhdkN8

Posted in Access, Coding | Leave a Comment »

Disable Right-Click Menus in Microsoft Access But Keep the Copy/Paste Menus

Posted by robkraft on February 26, 2021

I hope someone comments on this article to tell me that there is a better solution. I was unable to find it. The application requirements were:

  • Hide the right-click menus on the form and report title bars (shown below) to prevent users from entering “Design View”
  • Keep the right-click menus with textbox controls (shown below) to allow users to use it for Copy and Paste.

Microsoft Access has an option in the Current Database tab of Options to disable all the right-click menus and that option is called “Allow Default Shortcut Menus”. However, when I unchecked that, I could not figure out a way to enable the right-click menus solely within textbox controls to allow Copy and Paste.

My solution is to keep this option (“Allow Default Shortcut Menus”) set to checked (true), then run some code as the users open the application in AutoExec to disable the right-click on the form and report headers. I found no documentation that clearly described how to disable the right-click on the form headers, but with much trial and error I found that if I disabled all CommandBars that did not have a name, I got the result I was looking for.

Private Sub DisableCommandMenus()
    For Each Item In CommandBars
        If Item.Enabled = True Then
            'The menus we want to disable are those that don't have names
            If Trim(Item.Name) = "" Then
                Item.Enabled = False
            End If
        End If
    Next
End Sub

The code above disables menus that don’t have names. It is possible that I will need to disable more of the CommandBars, but at the moment my solution appears to be complete.

Do you know of a better solution to this?

Posted in Access, Coding | Leave a Comment »

Programmatically Invoke Microsoft Access SpellChecker using VBA – acCmdSpelling

Posted by robkraft on February 25, 2021

Microsoft Access has a built-in ability to run a spellcheck on your controls. It works best to call the VBA code to perform the spellcheck from the Exit method of the controls. I recommend calling DoCmd.SetWarnings to False prior to running the spellcheck because this will suppress “The spelling check is complete” message box from showing if there are not spelling errors in the control.

Private Sub Text5_Exit(Cancel As Integer)
    MySpellCheck Me!Text5
End Sub

Private Sub Text8_Exit(Cancel As Integer)
    MySpellCheck Me!Text8
End Sub

Private Sub MySpellCheck(ctl As Control)
    DoCmd.SetWarnings False
    With ctl
        .SetFocus
        If Len(.Value) > 0 Then
            .SelStart = 1
            .SelLength = Len(.Value)
            DoCmd.RunCommand acCmdSpelling
            .SelLength = 0
        End If
    End With
    DoCmd.SetWarnings True
End Sub

Posted in Access, Coding | Leave a Comment »

Trello Removes Feature for Creating Personal Board – A Setback From Their Atlassian Acquisition

Posted by robkraft on February 10, 2021

I discovered today that Trello no longer supports personal boards.  When you create a new board you need to create it within a Team first (teams will be renamed workspaces).

You can get around this by just creating your own team, perhaps name your team Personal.  But I see this as the first of many steps where Atlassian is re-purposing a popular tool for personal time management into a product optimized for users of other Atlassian products.  It is sad when a good product is acquired by another company then altered for different end users.

As I suspect more changes in the future, probably even pricing related, I am hunting for alternatives to Trello now.  There is a good list of some in this article:  The 17 Best Trello Alternatives in 2021 (In-Depth Comparisons) (kinsta.com)

Asana and Wrike are a few good alternatives.

Here is Atlassian trying to put a positive speed on their product degradation: Why can’t I create a board outside of a team anymore? – Trello Help

Posted in Project Management, Web Sites | Leave a Comment »

Track Changes to Access Form Properties in Source Control (Github)

Posted by robkraft on January 30, 2021

Last month I blogged about some code I borrowed and changed and used to track changes to code in Access databases in Github. Since then I have also added code to extract Access Queries along with Properties on Forms and Reports to track all of those in Github also. As maintainers of Access databases know, it is often difficult to identify what changes you intentionally or unintentionally made that may be causing the app to no longer behave as it once did. The code below helps.

I put this code in a github repo, so you can see the current version there: AccessUtilities/TrackAccessChanges.bas at main · RobKraft/AccessUtilities (github.com)

But you can also just copy and paste the code below and stick it in a .bas file and run it.

Run this code to export Form Properties, Report Properties, code from Forms, modules, and reports, along with query sources to external files. Then you can check the files into a source control repo. Do this daily and it gives you a good way to keep track of all the changes you made to your access database over time.

Note that this does not include ALL form and report properties, but you can change the code below if you want ALL of them.

Option Compare Database
    Dim debuggin As Boolean
    Dim filepath As String

Sub GatherInfo()
    debuggin = False
    filepath = CurrentProject.Path & "\"
    
    ExportAllCode
    robListAllFormProps
    robListAllReportProps
    robListAllQuerySQL
End Sub
Sub robListAllReportProps()
    Dim rpt As Report
    Dim reportIsLoaded As Boolean
    Dim outputThisProp As Boolean
    
    On Error Resume Next
    
    For Each rptHolder In Application.CurrentProject.AllReports
        reportIsLoaded = False
        For Each aLoadedReport In Application.Reports
            If aLoadedReport.Name = rptHolder.Name Then
                reportIsLoaded = True
            End If
        Next aLoadedReport
        
        If reportIsLoaded = False Then
            DoCmd.OpenReport rptHolder.Name, acViewDesign, , , acHidden
            If Err.Number <> 0 Then
                If debuggin Then
                    Debug.Print "Unable to analyze report: " & rptHolder.Name & " probably because of needing a specific printer. " & Err.Description
                Else
                    Print #1, "Unable to analyze report: " & rptHolder.Name & " probably because of needing a specific printer. " & Err.Description
                End If
            End If
        End If
        
        Set rpt = Application.Reports(rptHolder.Name)
        If debuggin Then
            Debug.Print rpt.Name
            Debug.Print "RecordSource = " & Trim(rpt.RecordSource)
            Debug.Print "Filter = " & Trim(rpt.Filter)
            ProcessFormOrReportMethods rpt.Properties
            Debug.Print ""
        Else
            Open filepath & "REPORTPROPSfor_" & rpt.Name & ".txt" For Output As #1
            Print #1, "RecordSource = " & Trim(rpt.RecordSource)
            Print #1, "Filter = " & Trim(rpt.Filter)
            ProcessFormOrReportMethods rpt.Properties
            Print #1, ""
        End If
        
        ProcessControls rpt.controls
        
        DoCmd.Close acReport, rpt.Name, acSaveNo
    
        If debuggin Then
        Else
            Close #1
        End If
    Next rptHolder
End Sub

Sub robListAllFormProps()
'https://docs.microsoft.com/en-us/office/vba/api/access.accontroltype
    Dim frm As Form
    Dim formIsLoaded As Boolean
    Dim outputThisProp As Boolean
    
    For Each frmholder In Application.CurrentProject.AllForms
        formIsLoaded = False
        For Each aLoadedForm In Application.Forms
            If aLoadedForm.Name = frmholder.Name Then
                formIsLoaded = True
            End If
        Next aLoadedForm
        
        If formIsLoaded = False Then
            DoCmd.OpenForm frmholder.Name, acDesign, , , acFormReadOnly, acHidden
        End If
        
        Set frm = Application.Forms(frmholder.Name)
        
        If debuggin Then
            Debug.Print frm.Name
            Debug.Print "RecordSource = " & Trim(frm.RecordSource)
            Debug.Print "Filter = " & Trim(frm.Filter)
            ProcessFormOrReportMethods frm.Properties
            Debug.Print ""
        Else
            Open filepath & "FORMPROPSfor_" & frm.Name & ".txt" For Output As #1
            Print #1, "RecordSource = " & Trim(frm.RecordSource)
            Print #1, "Filter = " & Trim(frm.Filter)
            ProcessFormOrReportMethods frm.Properties
            Print #1, ""
        End If

        ProcessControls frm.controls
        
        
        DoCmd.Close acForm, frm.Name, acSaveNo
    
        If debuggin Then
        Else
            Close #1
        End If
    Next frmholder
    
    

End Sub
Private Sub robListAllQuerySQL()
    For Each qryd In Application.CurrentDb.QueryDefs
        If Left(qryd.Name, 1) <> "~" Then
            Open filepath & "QUERY_" & qryd.Name & ".qry" For Output As #1
            Print #1, Trim(qryd.SQL)
            Close #1
        End If
    Next qryd
End Sub
Private Sub ProcessFormOrReportMethods(ctl As Properties)
    For Each prp In ctl
        outputThisProp = False
        If Left(prp.Name, 2) = "On" Then
                If Trim(prp.Value) <> "" Then
                    outputThisProp = True
                End If
            End If
            If (prp.Name = "BeforeUpdate" Or prp.Name = "AfterUpdate") Then
                If Trim(prp.Value) <> "" Then
                    outputThisProp = True
                End If
            End If
            If outputThisProp = True Then
                If debuggin Then
                    Debug.Print prp.Name & " " & Trim(prp.Value)
                Else
                    Print #1, prp.Name & " " & Trim(prp.Value)
                End If
            End If
        'End If
    Next prp
End Sub
Private Sub ProcessControls(controls As controls)
        For Each ctl In controls
            If ctl.ControlType <> acLabel And ctl.ControlType <> acRectangle And ctl.ControlType <> acPage And ctl.ControlType <> acLine _
                And ctl.ControlType <> acObjectFrame And ctl.ControlType <> acPageBreak And ctl.ControlType <> acTabCtl _
                And ctl.ControlType <> acCommandButton Then
                If debuggin Then
                    Debug.Print TypeName(ctl) & " - Name = " & ctl.Properties("Name")
                Else
                    Print #1, TypeName(ctl) & " - " & ctl.Properties("Name")
                End If
        
                For Each prp In ctl.Properties
                    outputThisProp = False
                    If prp.Name = "LabelName" Or prp.Name = "Text" Or prp.Name = "SelText" Or prp.Name = "SelStart" Or prp.Name = "SelLength" Or prp.Name = "ListCount" Or prp.Name = "ListIndex" Then
                    Else
                        If ctl.ControlType = acTextBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "DefaultValue" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acCheckBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "DefaultValue" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acListBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "ColumnCount" Or prp.Name = "RowSource" Or prp.Name = "RowSourceType" Or prp.Name = "BoundColumn" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acComboBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "ColumnCount" Or prp.Name = "RowSource" Or prp.Name = "RowSourceType" Or prp.Name = "BoundColumn" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acOptionGroup Or ctl.ControlType = acOptionButton Then
                            If prp.Name = "ControlSource" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acSubform Or ctl.ControlType = acToggleButton Then
                            If prp.Name = "SourceObject" Or Left(prp.Name, 4) = "Link" Then
                                outputThisProp = True
                            End If
                        Else
                            If ctl.ControlType = acRectangle Or ctl.ControlType = acPage Or ctl.ControlType = acLine Or ctl.ControlType = acObjectFrame Or ctl.ControlType = acPageBreak Or ctl.ControlType = acTabCtl Then
                            Else
                                outputThisProp = True
                            End If
                        End If
                        If Left(prp.Name, 2) = "On" Then
                            If Trim(prp.Value) <> "" Then
                                outputThisProp = True
                            End If
                        End If
                        If (prp.Name = "BeforeUpdate" Or prp.Name = "AfterUpdate") Then
                            If Trim(prp.Value) <> "" Then
                                outputThisProp = True
                            End If
                        End If
                        If outputThisProp = True Then
                            If debuggin Then
                                Debug.Print vbTab & prp.Name & " " & Trim(prp.Value)
                            Else
                                Print #1, vbTab & prp.Name & " " & Trim(prp.Value)
                            End If
                        End If
                    End If
                Next prp
            End If
        Next ctl
 
End Sub



Public Sub ExportAllCode()

    Dim c As Variant
    Dim Sfx As String
    Dim filen As String

    For Each c In Application.VBE.VBProjects(1).VBComponents
        Select Case c.Type
            Case 2 'vbext_ct_ClassModule, vbext_ct_Document
                Sfx = ".cls"
            Case 100 'vbext_ct_MSForm
                Sfx = ".frm"
            Case 1 'vbext_ct_StdModule
                Sfx = ".bas"
            Case Else
                Sfx = ""
        End Select
        
        filen = c.Name
        If Sfx <> "" Then
            c.Export _
                FileName:=CurrentProject.Path & "\" & _
                filen & Sfx
        End If
    Next c

End Sub

Posted in Access, Coding | 1 Comment »

Forcing dynamic content to render Client-Side on a Netlify Gridsome SSR Vue Site

Posted by robkraft on January 19, 2021

I inherited a site build on Netlify using Gridsome and Vue Server-Side Rendering (SSR).  The site is pretty good.  It loads very fast and the content is easily maintained by people that are not programmers.  However, we occasionally want to embed a form from another site, such as OpenForms.com, and that is challenging.  The idea behind Vue SSR is that the server will render and load ALL the content then provide it to the browser, and trying to run JavaScript on the client is challenging because traditional events used by SPA and web page JavaScript programmers don’t fire.

Furthermore, the Gridsome CMS uses Markdown for web page content, but the ability to place HTML and JavaScript in these pages is limited.  I battled for days to create a solution and am sharing how I made it work.  I’ll admit it may not be the best solution; please let me know if there is a better way; but this solution works robustly.

Step 1: In the Gridsome MarkDown page, add the anchor tag that links to your form as provided by OpenForms.  Something like this:

<a class="openforms-embed" href="https://us.openforms.com/Form/{your form ID here}">Click here to view form.</a>

I did not include the script tag to load the JavaScript here because it won’t load reliably here, so I load it elsewhere.

Step 2: On the Vue page that renders the MarkDown page from Step 1, add code in the mounted() and updated() events. (The console.log is not necessary, but I use it to help me understand what is going on.)

export default {
   mounted() {
     console.log("mounted basic: " + window.location.pathname);
    evalScripts()
    
  },
  updated() {
    console.log("updated basic: " + window.location.pathname);
    evalScripts()
  },

Step 3: The important piece is calling the evalScripts() method I wrote which is this:

function evalScripts() {
  //This SeamlessOpenForms is specific to USOpenForms to get an openform to render every time 
  //the page is refreshed or viewed.
  if (typeof(SeamlessOpenForms) != 'undefined')
  {
      SeamlessOpenForms.loadOpenForms();
  }
  else {
      const openforms = document.querySelectorAll(".openforms-embed");
      if (openforms.length>0)
      {
        console.log("missing SeamlessForms: " + window.location.pathname);
        const scriptPromise = new Promise((resolve, reject) => {
          var scriptElement = document.createElement('script');  
          document.body.appendChild(scriptElement);
          scriptElement.src = 'https://us.openforms.com/Scripts/embed-iframe.js';  
          scriptElement.onload = resolve;
          scriptElement.async = true;
        });
        scriptPromise.then(() => { SeamlessOpenForms.loadOpenForms();});
    }
  };
}

I will attempt to explain what I think is going on with USOpenForms and the code above.  First of all, this code is risky because I reviewed the JavaScript in https://us.openforms.com/Scripts/embed-iframe.js provided by OpenForms to figure out what to do here, and it is very possible that OpenForms will change their JavaScript and what I am doing here will no longer work (our fallback is to put this in an Iframe, but that causes two vertical scroll bars).

When the JavaScript file (embed-iframe.js) loads it executes and looks for any anchor tags in the DOM with a class of .openforms-embed.  If it finds an item with that tag, it uses the src property to pull in the form and render it within the current page.  However, if a user navigates first to another MarkDown page, based on the same Vue Page, the embed-iframe.js looks for those anchor tags on that MarkDown page and does not find them.  When the user navigates to the MarkDown page containing the anchor tag, the embed-iframe.js does not load and run to look for anchor tags because it already did so when the first MarkDown page for that Vue component loaded.

The script above, gets called by either the mounted() or updated() event, one of which will fire every time a MarkDown page is loaded or refreshed.  The script will render the OpenForm via SeamlessOpenForms.loadOpenForms() if the JavaScript to do so is already loaded, but if not it will dynamically load that JavaScript, then perform the render code (SeamlessOpenForms.loadOpenForms();)

FYI – Here is a simple example of loading the form in an IFrame, which is what we did initially in the MarkDown until I got the embedded form JavaScript to work.

<iframe height="600px" width="100%" style="border:none;" src="https://us.openforms.com/Form/{your form id}"></iframe>

The explanation:

In the Gridsome/Vue SSR architecture, some window/DOM events only fire when the first web page (the first MarkDown page) based on that Vue Page is loaded.  So if you have dozens of MarkDown files that all use the same Vue Page (such as BasicPage.Vue), some javascript methods and Vue events only run when the first MarkDown page based on the Vue Page is loaded.  But the mounted() or updated() events always fire when a MarkDown page is loaded, rendered, or refreshed.

Posted in Coding, Web Sites | Leave a Comment »

Type Mismatch Error in VBA for Microsoft Access Parameters Collection

Posted by robkraft on January 8, 2021

I ran into a strange problem today with an project that I took over where code in Microsoft Access VBA is being converted. The original database, with untouched source code, has the following lines of code to cycle over all of the parameters in a querydef. The code below has been working for 15 years:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next

However, our current Access database project (*.mdb) that has the exact same code, but we have modified “something” now gets a “Type Mismatch” error number 13 when we try to “For Each” over the results of qdf.Parameters.

If I open a watch on qdf.Parameters I can view the parameters without a problem. There is only one parameter and I even confirmed that all properties of the parameter are identical when they run in the old version of our Access database app.

I could not figure out why the code no longer works. Even the query that runs is identical. However, I was able to fix the code to work again for all cases by replacing the For Each block with the following:

Dim i As Integer
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i).Value = Eval(qdf.Parameters(i).Name)
Next i

Posted in Access, Coding | Leave a Comment »

Tracking Microsoft Access Code Changes in GitHub; And Searches with Visual Studio

Posted by robkraft on December 27, 2020

I will admit up front that this approach is not as awesome as the post title may make it sound, but I do find it to be very useful and fairly simple, just a little tedious.

As a consultant, I occasionally get the Access database project that needs some fixes. Of course we can often easily make copies of the .mdb or .accdb files regularly, but as a programmer I want more. I want to be able to see the history of my code changes over time so that I can identify where changes had unintended consequences. I do this by exporting all of the source code out of the Microsoft Access database into flat files, then importing the flat files into a git repo. I have a bit of code below that I use for this, which I found elsewhere on the Internet and modified a little.

I create a new module and place the code below in that module. Then I execute the subroutine. Following that I import or update the files in my git repo. The approach works well but the export does not include macros, and it does not include properties and events on forms and reports, nor table structures. Still, I have found it useful. I also use this approach to pull up all the files I exported in Microsoft Visual Studio which has search tools I like better than those built into the Access database.

I run the code below about once per day to track the latest changes I made, then apply the updates to the git repo.

Public Sub ExportAllCode()

    Dim c As Variant
    Dim Sfx As String

    For Each c In Application.VBE.VBProjects(1).VBComponents
        Select Case c.Type
            Case 2 'vbext_ct_ClassModule, vbext_ct_Document
                Sfx = ".cls"
            Case 100 'vbext_ct_MSForm
                Sfx = ".frm"
            Case 1 'vbext_ct_StdModule
                Sfx = ".bas"
            Case Else
                Sfx = ""
        End Select

        If Sfx <> "" Then
            c.Export _
                FileName:=CurrentProject.Path & "\" & _
                c.Name & Sfx
        End If
    Next c

End Sub

Posted in Access, Coding | Leave a Comment »