How to Use Macros in MS Access

[@zoomingby]

Sean, I can't tell you how much you've helped over the years. Truly awesome that you do this for the community. You've undoubtedly made people's lives better, easier, and more productive. Thank you.

[@seanmackenziedataengineering]

That's great to hear, thanks for the feedback! Truly appreciated

[@manishkashikar9154]

One of the best teachers of access in the world, Thank you sir, I love to watch your videos.

[@seanmackenziedataengineering]

Wow, thanks!

[@zoomingby]

Sean, you mentioned making a new table. What are a few situations where it's better to make a new table than to create a query that produces the same recordset?

[@seanmackenziedataengineering]

In some cases where you need to reuse a query that is particularly slow, it can be much faster to create a table one time, then join that query output into other queries for a faster result. You can also have some kind of output that is overly custom and difficult to represent with SQL. In those cases, you can write a table then run a procedure over it and update the output in a very specific way, including doing things like adding rows before the result is opened in a report, for example. A third use case is when you have a very slow networking environment and pulling data into a temporary table will have the secondary benefit of making a table in the local user's "front end" file, greatly improving performance for things like combo boxes/queries etc. (this is similar to the first example) Great question!

[@zoomingby]

@@seanmackenziedataengineering Ah i See, THANK YOU!!! Great examples. So helpful. Appreciate your time on this!

[@jim27105]

Very Helpful. I went straight into VBA without learning Macros at all. Curious how you would add the # of records to the batch processed message. In other words, display “227 records processed”. Is that easily accomplished?

[@seanmackenziedataengineering]

I do remember this exact problem when I was doing macro building. If you know which table will receive the records (in this case 5M_LAND_AREA), you can use a DCount in your messagebox expression; ie: "Process complete! " & DCount(" & Chr(34) & "ID" & Chr(34) & ", " & Chr(34) & "5M_LAND_AREA" & Chr(34)) If you want to know why I used Chr(34): kzbin.info/www/bejne/r16UfJ6mlJiXedk Alternatively, you can create a list or combobox with the RecordSource set to 5M_LAND_AREA, then you could use: "Process complete! " & lbxChanged.RecordCount though, if lbxChanged is empty, it will return -1, so you can watch for that.

[@cletuskasenga8434]

So helpful. Please help! I've set up primarily admission, student and payment tables for a school database. What I want is to use the admission form, through vba to write admission details to both the payment and student tables when you click the admission confirmation button. All I've managed to go is just write details to the student table. Do you have any ready made video about this or how would you help? Thanking you in anticipation.

[@seanmackenziedataengineering]

You bet. For this use case, you can try a data macro: kzbin.info/www/bejne/rabceqCroceSq5Y and to use a data macro to write to other tables: kzbin.info/www/bejne/jXq6o4CsoNhkjdE You can change these to suit your context. Good luck!

[@kailynnbronken2078]

this helped a lot

[@seanmackenziedataengineering]

Glad it helped!

[@dalskiBo]

Thanks Sean

[@seanmackenziedataengineering]

Welcome!

[@JemimahNjokiMuthoni]

nice

[@seanmackenziedataengineering]

Thanks!

[@gerfer6261]

Really good 🪢 Piggyback macros are that something I takeaway from this ( I think this will make life easier or getting into black hole)

[@seanmackenziedataengineering]

Oh yes 😀 before long, you'll be making super long macros to do collections of manual tasks that you were doing before. It really is incredible what you can accomplish!