How to get my VBE Subs to show in Run Macro box ??

[lifeson99]

I am new to doing this with Access. But with Excel I would go to the VBE, type in "Sub MySub" for example, add in the code, then go back to Excel and select Tools/Macros . . . and "MySub" is listed there for running.
OK, so I did the same thing with Access so that I can run some code, and again I have :

Sub Mysub
' code here
End Sub

Question 1) when I go back to Access and select Tools/Macros / Run Macro...

A dropdown box pops up and the drop-down is empty.

Question 2) I also see that you can go to "Insert/Macro" and Access has its own Macro editor where you select commands from a drop-down box. This method looks much more limiting and not very intuitive on how to create macos. How does that correspond with th Macros that I wrote in the Access VB Editor ? Does Access actually support two different versions of macros ?

Any help on these 2 questions ? Greatly appreciated . . .

ls

 

[boblarson]

A macro in Access is different than a macro in Excel. In Excel a macro is actually VBA code and it Access a macro is a separate type of object. For Access you want to use RunCode if you are using a macro to run VBA code. RunMacro is just for running other macros.

And for the procedures to show up in the list in the RunCode arguments, you need to

1. make sure your procedures are FUNCTIONS and not SUBS. And they need to either be put in like

Public Function xxxxx()

or

Function xxxxx()

but not

Private Function

as private functions will not show up in the list.

2. They need to be in Standard modules, not form or report modules (I believe, if I remember correctly).

 

[lifeson99]

That did not work. I changed it to a Public Functions and save the file to be sure. But when I went to Run Macro, there are still no macros listed. Here is my code:

Public Function AZ_Queries()
With DoCmd
.SetWarnings False
.OpenQuery "Make A"
.OpenQuery "Make B"
.OpenQuery "Make C"
.SetWarnings True
End With
End Function

It just run those 3 Queries sequentially. It works fine when I run it from the VBE but it does not show up in the Access "Run Macros" list. There must be another way . . .

 

[boblarson]

That did not work. I changed it to a Public Functions and save the file to be sure. But when I went to Run Macro, there are still no macros listed. Here is my code:

Public Function AZ_Queries()
With DoCmd
.SetWarnings False
.OpenQuery "Make A"
.OpenQuery "Make B"
.OpenQuery "Make C"
.SetWarnings True
End With
End Function

It just run those 3 Queries sequentially. It works fine when I run it from the VBE but it does not show up in the Access "Run Macros" list. There must be another way . . .

Read my post again. I said, it is NOT RunMacro. It would be RunCode NOT RunMacro. Does that help?

 

[Banana]

Are you actually referring to the menu?

Tools -> Macros -> Run Macros?

 

[lifeson99]

Yes I am - exactly. Under the Tools/Macros menu selection.

Bob said to use RunCode not RunMacro. But I do not know what either of those terms means, so I followed his two-step instructions:

1. make sure your procedures are FUNCTIONS and not SUBS.
I changed it to a Public Function

2. They need to be in Standard modules, not form or report modules (I believe, if I remember correctly).
It was already in a standard Module

But it is not working. How do you "use RunCode" ? I assume that is the problem ? As I said I am new to Access Macros - although I have used Access for a couple years, never needed to run a macro until now.

 

[Banana]

Well, Bob was talking about about the RunCode from the Macro design view, not from Tools -> Macros -> Run Macro.

FWIW, I've never ever used that menu item, and though public function *should* be a part of macro listing (e.g. it will show up when you select from a event handler for a control), I wouldn't be surprised if the Run Macro menu item only works for actual Macros and not just Macros & public function.

Since you're set on using macros, you would probably want to create a new macro then use the RunCode as Bob suggested.

 

[boblarson]

I wouldn't be surprised if the Run Macro menu item only works for actual Macros and not just Macros & public function.

Yep, RunMacro is only for MACROS not VBA code. VBA code is not a macro (at least not in Access). A Macro is a specific thing in Access and is unlike Excel. So do not think about what Excel does because it does not apply in Access when it comes to macros. A Macro in Access uses a special macro builder which lets you specify actions and arguments. It has nothing to do with VBA code.

 

[Banana]

Right. Access's Macros is weird because in rest of Office Suite, Macro = VBA, but that's not true in Access, and even so it's not totally consistent (e.g. you can see public functions in list of macros from event handlers), so I can see why it'd be confusing.

 

[lifeson99]

OIC - yes well the only reason for this was that I though I could avoid having to even go into the VBE editor to run the Subroutines and instead run them from the Access GUI.

You know, if you end up with a lot of Subs and/or Functions it is difficult to poke around the VBE and find them then run them. In Excel I either use the Run Macro menu item or I add image buttons right on the Sheet to run them by clicking on the buttons. Probably is a way in Access but it is not made simple, I'll tell ya' that !!

 

[Banana]

Well, personally, I think there's a good reason why I never use that menu item. It's mainly because I'd already had given my users a button on a form or a custom toolbar button that will execute the custom code (and macros as well, though I never use macros). Thus, there's no need to go Tools -> Macros -> Run Macros then selecting macros. That would be even more confusing to my users, I'd think.

 

[boblarson]

You know, if you end up with a lot of Subs and/or Functions it is difficult to poke around the VBE and find them then run them.

Not for me as I don't have subs and functions normally that run outside of my user interface. In other words, if I have something to run it is on my form or report. I don't think I've ever had a function or sub where I needed to have a user just run it, and that's been almost 13 years.

In Excel I either use the Run Macro menu item or I add image buttons right on the Sheet to run them by clicking on the buttons.

Yes, Excel is a bit different but in Access you normally use forms for user input, execution, etc. It IS the user interface.

Probably is a way in Access but it is not made simple, I'll tell ya' that !!

It is simple but it isn't done the same way you're used to in Excel. Remember Access is NOT Excel. It is a completely different program with its own specific things it accomplishes. Just having functions or subs to run, makes me wonder if you are using Access correctly in the context of what it is - a Relational Database program.

 

[lifeson99]

Yes, well it is a big time-saver for me. Obviously there are many ways to do this.
I am taking a slow-to-query gigantic DB with 2.6 million records and speeding it up dramatically by breaking the table into 26 smaller tables. One table per letter of the alphabet (all the States that start with A are placed into the table "A", etc).

Each time they update the big file I run 26 queries to create the tables, so I just stacked them up in Visual Basic, and I run it whenever the master DB is updated. Then I query that in Excel macro that loops and goes through thousands of customer sites - each site. this Excel macro pull data from this Access DB and presents it to the worker to see. It was slow as moolasses previously but with the 26 smaller tables it run very quickly:

Sub AZ_Queries()
With DoCmd
.SetWarnings False
.OpenQuery "Make A"
.OpenQuery "Make B"
.OpenQuery "Make C"
.OpenQuery "Make D"
.OpenQuery "Make E"
.OpenQuery "Make F"
.OpenQuery "Make G"
.OpenQuery "Make H"
.OpenQuery "Make I"
.OpenQuery "Make J"
.OpenQuery "Make K"
.OpenQuery "Make L"
.OpenQuery "Make M"
.OpenQuery "Make N"
.OpenQuery "Make O"
.OpenQuery "Make P"
.OpenQuery "Make Q"
.OpenQuery "Make R"
.OpenQuery "Make S"
.OpenQuery "Make T"
.OpenQuery "Make U"
.OpenQuery "Make V"
.OpenQuery "Make W"
.OpenQuery "Make X"
.OpenQuery "Make Y"
.OpenQuery "Make Z"
.SetWarnings True
End With
End Sub

 

[Banana]

Well, it's just my opinion, but there are few concerns.

1) I actually seldom use MakeTable query. It's usually faster and more robust to run a delete query to flush out the table and reload it with updated data. This also keeps bloat from running out of control.

2) I know you've said that the table with 2.6 million rows run slowly, but I'm not so sure that it makes sense to denormalize into several tables. I have a sample database that contains 4 millions rows of personnel data and it's quite zippy. This is only because all important columns are indexed.

Thought you may want to consider the possibility of indexing & structure before doing anything so drastic.

 

[boblarson]

I agree with Banana here. The structure seems a little - suspect to me.

 

[dcb]

Hi Lifeson

Been watching this and having worked with a fair amount of excel I understand why you would think this way. In Excel you have a tendency to rely on Alt+F8 as a method to quickly run your adhoc code – in fact its often more tedious to use buttons etc as the GUI is what it is.

In Access you create your GUI as forms – Very easy to get started – No really give it a try
Most of us spend huge amounts of time making sure the user can’t touch any menus, standard toolbars etc
In a form that looks exactly like we want it, we would have a button that we click which would then run our code – in a controlled setting – the idea of a user being allowed to run a macro whenever they felt like it, would have most access developers on the floor twitching

Run Macro Question
If you want to use Forms:
Create a blank form
In Design View
Add a Button (cancel the wizard)
Right Click on the button
Build Event (Opens the VBA Editor and creates the on click event)
Just put your Function Name between the Name and End Sub

Private Sub Command0_Click()
      AZ_Queries
  End Sub

Now return to your form and change it to form view
Click the button

Else if you really don’t want to use the access front end here’s the VBE to pull the same result from Excel
Close the DB before trying to run this
Add referance to Microsoft Access 12.0 Object Lib in Excel (Think you are using 2007 by the way you described the macro box)

Sub AccessTest1()
        Dim A As Object
        Set A = CreateObject("Access.Application")
        A.Visible = False
        A.OpenCurrentDatabase ("c:\David\Database3.accdb") ‘’Insert your path here
        A.Run "Test"
        A.Quit
        Set A = Nothing
   End Sub

As for the DB design – listen to the comments from the others – you are going to end up with a massive amount of tables that you will have to clean up



DCB