Monday, May 7, 2012

Access Recovery Using MS Query to Fix Damaged Database Tables

Microsoft Office includes an excellent tool, known as MS Query, to help you import data to MS Office applications from the external sources. This tool is particularly helpful for Microsoft Access to bring data to Access tables from external sources and databases. The advanced utility helps you perform Access database recovery in certain situations. Process of retrieving corrupted tables and other database objects using MS Query is described in this article.

You should keep the following two things in mind before starting the Access repair process using MS Query:
  • Do not forget to test the recovered database, prior to return it in production environment.
  • Never remove the damaged or corrupted Access database, until you confirm that recover is successful.
Following steps describe the Access database repair process using MS Query tool:
  • Create a duplicate copy of corrupted database and start Microsoft Excel.
  • In new Excel workbook, click Data option, and then point to Get External Data. Click New Database Query.
  • Click New Data Source in dialog box of Choose Data Source and click OK.
  • Name the data source with TestRecover in first step of Create New Data Source.
  • Select Access driver or .MDB in second step and then click Connect.
  • Click Select in dialog box of ODBC Microsoft Access Setup.
  • Browse the corrupted database in Select Database screen and click OK.
  • Click OK on Create New Data Source screen.
  • Select TestRecovery in Choose Data Source screen and click OK.
  • Double-click first table on Query Wizard- Choose Columns screen to add files to Columns in your query.
  • Follow the on screen instructions to finish the process.
  • Save data to Excel spreadsheet when it prompts and repeat these steps for all the tables.
  • Import all the spreadsheets to a separate spreadsheet.
After exporting all the data, you can now import data from spreadsheets to new database. The above mentioned steps work in most of the database corruption scenarios.
In case you are not able to work around the problem using above solution, you can opt for third-party recovery solutions. Such tools are designed particularly to repair and restore all damaged database objects in all cases of corruption.