Executing function on hundreds of databases

I want to embed a simple piece of VBA Code in Access 2007. I need to execute this code on hundreds of different Access DBs, so I don't want to manually paste the code into each and every DB. Is it possible to do this? Maybe by way of an add-in?

Thanks

Karl

EDIT

I want to execute the following VBA code:

DoCmd.DeleteObject acTable, "LastNum"
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=myDB;UID=User1;PWD=123;LANGUAGE=u s_english;" & "DATABASE=LastNumber", acTable, "LastNum", "LastNum"

How would I translate this into a VB addin?

The visual studio VB add-in template looks like this:

imports Extensibility
imports System.Runtime.InteropServices

<GuidAttribute("B61E2444-F46E-4591-A8BA-3D06A4E5D84C"), ProgIdAttribute("MyAddin1.Connect")> _
Public Class Connect

    Implements Extensibility.IDTExtensibility2

    Private applicationObject As Object
        Private addInInstance As Object

    Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
    End Sub

    Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
    End Sub

    Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
    End Sub

    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
    End Sub

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
        applicationObject = application
        addInInstance = addInInst

    End Sub

End Class

EDIT PART 2:

Ok, so I figured out I should do the following:

imports Extensibility
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Core
Imports Access = Microsoft.Office.Interop.Access



<GuidAttribute("B61E2444-F46E-4591-A8BA-3D06A4E5D84C"), ProgIdAttribute("MyAddin1.Connect")> _
Public Class Connect

    Implements Extensibility.IDTExtensibility2

    Private applicationObject As Access.Application
    Private addInInstance As Microsoft.Office.Core.COMAddIn


    Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
    End Sub

    Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
    End Sub

    Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
    End Sub

    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
    End Sub

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection

        applicationObject = CType(application, Access.Application)
        addInInstance = CType(addInInst, Microsoft.Office.Core.COMAddIn)

        ' This line enables VBA 开发者_运维问答to call back into this object.
        addInInstance.Object = Me


    End Sub

    Public Sub ChangeLink()

        applicationObject.DoCmd.DeleteObject(Access.AcObjectType.acTable, "LastPolNum")
        applicationObject.DoCmd.TransferDatabase(Access.AcDataTransferType.acLink, "ODBC Database", "ODBC;DSN=ZACANTDB02;UID=EDIPolicyNumber;PWD=museum123;LANGUAGE=u s_english;" & "DATABASE=EDIPolicyNumber", Access.AcObjectType.acTable, "LastPolnum", "LastPolNum")

    End Sub

End Class

Now what I want is to be able to execute ChangeLink() from Access. How do I do that?

Depending on what the code is for, there are (at least) two different ways to do this, without working at the VB Extensions level (which is where you'd copy and paste code, at least in VBA):

1. Make the code part of an add-in. It can then be loaded, and stay loaded and exposed, to any other application.

2. If the code is working on data only, consider writing a procedure that links and unlinks the table(s) in each mdb file that needs processing.

The advantage of #2 over #1 (and, indeed, over copying and pasting code) is that you can invoke your simple routine once, not hundreds of times.

EDIT:

There are two ways to invoke an add-in, depending on who is meant to invoke its functionality, and how.

1. Through the UI. Use this when you want an end user to be able to use it, especially practical for functionality you'll invoke often. How to: a. Office button > Access options > Add-ins tab b. If it's a .NET add-in (or any other non-Access add-in), choose COM Add-in from Manage dropdown. If it's Access, choose Access. Click go c. Click Add button in the form that pops up, browse to the add-in.

You will still need to provide some sort of UI to it: a commandbar, a ribbon button, a form, something. That's a separate discussion

1. Through code. Use this if you're just wanting to invoke it as part of some other VBA procedure. a. VBE window > Tools > References b. Browse button c. Change the dropdown in file of type if you're setting up an Access add-in.

You can then call the line of code directly, as if it were part of your current project. If you wish to be more explicit, you can also invoke it by library name: MyAddInName.ChangeLink

Seems to me that you don't need to run this code from the Access databases -- all you need to do is run it on all the databases.

To do that, you'd use DAO to open each database in turn, delete the table, then create the link. You'll have to do both of those steps with DAO (deleting from the TableDefs collection, and adding to it) rather than with DoCmd operations (which are unavailable through DAO).

Of course, if the databases are no accessible from a central location, you can't do this. But if that's the case, how would you alter the code or invoke an add-in?

Another way to use a library database is to use Application.Run:

Application.Run "\Server\PathToAddIn\MyLibrary.FixTables"

This assumes:

1. MyLibrary is an MDE/ACCDE.

2. in MyLibrary database is a subroutine or function called FixTables that runs the code you want to execute.

Indeed, with that method, you can actually use DoCmd, since the add-in is running within Access and in the context of the currently opened database.

However, note that some people seem to have difficulties executing add-ins in this fashion (I'm currently in a long discussion with somebody in another forum who can't seem to get it to work). I've been using this method for library databases for years and years and have had no problems (as long as you specify a full path), so I'm puzzled why the ones I'm discussing it with can't seem to make it work.