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??

 

[transistor1]

One detail to pay attention to though: if you have to distibute it as an .mde, make sure you change the entry in USysRegInfo from ".mdb" to ".mde", so Access can find your add-on on the user's computer. You can still access the tables in an .mde, so that should not be a problem.

 

[transistor1]

I didn't catch the 2013- oops! I saw March 30 thinking it was a few days ago. At least it still seems to be helpful to the OP & hopefully to other visitors.

 

[jasmith4]

Great -- I think I got it. But I've been told about having to rename MDA to MDE or vice-versa -- what's that about?

 

[transistor1]

Great -- I think I got it. But I've been told about having to rename MDA to MDE or vice-versa -- what's that about?

The ".mda" extension is technically supposed to be used by Access add-ins, but that is not enforced by Access. It doesn't hurt anything if you use that extension, but it doesn't help either. You can rename either an .mde or .mdb to .mda and either will work just fine.

There are 2 reasons that I can think of for using an .mda extension: (1) it helps your end user understand what this database is for, and (2) .mda files are one of the default filtered file types in the Add-In Manager.

 

[xenou]

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?

All very interesting but to my mind, you don't need to use the USysRegInfo table or anything like that. Just add a reference to the myTools database and it's code is available. I think if you compiled it it would not be viewable but would still be usable. However, I have found that if you reference an .mdb database the code is not editable (it's essentially read-only - a little tricky because you can make changes but they are not saved). To make changes, you have to open the myTools database itself directly.

 

[xenou]

Note that if you do reference another db I recommend you give it a proper project name:

In the VBE under Tools | Properties give the database a project name.

This will prevent naming conflicts that could occur if you are using a default name.

 

[jasmith4]

Please remember that the database I want to examine is read-only and belongs to someone else. So I can't add a reference to it, because that would be a change. However adding an add-in affects only my machine's Access environment. Is that right?

 

[xenou]

Referencing a database is not a change. I've never used an Access addin but I think both solutions do what you need here.

 

[jasmith4]

Isn't it? The bulk of what I do is Excel VBA, and it's definitely a change to an Excel macro-enabled file -- just examine ThisWorkbook.VBProject.References. In Word also it's ThisDocument.VBProject.References.

In fact I just tried creating a blank database, going to VBE and adding MyTools.MDB as a reference. Then I closed it and created another one, and the reference wasn't there. When I re-opened the first one, it was there.

 

[xenou]

Please remember that the database I want to examine is read-only and belongs to someone else. So I can't add a reference to it, because that would be a change

I may have been looking at this from the perspective of the other database. However, I don't see how it's different from using an addin. If you can reference MyTools.mdb and use it, then remove the reference, then you haven't changed the database. I guess the question is whether you could add a reference to a "read-only database". I suppose another question is whether you can install an addin for a "read only database" too.