Thursday, May 5, 2011

Global variables in Access database

The only persistent data in our application is the data in the database. Until now the only dialog data has been some data in the forms, for instance the value of the unbound controls. This data disappears when the form closes, so we need some way to handle dialog data that lives across the entire dialog with the user. Modules is the solution to the problem because a module is open and holds data as long as the application is running. As a simple example, we will see how to handle a simulated today's date. In the real system, we need to show today's date in many places, for instance as the default search criterion for arrival date.It is easy to do by means of the built-in function Date( ) which always gives us today's date (or rather the current date setting in the computer).

However, when testing the system, our test data is planned for some specific dates. We cannot change the test data every day we need it. We might instead change the current date setting of the system to the simulated date, but this is not recommended because it has side effects on other things, for instance the date of files created or changed, which again may create havoc in automatic backup procedures.











In this Example, Date is a global variable, sim Date is Access Function.

Tuesday, May 3, 2011

Can conversion cause corruption in Access Database??

Microsoft Access 2003 is a productivity suite written by Microsoft which was successor to Office Access XP and the predecessor to Office Access 2007. Office Access 2007 has plenty of advantages over earlier versions of MS Office Access. Files created in Microsoft Access 2000, 2002, 2003 can be opened in Microsoft Access 2007. Additionally database created in earlier previous versions can be converted into the new Office Access 2007 file format. The conversion provides you several functionalities and features available in Office 2007. At times, you might confront adversities with the converted database. Chances are corruption in files which leads to data inaccessibility. As an alternative, you should always have a updated backup of the database. However, If you are unable to maintain a backup of database then you have no better option than opting for a third party access database repair software.

To understand this lets take a real time example wherein you convert a database created in MS Access 2003 into MS Access 2007 format. It works fine for few days but later you find some error message:

“Microsoft Office Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open a new database. The names of objects that were not successfully recovered will be logged in the 'Recovery Errors' Table."

Due to before mentioned message data becomes inaccessible to the user. Before proceeding for further plan of action we must know about the cause of corruption and its recuperations.

Cause: Corruption can occur due to many reasons including power surges, malfunctioning, system crash etc. Another reason could be due to the excessive use of same resource at the same time by different users.

Resolution: Perform the following steps to perform access database recovery.

If shared Access database is compacted by you which is located on a server or shared folder then open MS Access database file needs to be closed and ensure it is not opened by any other user.
Go to Tools menu>>Database Utilities>>click Compact and Repair Database option
Run the Access file you want to compact in the database and click compact
Click Save
The original Access file will get replaced with the compacted at the same location

If still you don't get the solution then download an advanced and system compatible access recovery software from the Internet.