Roger's Access Blog: Access 2010

Showing posts with label Access 2010. Show all posts

Wednesday, January 25, 2012

Now() Function in Default Value Stores the Wrong Time

In a previous post (Date Stuff – Using the functions), I discussed, among other things, using built in Access functions (specifically Date() and Now()) in the default value of a field in a table.

The Date() function is generally safe to use, but the Now() function is problematic. Now() in the default value of your field WILL store the wrong time. If your concern is to store an accurate time of when the record was saved, this is a problem.

Let me illustrate. Suppose I have a simple table like so:

Now, I'm going to add a field called CreatedDate, and use the Now() function in the Default Value of the field.

The New Record, that is, the one at the bottom with the asterisk at the far left, has not yet been created. The date and time displayed is the time that I opened the table (or form, if the form was based on that table).

However, suppose I take a few minutes before I actually enter anything into the new record.

Once I begin typing in the record, it is created (but not yet saved), but notice that the time that I opened the table is still displayed, even though the actual time (as shown in the task bar) is 6 minutes later.

By the time I get around to actually saving the record (by moving to the new New Record) another 5 minutes has passed.

So the record was saved at 6:29, but the time stored in the field is 6:18. Not only that, but the next New Record displays the time that the previous record was created.

Solutions

So what do you do to get an accurate time in the CreatedDate field? There are two solutions depending on which version of Access you're using: Access 2007 (and previous) or Access 2010.

Access 2007 and Previous

In Access 2007 and before, it cannot be done at the table level. It must be done in a form. So in Design View of your form, create a code procedure in the BeforeUpdate Event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(CreatedDate) Then
        CreatedDate = Now()
    End If
End Sub

Access 2010

With the introduction of Access 2010, we how have Data Macros (essentially triggers) that work at the table level.

On the Tables tab, select the Before Change Event and create the following data macro:

Now, you will save the actual time the record was saved.

Monday, August 2, 2010

Access 2010: Detect and Repair

Starting with Office XP, Microsoft introduced the Detect and Repair feature for all Office applications. This is different than the Access Compact and Repair in that it detected and repaired problems with the Office application itself (Word, Excel, Access, etc.) rather than a particular file.

In a way, it was like launching the Office Repair from Control Panel > Add/Remove Programs, except it targeted just the application you launched it from. If launched from Word, it would repair only Word. If launched from Excel, it would detect and repair problems in Excel only. If...well you get the idea.

Once launched, it brings up the Detect and Repair Wizard, which looks something like this:

In Access XP and Access 2003, Detect and Repair was launched from the Help Menu.

In Access 2007, they changed it to a more comprehensive utility called Microsoft Office Diagnostics, which is launched from Office Button > Access Options > Resources Tab > Diagnose.

As far as I know, however, the option is not available in Access 2010, either from the menu system or the Ribbon. You can, however launch it in code:

Application.CommandBars.FindControl(ID:=3774).Execute

This will launch the original Detect and Repair.

If you want to add it to the Ribbon, you'd have to put the code in a function in a general module:

Function Detect_Repair()

Application.CommandBars.FindControl(ID:=3774).Execute
End Function

Create a macro that runs the function:

And lastly, add the macro to a custom group on the Ribbon.

.

Tuesday, July 20, 2010

Access 2010: Unrecognized Database Format

I've been seeing more and more people having problems with the Unrecognized Database Format when creating or modifying an Access 2007 (accdb) format database in Access 2010 and then trying to open it again in Access 2007.

The issue appears to be that Access 2010 does not have its own file format. When you add a feature that is specific to A2010 (like the navigation control, data macro, or calculated column), that file becomes forever unreadable to A2007, even if the new feature(s) are removed. The solution appears to be:

Remove all the new features from the file in A2010
Create a new, blank accdb format database in A2010
Import all the objects from the old database to the new.

The file will then be readable by A2007. The following link explains it more in detail: http://msdn.microsoft.com/en-us/office/cc907897.aspx.

I'm told that it is also possible to make the original file A2007 readable by removing the A2010 features and then making a minor edit to the file in a Hex editor. But I'd be wary of that approach. Importing the objects to a new database is safer, more reliable, and frankly simpler.