Saturday, November 20, 2010

How to manage accdb file without office 2007/2010

I have an accdb (access database file in Microsoft office 2007-2010 format) file but I do not have Microsoft Access 2007 or 2010. I need to manage (insert, select update, delete) accdb file and I have Microsoft Office 2003 including Access 2003.

Background

Situation is such that, I have an accdb (access database file in Microsoft office 2007-2010 format) file but I do not have Microsoft Access 2007 or 2010. I need to manage (insert, select update, delete) accdb file and I have Microsoft Office 2003 including Access 2003. Actually this situation is from one of my buyer who has given a requirement to develop a website within small scope using ASP.NET 4.0, MS-Access, etc. I used MS Access 2010 as a database. I knew that my buyer hasn’t MS Access 2010 after I had completed the development. And also not agree to buy incense for MS Access 2010 coz my buyer has assured me that the application will be developed in broader scope and MS SQL Server 2008 will be the database system. In this situation my buyer has two options to manage accdb either MS Excel 2003 or MS Access 2003. Regarding this situation I will show how do we mange accdb file without MS Access 2007/2010..

Pre-requisites
Let's Started
  • Open MS-Excel 2003
  • Select Data > Import External Data > Import Data
Select New Source from Select Data Source dialog

Figure 1:
Select Other/Advance and Next from Data Connection Wizard dialog.

Figure 2:

Select Microsoft Office 12.0 Access Database Engine OLE DB Provider from Data link Properties dialog.



Figure 3:

Select Next


Figure 4:
As a Data Source in Data Link Properties dialog put your accdb file with location.

Click Test Connection. If accdb file is ok then it will show dialog like bellow:

Figure 5:

Select OK and OK in Data Link Properties.

Select your table from Select Database and Table  dialog. In my case Country is the table.

Figurre 6:

Select Finish



Fgure 7:

 Like as above dialog will show. Click Cancel. It will create a data source (.odc) file in a default location (in my case, C:\Users\Mamun\Documents\My Data Sources, Please check while creating)  for your selected table. In my case (Default) Country.odc.

Minimize Excel. Browse the location where the (Default) Country.odc and Right Click on it. Select View in Browser:
Figure 8:
You can view all records at a time by selecting  maximum number of rows to show to All from right top side in the browser.

Now you can insert, update and delete records to underline table.
Figure 9:
Testing
We can test the process that we have already done.
  • Open excel  again
  • Data > Import External Data > Import Data > New Source > Other/Advance > Microsoft Office 12.0 Access Database Engine OLE DB Provider > Connection > Data Source > Select Database and Table > Select Table> Next > Finish > Open.

Figure 10:


Click  Edit Query.


Figure 11:

In Edit OLE DB Query dialog
  • Select Command Type to SQL
  • Command Text to Select * from Country. Country is my table name in your case it may different.
  • Click OK and then OK.

Figurre 12:

Result from excel sheet.

Let's try your own.

THANKS!