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.
