Tracking Revisions

[Guest]

Is there any way to track revisions to an Access database? In other words,
is there a way to show what data was changed in the previous month, etc.?

Thanks in advance for any help you can provide.

LotB

 

[Guest]

Not built-in, but you can create ModifiedBy and ModifiedDate fields in your
tables to capture the NT username and date/time the info was changed. But
has far as looking at Column A and determining what is different between
January and February, you would have to create your own queries or use VBA.
There are 3rd party tools as well but unfortunately, I cannot think of any
off hand.

 

[Guest]

I think the "Modified By" solution would work, but I'm fairly new to Access.
Can you help me out on how to do that?

Thanks again,

LotB

 

[Guest]

Add two fields to your table:
-- ModifiedBy (Text)
-- ModifiedDate (Date/Time)

Create a module and add the following function, written by an Access MVP, to
capture the NT login name:

-- Declaration Statement (paste under Option Compare statement in VBA
editor):
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

-- Function:
Function fOSUserName() As String
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function

In the AfterUpdate event of the form used to data enter into the table, enter:

[ModifiedBy] = ModuleName.fOSUserName
[ModifiedDate] = Now()

You can format the Date/Time field to meet your needs. I hope this helps.

 

[Guest]

I am not sure where or how to place this: [ModifiedDate] = Now()

I used the Expression Builder in the After Update cell of the property sheet
for the form.

After clicking OK, it changes the expression to "=[ModifiedDate] = Now()"

ModifiedDate is a date field that I created in the table to which this form
is related.

As you can tell I am a complete amateur with Visual Basic and Microsoft
Access. I am used to using FileMaker which has stuff like this built in.

Thanks for any help you can give to get me on track with using some of the
deep functionality of Access.

- northwardspirit

Add two fields to your table:
-- ModifiedBy (Text)
-- ModifiedDate (Date/Time)

Create a module and add the following function, written by an Access MVP, to
capture the NT login name:

-- Declaration Statement (paste under Option Compare statement in VBA
editor):
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

-- Function:
Function fOSUserName() As String
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function

In the AfterUpdate event of the form used to data enter into the table, enter:

[ModifiedBy] = ModuleName.fOSUserName
[ModifiedDate] = Now()

You can format the Date/Time field to meet your needs. I hope this helps.

I think the "Modified By" solution would work, but I'm fairly new to Access.
Can you help me out on how to do that?

Thanks again,

LotB

 

[tina]

don't put the code in the Event *line* in the property sheet, put it in the
Event's *procedure* in the VBA Editor window. if you don't know how to
create an event procedure in a form (or report), go to
http://home.att.net/~california.db/instructions.html and click the
"CreateEventProcedure" link for illustrated instructions.

hth

I am not sure where or how to place this: [ModifiedDate] = Now()

I used the Expression Builder in the After Update cell of the property sheet
for the form.

After clicking OK, it changes the expression to "=[ModifiedDate] = Now()"

ModifiedDate is a date field that I created in the table to which this form
is related.

As you can tell I am a complete amateur with Visual Basic and Microsoft
Access. I am used to using FileMaker which has stuff like this built in.

Thanks for any help you can give to get me on track with using some of the
deep functionality of Access.

- northwardspirit

Add two fields to your table:
-- ModifiedBy (Text)
-- ModifiedDate (Date/Time)

Create a module and add the following function, written by an Access MVP, to
capture the NT login name:

-- Declaration Statement (paste under Option Compare statement in VBA
editor):
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

-- Function:
Function fOSUserName() As String
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function

In the AfterUpdate event of the form used to data enter into the table, enter:

[ModifiedBy] = ModuleName.fOSUserName
[ModifiedDate] = Now()

You can format the Date/Time field to meet your needs. I hope this helps.

I think the "Modified By" solution would work, but I'm fairly new to Access.
Can you help me out on how to do that?

Thanks again,

LotB

:

Not built-in, but you can create ModifiedBy and ModifiedDate fields in your
tables to capture the NT username and date/time the info was changed. But
has far as looking at Column A and determining what is different between
January and February, you would have to create your own queries or use VBA.
There are 3rd party tools as well but unfortunately, I cannot think of any
off hand.

:

Is there any way to track revisions to an Access database? In other words,
is there a way to show what data was changed in the previous month, etc.?

Thanks in advance for any help you can provide.

LotB