Mapping VBA Code to show tables affected by VBA (1 Viewer)

BenMason

New member
Local time
Today, 02:43
Joined
May 14, 2021
Messages
23
I have been given a legacy database and need to map what the VBA does and what tables are affected when a button on a form is clicked. I used the object dependencies, but it doesn't get down to what happens when buttons are clicked to show when tables are updated. Is there a way to map this? I tried using a flow chart, but I don't know the best way to visually show it to my manager.

Is there an example of the mapping that you can share? Or another way to let me know how best to approach VBA mapping. It doesn't need to show everything about the VBA, just when buttons are clicked and the tables that are affected.

Thank you.
Ben
 
I would just look at each buttons click event?
Perhaps there is something in VBA Extensibilty that could help?
 
need to map what the VBA does and what tables are affected when a button on a form is clicked
To understand what VBA code does, you need to look at and understand the VBA code. The code itself is the "map."
 
Ben,
If you post a code example along with the desired result, I'm sure others will understand the requirement and offer some vba solution(s).

If it's button clicks, then you're dealing with Forms. Isolate Form controls (command buttons), then the button click event code.
 
To understand what VBA code does, you need to look at and understand the VBA code. The code itself is the "map."
Thank you. Yes, you are correct. But I've been given an existing database and told to map out what it does. I'm looking for the best way to do it. In Excel, I started with "This button updates tbl_green based on type code. " But it's just words. Should I create a flow chart? Just wondering how to show the steps visually.

Thanks again. Ben
 
Ben,
If you post a code example along with the desired result, I'm sure others will understand the requirement and offer some vba solution(s).

If it's button clicks, then you're dealing with Forms. Isolate Form controls (command buttons), then the button click event code.
Thank you.
 
I know of at least to two accdb analyzers offered for free as downloads by Access developers. Crystal Long offers one. Colin Riddington offers one. There is also a commercial application from FMS, Inc.

Colin posts here as Isladogs, and I'm sure you can find his site easily with a search here or on the internet.

Crystal also posts here as striveforpeace, and the same is true for her site; search and it'll turn up.
 
Last edited:
Ben,
Why did you opt for Excel? I agree with George that analyzers exist, but I don't think they describe what the code does. With Access vba you can identify the buttons on every form, and you could extract the click event code for each, However, I think it will require your looking at each click event code to determine WHAT it does exactly. Again, depends on your detailed requirement.
Others may have code to parse the click event code and provide some sense of its functionality.
 
I have been given a legacy database and need to map what the VBA does and what tables are affected when a button on a form is clicked. I used the object dependencies, but it doesn't get down to what happens when buttons are clicked to show when tables are updated. Is there a way to map this? I tried using a flow chart, but I don't know the best way to visually show it to my manager.

Is there an example of the mapping that you can share? Or another way to let me know how best to approach VBA mapping. It doesn't need to show everything about the VBA, just when buttons are clicked and the tables that are affected.

Thank you.
Ben
Seriously?
That might be an almighty task if it's a big database.

Are you trying to rewrite it or something? I can't think it's just for academic interest.

The buttons themselves may not reveal much anyway. You need to understand the data tables and relationships. Once you have that, the behaviour of the forms should really be self explanatory. As long as the data is well normalised.

I mean, tables are updated when you close a form or navigate to a new record. You don't need any code to make that happen. A record just saves automatically unless the contents of the record cause some sort of data error - duplicated records, or a missing value for a mandatory field, in which case there might be code to report the error, but there doesn't have to be. You don't want unhandled errors, but that's part of robust programming, not directly to do with the system architecture itself.

Among many others, there is a before_update event and an after_update event. The before_update event allows you to examine the record and make sure you do want to commit it to the table. You don't have to code this event, though. It's just there if you need it. The after_update event lets you prepare for the next record after you commit the previous record. Again you don't have to code this event, and it's relatively rarer than the before_update.

But there's no buttons being used. This is just the code reacting to events raised by the database system. If you look at a form, or a control on the form, and examine the properties of the object, you will see dozens of events for each object. This is what you can interact with. For any event you can add code to modify the database behaviour. No buttons at all. Just adding and editing records in tables according to the structure of the table, and it's relationship with other tables. We don't use most events, but we can if we need to.

So you look at a record on the screen. You edit a value, and move to another record. Your change is saved instantly. No code, no button. It's a multi user system, so all other users will see the result of that edit as soon as they use your edited record.

Rather different to excel. A record in an access table is really equivalent to a row (or maybe part of a row) in an excel spreadsheet. You don't have many buttons in an excel spreadsheet and you don't really need many buttons in an access database. However each access table row is completely standalone. You just can't use one access row by it's nextness to another access row, the way we do in excel. Rather all the rows in a table form a set, and we process the set as a whole, although the set we use can be a subset of the entire table.

The whole database paradigm can be quite hard to grasp, but it's so powerful when the data is properly structured.

Buttons may just provide information to the user, and probably won't do anything much. They may open another form for instance. Examining button code may be useful in some cases, but not all by any means.

Put it another way. If you have a database with certain tables, effectively you design a form to enable you to interact with each table. You don't need any code at all. The database will work like that. We tend to add code, defensively to prevent users doing things they shouldn't which could damage the data catastrophically. We even do this in our own databases. Rather then enter data directly into tables, we enter data into forms, and add code to interact with certain events to ensure that the data we enter is sensible. That's it pretty much. That's why the buttons won't do a lot.

As an example, we might set up a form to not allow data to be deleted by hitting the DEL key. But we might then add a button to allow the delete but in a controlled fashion. In some cases we permit the delete. In others not. Often we don't allow deletes at all. Just have a setting in the table for each record with a Yes/No field called "ignore". It gives the same result, but keeps a full audit trail that this record actually existed. And then you don't need the delete button at all.
 
Last edited:
Hey Ben, paste your code into ChatGPT, and ask the AI what it does. I pasted in this class...
Code:
Implements ICommand

Private obj_ As Object
Private name_ As String
Private sub_ As String
Private prp_ As String

'******************************************************** Aug 16 2024 *****
'
'   Method
'
'**************************************************************************
Function Load(Target As Object, ExecuteSubName As String, Optional Name As String, Optional CanExecutePropertyName As String) As cRelayCommand
    Set obj_ = Target
    sub_ = ExecuteSubName
    If Len(Name) Then
        name_ = Name
    Else
        name_ = sub_
    End If
    prp_ = CanExecutePropertyName
    Set Load = Me
End Function

'******************************************************** Aug 07 2024 *****
'
'   ICommand
'
'**************************************************************************
Private Property Get ICommand_Name() As String
    ICommand_Name = name_
End Property

Private Property Get ICommand_CanExecute() As Boolean
    If prp_ = "" Then
        ICommand_CanExecute = True
    Else
        ICommand_CanExecute = CallByName(obj_, prp_, VbGet)
    End If
End Property

Private Sub ICommand_Execute(Optional vInput As Variant)
    If IsMissing(vInput) Then
        CallByName obj_, sub_, VbMethod
    Else
        CallByName obj_, sub_, VbMethod, vInput
    End If
End Sub
... and got this as a response...
Code:
The class is a customizable implementation of the ICommand interface. It allows binding 
commands to UI elements in an MVVM pattern by specifying which methods to execute and under 
what conditions (using the prp_ property to check if the command can execute). The CallByName 
function is used to dynamically call methods and properties on the target object, providing 
flexibility in defining the command's behavior.
 
I agree with Markk. This is the most positive interaction I have had with Chatgpt etc.
I took a random click event and asked what it actually does;
Code:
What does this click event code actually do? 
Private Sub cmdUpdateRowSource_Click()
    On Error GoTo Error_Handler
    If (Me.txtSQL & "") = "" Then GoTo Error_Handler_Exit
    Select Case Me.txtSQL = tempvars!tvOrigSQL
        Case True
            MsgBox "Rowsource has not been changed. Nothing to do.", vbInformation + vbOKOnly, "  N O T H I N G  T O  D O  "
            Me.txtSQL = ""
            tempvars.Remove "tvOrigSQL"
            'GoTo Error_Handler_Exit
        Case Else
            If MsgBox("Updates to the control's rowsource have been made." & vbCrLf _
                & "Do you want to update cbo/listbox rowsource with this revised text?", vbQuestion + vbYesNo, " C O N F I R M ") = vbYes Then
                    If fcnUpdateRowSrc = True Then
                        MsgBox Me.lstCtlName & " Updated successfully.", vbOKOnly + vbInformation, " S U C C E S S "
                        Me.txtSQL = ""
                        Call FrameChoose_AfterUpdate
                    Else
                        MsgBox Me.lstCtlName & " Update failed.", vbOKOnly + vbCritical, " F A I L E D "
                End If
            End If
    End Select
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
Error_Handler:
    Select Case Err
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure subUpdateRowSource" & "."
    End Select
    Resume Error_Handler_Exit
    Resume
End Sub

And was pleasantly surprised with this result (including a Summary):

This click event code for the button cmdUpdateRowSource performs the following actions:

  1. Error Handling:

    • The code starts with an error handler that redirects to Error_Handler if an error occurs.
    • If the txtSQL text box is empty, it exits the subroutine.
  2. Comparison and Message Display:

    • It compares the value of txtSQL with a temporary variable tvOrigSQL.
    • If they are the same, it displays a message box indicating that the row source has not been changed and there’s nothing to do. It then clears txtSQL and removes the temporary variable tvOrigSQL.
  3. Update Confirmation:

    • If txtSQL is different from tvOrigSQL, it asks the user if they want to update the row source of a combo box or list box with the revised text.
    • If the user confirms (clicks “Yes”), it calls the function fcnUpdateRowSrc.
  4. Update Success or Failure:

    • If fcnUpdateRowSrc returns True, it displays a success message, clears txtSQL, and calls the FrameChoose_AfterUpdate subroutine.
    • If fcnUpdateRowSrc returns False, it displays a failure message.
  5. Exit and Error Handling:

    • The Error_Handler_Exit section ensures the subroutine exits gracefully.
    • The Error_Handler section displays an error message with the error number and description if an error occurs.

Summary:

  • The code checks if the SQL text has changed.
  • If unchanged, it informs the user and clears the text.
  • If changed, it asks for confirmation to update the row source.
  • Based on the user’s response and the success of the update function, it displays appropriate messages.
If you have any further questions or need more details, feel free to ask!
 
Yeah, I use the AI all the time, probably more than google, depending on what I am trying to solve. The AI can even write code, I mean, not that well, but I asked it:

Can you write me a VBA subroutine to find a WorkOrder in the fWorkOrder form's recordset using WorkOrderID?

It provided...
Code:
Sub FindWorkOrderByID(WorkOrderID As Long)
    Dim rst As DAO.Recordset
    
    ' Ensure the form is loaded and the recordset is available
    If Not (Forms!fWorkOrder Is Nothing) Then
        ' Get the form's recordset
        Set rst = Forms!fWorkOrder.RecordsetClone
        
        ' Find the record with the matching WorkOrderID
        rst.FindFirst "WorkOrderID = " & WorkOrderID
        
        ' Check if a match was found
        If Not rst.NoMatch Then
            ' Move the form's bookmark to the found record
            Forms!fWorkOrder.Bookmark = rst.Bookmark
            MsgBox "WorkOrder found and selected.", vbInformation
        Else
            MsgBox "WorkOrder with ID " & WorkOrderID & " not found.", vbExclamation
        End If
        
        ' Clean up
        rst.Close
        Set rst = Nothing
    Else
        MsgBox "fWorkOrder form is not loaded.", vbCritical
    End If
End Sub

Then I asked: Can you rewrite it so the code goes in fWorkOrder?

Code:
Private Sub FindWorkOrderByID(WorkOrderID As Long)
    Dim rst As DAO.Recordset
    
    ' Get the form's recordset
    Set rst = Me.RecordsetClone
    
    ' Find the record with the matching WorkOrderID
    rst.FindFirst "WorkOrderID = " & WorkOrderID
    
    ' Check if a match was found
    If Not rst.NoMatch Then
        ' Move the form's bookmark to the found record
        Me.Bookmark = rst.Bookmark
        MsgBox "WorkOrder found and selected.", vbInformation
    Else
        MsgBox "WorkOrder with ID " & WorkOrderID & " not found.", vbExclamation
    End If
    
    ' Clean up
    rst.Close
    Set rst = Nothing
End Sub
 
Jeez, people cannot be bothered to use Google, but perhaps, just perhaps, they might use that. :-)
If nothing else, it might give them a start.

Of course, we will then have the issue, that they do not understand any of it. :(
 
I know of at least to two accdb analyzers offered for free as downloads by Access developers. Crystal Long offers one. Colin Riddington offers one. There is also a commercial application from FMS, Inc.

Colin posts here as Isladogs, and I'm sure you can find his site easily with a search here or on the internet.

Crystal also posts here as striveforpeace, and the same is true for her site; search and it'll turn up.
Thank you.
 
Yeah, I use the AI all the time, probably more than google, depending on what I am trying to solve. The AI can even write code, I mean, not that well, but I asked it:

Can you write me a VBA subroutine to find a WorkOrder in the fWorkOrder form's recordset using WorkOrderID?

It provided...
Code:
Sub FindWorkOrderByID(WorkOrderID As Long)
    Dim rst As DAO.Recordset
   
    ' Ensure the form is loaded and the recordset is available
    If Not (Forms!fWorkOrder Is Nothing) Then
        ' Get the form's recordset
        Set rst = Forms!fWorkOrder.RecordsetClone
       
        ' Find the record with the matching WorkOrderID
        rst.FindFirst "WorkOrderID = " & WorkOrderID
       
        ' Check if a match was found
        If Not rst.NoMatch Then
            ' Move the form's bookmark to the found record
            Forms!fWorkOrder.Bookmark = rst.Bookmark
            MsgBox "WorkOrder found and selected.", vbInformation
        Else
            MsgBox "WorkOrder with ID " & WorkOrderID & " not found.", vbExclamation
        End If
       
        ' Clean up
        rst.Close
        Set rst = Nothing
    Else
        MsgBox "fWorkOrder form is not loaded.", vbCritical
    End If
End Sub

Then I asked: Can you rewrite it so the code goes in fWorkOrder?

Code:
Private Sub FindWorkOrderByID(WorkOrderID As Long)
    Dim rst As DAO.Recordset
   
    ' Get the form's recordset
    Set rst = Me.RecordsetClone
   
    ' Find the record with the matching WorkOrderID
    rst.FindFirst "WorkOrderID = " & WorkOrderID
   
    ' Check if a match was found
    If Not rst.NoMatch Then
        ' Move the form's bookmark to the found record
        Me.Bookmark = rst.Bookmark
        MsgBox "WorkOrder found and selected.", vbInformation
    Else
        MsgBox "WorkOrder with ID " & WorkOrderID & " not found.", vbExclamation
    End If
   
    ' Clean up
    rst.Close
    Set rst = Nothing
End Sub
Wow. Thanks!
 
My analyzer app that was mentioned by @GPGeorge is available at

Its a commercial app though there is a free evaluation version.

I also use CoPilot to explain what specific code procedures do.
However, to try & do that for every procedure in a large database is going to be a lengthy task
 

Users who are viewing this thread

Back
Top Bottom