Start by setting up a table and two Queries in Access:
Table Table1:
IncrementalKey Int (Primary Key)
LockExpirationDate Date/Time (This will be used to Lock/Unlock the record)
Query SelectTable1:
SELECT [Table1].[IncrementalKey], [Table1].[LockExpirationDate] FROM Table1;
Query UpdateExpiration:
UPDATE Table1 SET Table1.LockExpirationDate = DateAdd("n",10,Now())
WHERE [Table1].[IncrementalKey]=[@IncrementalKey];
Now, Open your Server Explorer in VS.NET.
Right-click Data Connections and click "Add Connection".
Select the latest JET provider and specify the location of the mdb file.
Test your connection and press OK.
You should now see the connection begining with "ACCESS." under Data Connections.
Expand the Views and StoredProcedures sections under the connection.
Drag the SelectTable1 view and the UpdateExpiration Stored Procedure onto your form/control. You can choose to store your password or provide it at runtime.
You now have a command, an adapter, and a connection on your form.
Unfortunately, OleDb does not recognize parameters in "Stored Queries", so we must add one for our "@IncrementalKey" parameter.
Right click on the OleDbCommand1 below your form and select properties.
Select Parameters and click the "..." button in the Properties window.
Click Add. Change the type to Integer.
We also need to remove the optimistic concurrency for the LockExpirationDate field from the update and delete commands since we are using a stored procedure to modify them after the fill.
Right click the adapter and select properties.
For both the update and delete commands, modify the command text from:
WHERE (IncrementalKey = ?) AND (LockExpirationDate = ? OR ? IS NULL AND LockExpirationDate IS NULL)
To:
WHERE (IncrementalKey = ?)
To do this, expand the update/deleteCommand Property, select CommandText, and click the "..." button.
Modify the text and make sure "regenerate parameters is checked". Click OK. Select YES to regenerate the parameters (nothing is lost since we haven't modified the parameters collection outside of the initial generation).
Do that for both commands.
Now, Right click on the adapter and generate a dataset.
(See .NET Databinding Secret to RAD blog)
I named it DataSetAccessLocking
Now drop a datagrid and bind it to DataSetAccessLocking1.table1.
Put some code in your Sub New():
Private Sub myInitializer()
Me.OleDbCommand1.Connection.Open()
Dim dr As DataSetAccessLocking.Table1Row
Dim i As Integer
Me.OleDbDataAdapter1.Fill(Me.DataSetAccessLocking1)
For Each dr In Me.DataSetAccessLocking1.Table1
If dr.IsLockExpirationDateNull OrElse dr.LockExpirationDate < Date.Today Then
'Unlocked - Lock
Me.OleDbCommand1.Parameters(0).Value = dr.IncrementalKey
Me.OleDbCommand1.ExecuteNonQuery()
Else
'Locked - Set Row Error
dr.RowError = "Locked"
End If
Next
'Remove locked rows from results
With Me.DataSetAccessLocking1.Table1.Rows
For i = .Count - 1 To 0 Step -1
If .Item(i).RowError = "Locked" Then
.Remove(.Item(i))
End If
Next
End With
Me.OleDbCommand1.Connection.Close()
End Sub
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
myInitializer()
End Sub
And override the OnClosed Subroutine to save and unlock:
Protected Overrides Sub OnClosing(ByVal e As System.EventArgs)
MyBase.OnClosed(e)
Dim dr As DataSetAccessLocking.Table1Row
For Each dr In Me.DataSetAccessLocking1.Table1.Rows
dr.SetLockExpirationDateNull()
Next
Me.OleDbDataAdapter1.Update(Me.DataSetAccessLocking1)
End Sub
