Automating deployment for MS Access Application

Mani Murthy

4.20/5 (2 votes)

Jul 1, 2016

CPOL

3 min read

15165

Using .Net application to automate the 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

I have made use of 2 MS Access database files which will act as my application. Database2.accdb is the production copy and Database1.accdb file is the development copy which has got the required modified components. I am using the .Net application to automate the transfer process.

The main method that is being used for copying objects is

DoCmd.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
The help link https://msdn.microsoft.com/en-us/library/office/ff196455.aspx

Now, lets get into the details. First thing that we need to do is to create a project in Visual Studio. I have created a console project using VS2015 community edition. To use the MS-Access objects we need to first add the reference of the Access COM objects in our project. This can be done by right clicking on Reference in the solution explorer and then click on Add Reference which should pop up the Reference Manager dialog box. Click on COM which should be available on the left hand side of the dialog box. Search for Microsoft Access 14.0 Object Library (please note that I have 14.0 but this version number may vary). Below Screen shot shows how the Reference Manager looks.

Once the reference is added we can go next step that is to open the source file i.e.. Database1.accdb file within the application. Below two lines shows how it is done.

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

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”.

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

The above link explains in details about the TransferDatabase method, but I will quickly explain the parameters used above.

First Parameter is to specify if we wish to export, import or just link the desired object to the destination DB.

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.