Recording a macro/module (97SR2)

[charlotte]

It's appropriate right here, Chris. Access is enough different from other Office applications and even from VB to make this the best place for Access-specific questions and answers.

Unfortunately, your play time is over. There is *no* macro recorder in Access. You have to learn to do it the hard way. There are a few wizards for things like command buttons, but for the most part you write it yourself. On the other hand, we never have discussions about the relative merits of selection versus range. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Would you like to discuss ADO versus DAO, though? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

One sort of entry point for you would be to create a macro (in the Access sense of the word), which is a container for a list of actions you can take. They roughly equate to selections from menus, although writing good macros is just about as hard as writing good code and a lot more frustrating. Once you have a macro saved, you can use File-->Save As to save the macro as a module, which will convert it into what you're accustomed to calling a "macro". Unfortunately, Access is still using older code in its wizards and converters, so it isn't great code by any means. For example, here's an AutoKeys routine converted from the macro:

<pre>Function AutoKeys__B()
On Error GoTo AutoKeys__B_Err

DoCmd.Echo True, ""
DoCmd.Hourglass True
DoCmd.SetWarnings True


AutoKeys__B_Exit:
Exit Function

AutoKeys__B_Err:
MsgBox Error$
Resume AutoKeys__B_Exit

End Function</pre>


Not pretty, and the converter has replaced the caret in the macro name to an underscore since the caret is not a legal character in code (it is legal in an AutoKeys macro name where it represents the ctrl key).

Your best bet is to get a good book on Access if you want to learn Access, because it doesn't share much in the way of objects with any other Office app. Even the forms and controls are different, since Access doesn't use the Microsoft forms that the rest of Office uses, and the properties and methods of the forms and controls are different. XP seems to look a bit more like the rest of Office in some respects, but Access is still going its own way.

[chrisgreaves]

> It's appropriate right here

OK. I'll continue with major observations in this thread. Maybe start a second thread in VB/VBA for when the going gets rough.



>Would you like to discuss ADO versus DAO, though?

Yes. (no laugh!). I started to think about moving away from DAO six months ago in Word97/VBA, but then saw an article that said with the JET engine DAO was faster (like I care!). I would like to learn how to migrate from DAO to other engines. One day someone is going to ask me to switch their application from DAO to, say, SQL, and I won't know what to do.

I had considered the switch in Word. What are the implications in Access. I hadn't thought about it. Of course, since I've written only THIS MUCH code in Access, I haven't got to writing DAO yet. I've used Access only as a desktop application.

I shall go search for DAO vs. ADO.



> you can use File-->Save As to save the macro as a module

I'll try this scheme first. I need enough to get me into the Help files, and to get a sense of common objects. In WordVBA, an early useful object for me was ActiveDocument. Typing a period after that opened up a slew of dandy things I could do with a document.



>get a good book on Access

Wot?! Spend money?! HAH! OK. mebbe I'll check out the $20 book bins next time I'm at the mall.

[chrisgreaves]

> Access is enough different from other Office applications and even from VB

Further thoughts: is this true in Office XP? Has MSoft managed to make these VBA arenas move towards each other? I have a beta of OfficeXP, so if the VBA is way better there, I'd be better off learning in AccessXP, and transferring my knowledge to Access97 or whatever?

[charlotte]

I wasn't joking about the book, Chris. Access is a far more complicated app to design in because its main object isn't a document, it's a database. Plus, it is actually two applications, Jet and Access, and they aren't really the same thing.

You need to learn not only how to write VBA code for Access objects but also table design (nothing like Word or Excel), normalization, query design, a whole new way to populate listboxes and comboboxes (no AddItem, sorry), etc. It's very easy to build bad applications in Access, people do it all the time. Building a good Access application is just as demanding as building a good app in any other development language. You can make a lot of things look good with a flashy user interface, but bad design will bite you down the road when you absolutely *must* do something that your design renders impossible or impractical.

There are white papers on the Microsoft site that discuss moving from DAO to ADO. I's suggest you start there and then search through this forum for discussions on ADO and DAO.

There aren't a lot of common object between Word and Access, although there are common methods and properties for objects. In Access, a document is a table, report, macro, module or query. There are controls that have the same names, but they aren't the same controls and you have to work with them differently. Forms are entirely different as well.

[charlotte]

Both XP and 2000 are based on VB6. There are very few differences between Access XP and Access 2000 and most of those result from the broader XML support in Office XP and in the expansion of pivot table capabilities in Access XP over what 2000 supported. It isn't the VBA that's the issue, although with difference objects the VBA is different too. The real difference is that Access is inherently data driven and so everything is built around the idea that you will use Access to manipulate data of some kind. Interface is a separate concern because it addresses *how* you manipulate data. Little if any of your Word stuff will mean anything in Access because it doesn't apply to the object model. I've spent over 9 years learning Access's ins and outs, and I'm still learning new stuff daily.

Oh, forget about learning VBA in XP and transferring it to Access 97. There are any number of new functions and capabilities that came in with Access 2000 that don't exist in 97.

[chrisgreaves]

> save the macro as a module

Rooty-toot-toot-toot!!

I have a VBA module which imports my comma-delimited file to the table.

THAT was easy.


Now to work out how to build import specifications on the fly .....

[rory]

Chris,
Having read your posts in VB/VBA, I know you like to get down into the 'bowels' of the program - how about writing a macro recorder? If you can, I think I know of a company that'd buy it off you..... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[chrisgreaves]

>how about writing a macro recorder?

OK.

The essence must be a module that traps and re-issues keystrokes, right?

[chrisgreaves]

(Charlotte, please transfer this to vb/vba if appropriate).

How do I record a set of commands in Access?

I've been recording and writing VBA module code in Word97 for 3 years now.

Time to start work in Access97.

To my abject dismay, I don't seem to be able to follow the learning path that I chose with Word, to wit, to record a set of commands, and then inspect the recorded code, use the F1 key for help, and move on from there.


My first task is to write some code to automate the process of importing a text file, appending it to an existing table. The table is in place, the text file is good. I'd like to know how to go about finding out how to do it from within Access.

I know to post a question here or in VB/VBA, but I'd like to start learning some basics before I flood the forums with "How Do I?" questions.

Hope this makes sense .....

[rory]

Hmm, I think that might be a little bit of a simplification? <img src=/S/smile.gif border=0 alt=smile width=15 height=15> If you did that presumably you'd just end up with a lot of SendKeys commands. I'm actually having trouble thinking of many things you'd want a macro recorder for - most of what I do in code, I can't do in the normal application window!

[charlotte]

You'd also have the problem of *how* to trap the keystrokes and mouse-clicks because you need an object to trap them from. In Access, you don't have a constantly opened document to use like that, although you can trap KeyDown and Mouse events in forms and controls, and not all Access objects have those exposed events.

[chrisgreaves]

> trouble thinking of many things you'd want a macro recorder for - most of what I do in code, I can't do in the normal application window!


Now just hang on a minute.

I might be from the Word97/VBA fold, but .....

main use of a macro recorder in WordVBA is to get started with VBA. The recorded/generated code isn't terribly good, but it does give one a start into writing VBA.

For example, rather than explore the Help files for mail merge, I record a mail merge then go back andd see what got dredged up out of the sewer.



>a lot of SendKeys commands

When I'm delivering training, i tell people "A macro is a recorded set of keystrokes", also "If it's boring and repetitive, write a macro". At that level i am trying to get people to transcribe their keystrokes to a mini-program, so that they don't spend the rest of the day typing. As you are probably awrer I'm not the worlds greatest typist, so anytghing that vuts down on my typing is a goood thinf!

[chrisgreaves]

>you need an object to trap them from.

I've a sneaking suspicion that this would be true too in Word, were it not for the omnipresent Normal.dot.


Given that, with the exception of the command to open a databse, almost everything i do in Access will be done in the presence of a database, i should assume that i always have that present.

Truth is that I wasn't thinking necessarily of doing it IN Access as much as having a guardian angel floating above, an EXE maybe, that did trap the keystrokes and somehow detect what they were doing. That suggest that I'd have a LOT more knowledge about Access commands than now I do, and the generated code would almost certainly be worse than that of Word.

[chrisgreaves]

>I'm actually having trouble thinking of many things you'd want a macro recorder for

<A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=90940&page=0&view =collapsed&sb=5&o=0&fpart=&vc=>Here's</A> an example of how useful it can be in Word.