Automating deployment for MS Access Application

Introduction

MS-Access is one of the favorite tools for developing applications for small user base because one Database file can contain all that is required for any application (Data tables, Forms, Queries,Reports, etc...) the cost of the maintenance is also very low. .

Like any other application, MS-Access applications also has to undergo changes. In normal scenario,for making the changes available in the production we would have to copy all the modified objects (forms, queries, reports, etc) from the development copy to the production. This process can be strenuous, error prone and even time consuming. This article explains how we can automate the deployment process which can make life easier and deployment faster.

Using the code

<code>    Application app = new Microsoft.Office.Interop.Access.Application();
    app.OpenCurrentDatabase(@"C:\TransferDemo\Database1.accdb", false);</code>

That is it! We are done. Once the source database is opened then we just have to use the DoCmd.TransferDatabase() method to transfer the required objects. For this article I have used just one form named “frm_Registration”.

<code>app.DoCmd.TransferDatabase(AcDataTransferType.acExport, "Microsoft Access", @"C:\TransferDemo\Database2.accdb", AcObjectType.acForm, "frm_Registration", "frm_Registration");</code>

Second, specifies which is the target application. In this case its MS Access hence that is mentioned.

Third parameter is the path of our destination MS Access DB.

Forth parameter specifies what type of object is being transferred to the destination, in our case since we are copying only the form that is mentioned. Access allows us to copy other objects such as Queries, Reports, Tables etc...

Next two parameters are the name of the object that we want to copy and the name that we want it to be in the destination file. The last two parameters are not used in this article. 

After the transfer is done, as a good practice the open database file has to be closed. Below line of code does that.

app.CloseCurrentDatabase();

That's it! we have a code that will copy the required object from one .accdb file to the other. The good thing here is that we don't have to worry even if the object that is being copied already exists in the destination DB, TransferDatabase method will overwrite it on the destination DB.

Conclusion

As an in input to this application, all that is needed is a the list of objects to be transferred along with its type. There are multiple ways to achieve this, creating a text file, excel sheet or even create a UI which will list of all the objects in the source DB file and then provide a feature to select the objects that needs to be transferred.