How to make a 2003 add-in in Access 2010?

[jasmith4]

I use Access 2010, but many clients still have 2003, so I have to deal with 2003-format files all the way.

I've developed MyTools.MDB with nothing but macros and modules containing various tools like exporting all objects to text files. I want to make it an add-in so I can open any MDB or ACCDB and run these tools on it. But I'm finding it nearly impossible, and there's absolutely no comprehensible help on anything anywhere.

I start with MyTools.MDB open, I go to File/Save and Publish, click Make MDE and Save As, and I save it as MyTools.MDE. Then I close it, open some other database, click Database Tools / Add-in Manager, cick Add New and point to MyTools.MDE. It tells me something about a missing "USysRegInfo" table. I search the Help for that string, both Access Help and Developer Reference, and naturally I get NO results -- thanks, Microsoft!

So I search the Web for "USysRegInfo" and I get articles about menu add-ins, form and report wizards, table and query wizards, control wizards, property wizards, and OLE custom control wizards. But all I want to do is execute macros and public functions within modules -- what do I do? Couldn't Microsoft develop some wizard which leads you through creating and populating that table??

 

[xenou]

See if it works instead this way: just go to Tools | References in the visual basic editor and set a reference to the database (instead of checking a box you will need to browse out to the file and "select" it instead).

ξ

 

[jasmith4]

Yep -- I just remembered that. But here's one for you: let's say I have a read-only MDB file. I open it, then do the Tools/Refs thing in the VBE. Now, from the Access UI, how do I run any macro or public sub or function?

 

[xenou]

I don't usually use Access macros so I can't speak to that. Subs or functions would work the same as they normally do.
ξ

Edit:
I'd probably just go with a reference but I did just find an article that explains the addin situation pretty well (I think):
http://www.databasedev.co.uk/access-add-ins.html

 

[transistor1]

It's really simple to create an add-in for Access. You have to create a USysRegInfo table in the database that you want to use as an add-in.

You can use this table as a template for your USysRegInfo table.

Replace the =FunctionToCall() with the function you want to be called when the user selects your add in from the Add-Ins menu.

Once you've done that, close the add-in database, and open any other database in Access (besides your add-in).

Under Database Tools->Add Ins, choose "Add In Manager". Then click "Add New," and browse to your add-in database.

Now you should have a new entry in your add-ins menu that should match the "&Menu Item Name" from your USysRegInfo table.

 

[jasmith4]

OK -- I've done all that. It's a clumsy process that includes having to rename your MDA file to MDE (in 2003).

Now here's the next problem: I open another database -- say it's read-only, and I can't touch it. I pull in my add-in with the Manager. I run a macro from my add-in via the Manager's newly amended menu. There's an error. I'd like to debug it. I go to the VBE, I see both databases' project entries, but the one for my add-in is unviewable! I didn't password-protect it or anything.

Come on now, neither Excel nor Word makes things this difficult. I must be missing something...

 

[xenou]

Have you really been trying to get this working for the last year? I would really suggest you just add a reference to your myTools.mdb. It is the simplest way and only takes a second.

 

[jasmith4]

Not constantly, by a long shot.

OK, so I keep my tools-code database a normal MDB for now, open the read-only database, go to VBE, add MyTools.MDB to Tools/References, and THEN its code will be viewable and debuggable? So only when it's perfect I release it -- as MDE or MDA? -- and pull it in via the Add-In Manager?

 

[transistor1]

You don't need to use an .mda or .mde; you can just reference the .mdb file in your USysRegInfo.

An .mde file is a compiled project -- you can't debug one because the source code has been compiled. Renaming the .mdb to .mda should be possible (I've not tried it) but it is not necessary. If you need something you can debug, just use the .mdb.

 

[transistor1]

Yes, if you need to release it to people for whom you don't want to view the code, you can create an .mde. As I said above, the .mde is compiled and can't be debugged.