How to export macros' commands as text?

[jasmith4]

I'm trying to devise a way to export all code in an Access database as text such that I can use CVS meaningfully when I make changes. Not just VBA code in forms, reports and modules, but also the connection strings and SQL commands for queries, and the list of commands in all macros.

I've done all the VBA and queries, but I can't figure out how to get to macros' commands at all. I've examined all containers and documents in CurrentProject, all the Access objects in CurrentData, everything, and I can't find anything. All I can do is save each macro as a module and then use the module-VBA system I have.

I've seen the Documentor, but all it does is put out a report (I suppose I could send that report to a generic text-file printer driver). But I want to be able to import everything from the text files too, and again, I can do that to remake all queries and the VBA behind all forms, reports and modules. But I couldn't do that with macros converted to modules.

So how would you use VBA to list out all macros' commands?

 

[Norie]

Have you tried converting the macros to VBA?

Then you can use the same process you have for exporting the VBA you have now.

 

[SydneyGeek]

I think you'll find that macros are part of Scripts.
Are you using SaveAsText for the export?

Denis

 

[jasmith4]

SydneyGeek: I found the container Scripts with a Document object for each macro. But that Document doesn't have any list of commands, only the macro's name and a few properties.

Norie: Yes, I already said that I've found how to convert macros to VBA modules using Save As, and yes, I can use my VBA methodology. But I want a two-way system: export to text AND import from text. Given that I could parse the code in a module that used to be a macro, how would I re-create a macro rather than a module?

 

[SydneyGeek]

Look up the SaveAsText and LoadFromText methods. You will need to Google.

SaveAsText lets you create text versions of all your database objects (except tables -- but you can use XML for that). LoadFromText imports them into a database, with all the controls in the correct place (code included). It's the mechanism behind Access templates.

EDIT: First item in a search for Access SaveAsText: http://www.access-programmers.co.uk/forums/archive/index.php/t-99179.html

Denis

 

[jasmith4]

Wow, SydneyGeek/Denis, that really helped! Actually I opened the Object Browser, right-clicked it in the middle, and chose Show Hidden Members to find that SaveAsText and LoadFromText are hidden members of the Application object.

But there's one thing SaveAsText doesn't work on: relationships. There are two containers, "SysRel" and "Relationships", so I'll start there, and maybe I'll have to write my own routines to make and read text files with relationships.

Thanks again!

Andy

 

[SydneyGeek]

You could also use a query based on the MSysRelationships table to give you the bulk of the information. Not sure about reading it back though -- a relationships report could be a useful way to snapshot the schema.

Denis

 

[SydneyGeek]

Another option:

This page from Allen Browne lets you create an enhanced relationship report that includes data types and field sizes. You can probably dig through the code to work out how to extract the relationhsip data

Denis

 

[jasmith4]

SydneyGeek: I'm finding now that SaveAsText doesn't work on tables either. The link you gave shows code that dumps the *contents* of a table into a text file, and that's useful, but what I want is each table's columns, indices, and all properties of each.

Whenever I try to execute...

Application.SaveAsText acTable, "MyTable", "C:\MyTable.txt"

...it complains that the acTable argument is missing or invalid. Funny, though, the Documentor works just fine on tables.

Any suggestions?

 

[SydneyGeek]

Go to this page: http://www.datawright.com.au/access_resources/access.htm
Run through the links under Documenting Your Database.

Denis