Access 97 - Convert Macro to VBA

[Xpert]

I'm trying to find VBA code to convert Macros to Visual Basic Modules.

I've found that it can be done in versions of Access higher than 97 (see below), but the some of objects used in Access 2000 or higher are not found in Access 97. I've bolded the ones that I can't find in 97...

Sub AllMacros()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllMacros collection.
For Each obj In dbs.AllMacros
DoCmd.SelectObject acMacro, obj.Name, True
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
Next obj
Set dbs = Nothing
End Sub

Can anyone help?

 

[SydneyGeek]

VBA changed when 2000 came in; it was based on VB5 for 97, and VB6 thereafter. Because of that, there are definitely some things that can't be done as easily in 97.

How many macros are you converting? Because I guess the nasty option is to do it by hand.

Denis

 

[Xpert]

There are about 60 or 70 macros.

Not really something I want to do manually.

Is there some kind of work-around? My thinking is that anything you can point and click to do should have the capacity to be translated into code...

RIIIIIIIIIGHT?????

 

[SydneyGeek]

The manual method is this...

Select the macro
File > Save As
Pick Module as the option

Still point and click - ish, but you need to run through that process for each macro. Unfortunately I no longer have 97 installed, so I can't go digging.

Denis

 

[SydneyGeek]

Just had a thought.

Did you import that code from elsewhere, or save the DB as 97 from a 2000 version? If so, check for missing references and see if that helps. Tools > References in any code module -- if missing refs are found, deselect the reference and go find the corresponding one(s) in the list.

Denis

 

[SydneyGeek]

Not sure if this helps...

http://support.microsoft.com/kb/163842

Denis

 

[Xpert]

Hmmm... Just read the Microsoft Support article and I'm not sure if it will work.

I'll try it when I'm at work tomorrow and let you know what happens.

Keeping my fingers crossed!

 

[Xpert]

Ok, so I tried the MS Support thing and it worked... but not the way I want it to. What I need to know is how to write VBA code to do exactly what that article tells you to do. I don't want to have to manually convert 50 macros.

Doesn't look like it's possible in Access 97.

 

[SydneyGeek]

Yeah, I went for a dig around too. The Access 97 Developer's Handbook (the only 97-specific book still on my shelf) wasn't helpful. Short of trying to find a reference for the whole object model, and digging your way through it to see if another object or property comes up, you are probably stuck with the manual approach.

Sorry I couldn't help more

Denis