Access User-Level Security and ADO.NET

Access User -Level Security and ADO.NET

Access user-level security interoperates well with ADO.NET. With user-level security, you can create .NET solutions that authenticate and authorize data access and data manipulation tasks for Access database files. Those building solutions for Access databases with Visual Basic .NET likely will have good working knowledge of user-level security because it has been so closely associated with Access databases since its early releases. When invoking user-level security, you must understand that security does not impact your code until the code makes a request of the Jet engine. This functionality is common in database solutions, and if it is acceptable for your application, you can rely on user- level security to provide a useful and familiar alternative to the specialized .NET Framework security techniques.

This section examines user-level security from several perspectives. First, the section begins with a quick review of user-level security design and operation issues. This is mostly to refresh your memory and highlight key issues. You should consult Access Help if you want a more in-depth review of user-level security. Second, the section reviews the setup of a database with user-level security. All the instructions are for the Access 2002 user interface, which is similar to earlier versions of Access. Using the Access user interface makes the sample setup easy to follow. I did this to save the sample code for the discussion of management of user-level security. The third and fourth parts of this section present the sample code. The first sample focuses on data access. The second sample demonstrates how to manage security as a gateway for manipulating a database.

Note  

One unique benefit of the Access 2002 user interface is the direct access it offers to the Workgroup Administrator on the Tools, Security menu. Users of earlier versions of Access can search for this administrator (for example, by using the Search or Find utility available by right-clicking the Windows Start button).

Review of User-Level Security Design Issues

User-level security is the most robust form of built-in security for Access database files. This form of security requires the existence of a workgroup information file. This file is actually an Access database file, but it has an .mdw extension and plays a special role in user-level security. The .mdw file for a workgroup stores information about the users and groups in a workgroup. These users and groups enjoy permissions, such as the ability to read or update a table in an Access database. One .mdw file can apply to one or more .mdb files. In the user-level security model, the .mdb file contains the database objects, such as tables and queries, as well as permissions to use those objects. User-level security manages security through the intersection of an .mdw file with an .mdb file.

Note  

The .mdw file is sometimes referred to as the system database file. This designation is particularly relevant to creating connection strings for Access databases with user-level security.

In a sense, user-level security is always on, but it is not activated until you assign a password to the built-in Admin account. The Admin account initially has a Null password. Before assigning a password to the Admin user, anyone can open an Access database file without specifying a password. All the Access database samples shown so far in the book demonstrate how to open database files without user-level security. Once the Admin account has a password, you must specify an .mdw file, a user account, and optionally a user password to open an Access database file. The user account (and its optional password) permit user-level security to authenticate a user. The intersection of the user and group account specifications in the .mdw file with the permissions and database objects in the .mdb file authorize users to perform actions, such as reading and updating database objects. When using Visual Studio .NET to programmatically open a database in which the Admin account has a password, you must update your connection string to designate an .mdw file as well as a user account and the password for the account (if one exists).

All Access databases initially have an Admin user account as well as Admins and Users group accounts. You can create additional user and group accounts to designate the individuals and roles of workgroup members in a user-level security model for an Access database file. The Admin account is initially a member of the Admins group account. Any user account in the Admins group account is an administrator in a workgroup. Users opening an Access database file (.mdb) with an administrator account have unrestricted permissions for the objects in the database. Because the Admin account exists in all Access database files, it is good security practice to remove it from the Admins group after adding another custom-defined user to that group. This practice strengthens your database security because the name of an administrator account is not automatically known.

You will also typically want to remove any permissions for the Users group. This is because any new users you add to a workgroup automatically inherit these permissions. Therefore, individual user accounts have whatever permissions you explicitly assign them plus the permissions they inherit from the Users group. By removing all permissions from the Users group, you maintain stricter control over the permissions to individual user accounts and any group accounts that you create.

Setting Up for the User-Level Security Samples

To set up for the user-level security samples, we will need an .mdw file with designated user and group accounts and an .mdb file with database objects and coordinated permissions for the user and group accounts in the .mdw file. The security samples demonstrate a simple data access task that is restricted to users with permission to read a table and an update task that selectively grants update permission to some but not all users. The .mdb file for the security samples has the name MyNWTables.mdb. This file contains copies of the Categories and Customers tables from the Northwind database. The .mdw file for the security samples has the name MyNWTables.mdw, and it contains users with selective permissions for data access as well as data manipulation.

To begin the setup, open a new session of Access 2002. Choose Tools, Security, Workgroup Administrator. Then, click Create on the Workgroup Administrator dialog box. Access automatically populates the Name and Organization text boxes on the Workgroup Owner Information dialog box with the name and organization of the registered Access licensee. Enter a text string of up to 20 characters into the Workgroup ID text box and click OK. The Workgroup ID value is case sensitive. For the sample workgroup information file, I entered MyNWTables. You should securely store the Workgroup ID value (along with the Name and Organization values on the dialog box) in case you have to reconstruct the workgroup information file. You can save the .mdw file anywhere that your Visual Studio .NET project can access it. The following two security samples use the bin folder within the project folder for a Windows application project named MyNWTables. Browse to the folder in which you want to store your .mdw file, assign a filename such as MyNWTables in the File Name box in the Select Workgroup Information File dialog box, and click Open.

After clicking Open, you are ready to work your way out of the dialog boxes. Start by clicking OK twice. Figure 13-1 shows the dialog box in which you click OK the second time. This dialog box confirms the workgroup information file settings. You might want to take a screen shot of this dialog box to document your workgroup information file settings. Clicking OK here displays yet another message, confirming the successful creation of the workgroup file. Clicking OK in this display and OK in the Workgroup Information File dialog box puts you in a position to use the workgroup information file.

click to expand
Figure 13-1: The confirmation dialog box for setting up the sample workgroup information file (.mdw)

You can start using the .mdw file by closing and reopening Access. Access automatically uses the last .mdw file created as the workgroup information file. Start by creating three new user accounts: MyAdmin, ReadOnlyUser, and ReadWriteUser. All user accounts will have blank passwords until we change them later in the process. To create a new user, choose Tools, Security, User And Group Accounts. In the resulting dialog box, click New on the Users tab. Enter a user account name and a personal ID for the first user account, such as MyAdmin and MyNWTables . Just as you saved the workgroup ID along with the name and organization needed to re-create the workgroup information file, you should store the name and personal ID settings for all users. Saving this information permits you to re-create the account if necessary. Figure 13-2 shows the setup settings for the MyAdmin user account. Click OK to finalize the settings. Then, create the other two user accounts, ReadOnlyUser and ReadWriteUser. You can use any legitimate personal ID setting that you prefer ” namely, any alphanumeric string 4 “20 characters in length.


Figure 13-2: Setup settings for the MyAdmin user account

After creating the user accounts, you can manipulate these newly created user accounts as well as the built-in Admin user account. For these samples, you ll need to make changes to the MyAdmin and Admin user accounts. Select MyAdmin from the drop-down box on the Users tab in the User And Group Accounts dialog box. Notice that this user account is a member of the Users group but not the Admins group. Because the security samples depend on the MyAdmin account being a security administrator, select Admins in the Available Groups box and click Add>>. The next step is to remove the Admin account from the Admins group. Start by selecting the Admin user in the drop-down box. With Admins selected in the Member Of box, click <<Remove. This removes Admin as a security administrator. Next, assign a password to the Admin account to require user authentication when opening Access. Click the Change Logon Password tab. Leave the old password box blank, and enter the string password into the New Password and Verify text boxes. Click OK to confirm your settings. Assigning a password to the Admin account pops up a Logon dialog box the next time you use Access interactively to perform a task that involves user-level security.

Close and reopen Access to launch a session with MyAdmin as the default security administrator account. Click the Blank Database link on the Task pane. Then, save a new database file (.mdb) named MyNWTables in the bin subdirectory of the MyNWTables Windows application project folder. After clicking Create in the File New Database dialog box, Access pops up a Logon dialog box with MyAdmin as the user account and no password. Click OK. This step makes MyAdmin the owner of the MyNWTables database. Import the Customers and Categories tables from the Northwind database with the File, Get External Data, Import command.

Note  

If the Logon dialog box does not default to designating MyAdmin before you create the database file, override the entry and insert MyAdmin as the user account.

After importing the tables, adjust the permissions for the users and groups so that they match our requirements. Choose Tools, Security, User And Group Permissions to open a dialog box. Select the Categories table. Notice that only the MyAdmin user has any permissions for the table. In fact, the MyAdmin user has all the permissions for the Categories table. The same pattern of user permissions applies to the Customers table as well as for new tables and queries. Select the Groups radio button below the User/Group Name box. In this case, both the Admins and the Users groups have all permissions for both tables as well as new tables and queries. Select the Users group in the User/Group Name box with the Categories table selected, and clear all permission check boxes on the dialog box. (Clicking the Read Design check box can accomplish this.) Then, click Apply. Use the same process to clear Users group permissions for the Customers table as well as new tables and queries. You can now set permissions for individual user accounts without having membership in the Users group convey extra permissions.

Start to set permissions for the ReadOnlyUser and ReadWriteUser accounts by clicking the Users radio button. Then, select the ReadOnlyUser account name and the Categories table. Select the Read Data permission check box. Access additionally selects the Read Design check box because Access links these two permissions. (See Figure 13-3.) Clicking Apply assigns the ReadOnlyUser account permission to read data from the Categories table. Apply the Read Data permission for the Customers table to the ReadOnlyUser account as well. In the case of the ReadWriteUser account, assign Read Data and Update Data permissions for both the Categories and Customers tables; this process automatically includes Read Design permission as well. After making the permission assignments just described, the Admin user still has no permissions for either the Categories or Customers tables.

click to expand
Figure 13-3: Permission settings for the ReadOnlyUser account with the Categories table

To complete the security settings, we can add passwords to the three new users added to the MyNWTables.mdw file. We already specified a string value of password for the Admin user. At this point, each new user still has a Null password value. (That is, these user accounts have no password value.) You can change the password for the MyAdmin user, who is the current user, by choosing Tools, Security, User And Group Accounts. Next, select the Change Logon Password tab. Then, follow the previously described process for adding a password to the Admin user. To assign passwords to the ReadOnlyUser and ReadWriteUser accounts, close and reopen Access with each user account. During the session for each user account, choose Tools, Security, User And Group Accounts and follow the process for assigning a password to the MyAdmin account. In the sample for this chapter, I assigned the string password as the password for all three new user accounts.

Note  

See my book Programming Microsoft Access Version 2002 (Microsoft Press, 2001) for VBA code samples that manage and report on user-level security with the ADOX object model or Jet SQL.

Selecting from a Secure Access Database with ADO.NET

The first user-level security sample demonstrates how to specify the arguments for a secure connection as well as an approach to managing the connection. The sample s discussion begins with a demonstration of how user-level security works for a simple selection task in a Windows application project. Then, the presentation describes how the code behind the Windows form controls the operation of the application.

Demonstration

Figure 13-4 shows the selection sample application in action with the ReadOnlyUser account. The top window shows the Windows form for the application. The top text box ( TextBox1 ) displays the user account name. The second text box ( TextBox2 ) displays asterisks instead of the password that I typed into the text box. Clicking the top button ( Button1 ) with the Text property value of Show Scalar opens the second window in Figure 13-4. This window is a message box that merely echoes the connection string that the application uses to connect to the MyNWTables.mdb file. I will discuss the syntax of the connection string when I review the code for the sample. Showing this message box reinforces the value of displaying intermediate values such as connection strings when you debug an application. In a production version of an application, you typically comment out the lines of code that display this type of message box. The bottom window in Figure 13-4 shows the result of the selection statement (a Count aggregate function) in a string expression. The function returns the number of CategoryID values in the Categories table.

click to expand
Figure 13-4: The selection sample application in action, with an account that can read from the Categories table

The sequence of windows in Figure 13-4 concludes with a display of the number of categories in the Categories table because the ReadOnlyUser account has permission to read the data in the Categories table. However, what happens if a user inputs a user account that doesn t have Read Data permission for the Categories table? Figure 13-5 shows the sequence of windows for the Admin user. This user is in the MyNWTables.mdw file, so it is a valid user account. However, the account does not have Read Data permission for the Categories table. The top window shows the input values, and the middle window shows the resulting connection string. This type of display can be useful if input values are no longer easily visible elsewhere. The bottom window displays a native error message from the Jet engine. The application uses a Try Catch End Try statement to return the error message. If the user inputs an invalid user account such as Adminx, the bottom window would confirm that either the user account or password is not valid. Again, this would be a message from the Jet engine displayed inside the Windows application.

click to expand
Figure 13-5: The selection sample application in action, with an account that cannot read from the Categories table

Code Behind the Form

The code behind Button1 takes advantage of four code blocks behind the form: the module-level declaration area, the Form1_Load procedure, the Button1_Click procedure, and the ConnectToMyNWTables procedure. The module-level declaration area that follows includes declarations for the next sample as well. However, this sample relies on the declaration and the instantiation of cnn1 as an OleDbConnection object. The other elements of the code use this object reference and invoke methods for it. This cnn1 variable serves double duty in this code sample as well as the next one.

The Form1_Load procedure formats the controls on the form. This procedure groups its code into three units. The first unit formats the labels by assigning Text property values as well as setting the alignment so that the text appears close to the text box on the left edge. The second code unit formats the text box controls on Form1 . This code clears both text boxes of their default assignments and assigns an asterisk as the symbol for representing password characters in TextBox2 . The closing unit of code in the Form1_Load procedure assigns Text property values to the three button controls on the form.

The Button1_Click procedure works in concert with the ConnectToMyNWTables Function procedure to process and report on data from the MyNWTables.mdb file. The Button1_Click procedure starts by invoking the ConnectToMyNWTables function. While the Button1_Click procedure invokes the function, it passes Text property values for TextBox1 and TextBox2 as arguments. The click event procedure saves the return value from the function in the cnn1 variable. The ConnectToMyNWTables function instantiates a new connection object each time it is invoked based on the current procedure s Text property values for TextBox1 and TextBox2 . With these design features, the procedure does need code to close the connection object ( cnn1 at the module level) to change the connection string if the text box values differ from the last invocation of the procedure. Next, the Button1_Click procedure displays the connection string value computed by the function procedure in a message box.

As mentioned previously, you will probably want to add a comment marker in a production application before the line displaying the connection string, but displays such as this are convenient while you are debugging an application. Next, the Button1_Click procedure tries to open the OleDbConnection object at which the cnn1 variable points. If the user inputs a valid user account and password and the database is available, the Open method should succeed. The procedure embeds the invocation of the Open method in a Try Catch End Try statement. By printing the Message property for Exception objects thrown by the Open method, the procedure displays native error messages from the Jet engine.

After opening a connection to the MyNWTables Access database, the Button1_Click procedure moves on to using the connection. It does this by instantiating cmd1 as an OleDbCommand object. Then, it uses the cnn1 variable and a SELECT statement to assign property values to the object. Next, the procedure embeds the invocation of the ExecuteScalar method for the OleDbCommand object in another Try Catch End Try statement. This statement catches the use of the Admin account, which does not have Read Data permission for the Categories table. The use of the Admin account does not throw an Exception in the first Try Catch End Try statement because the account is valid for making a connection to the MyNWTables database. The next to last statement in the Button1_Click procedure displays a count of the rows in the Categories table, and the last statement in the procedure closes the cnn1 object.

The ConnectToMyNWTables Function procedure starts by checking to see whether the user ID or password arguments passed to it are empty strings. If so, the connection will not succeed. The first Try Catch End Try statement in the Button1_Click procedure catches the Exception that the .NET Framework throws for either missing value, but the two If Then statements that start the function procedure offer more precise feedback to the user about the cause of the problem and how to fix it. After testing the user ID and password values that serve as inputs to a connection string, the procedure computes the string in four separate parts with semicolons as delimiters between parts. The procedure starts the specification of the connection string by designating the Jet provider.

Next, the procedure adds a pointer for the workgroup information file. Then, the procedure references the data source for the connection object. Both the workgroup information file and the Access database file reside in the bin subdirectory of the project folder. Therefore, you can reference the files without designating any explicit path . The expressions for computing a connection string conclude by appending the user ID and password arguments. The user ID is the user account name in TextBox1 , and the password argument is the password associated with the user account. The second and fourth parts of the connection string are mandated because the application relies on user-level security. This reliance permits the rejection of user account names that are not in the workgroup information file and the rejection of attempts to perform actions by a user account for which it does not have permission.

 ˜Declare and instantiate an OleDbConnection object Dim cnn1 As New System.Data.OleDb.OleDbConnection() ˜Instantiate dap1 as an OleDbDataAdapter object Dim dap1 As New OleDb.OleDbDataAdapter() ˜Instantiate das1 as a DataSet object Dim das1 As New System.Data.DataSet() Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Format label controls Label1.Text = "User ID" Label2.Text = "Password" Label1.TextAlign = ContentAlignment.BottomLeft Label2.TextAlign = ContentAlignment.BottomLeft Format text box controls TextBox1.Text = "" TextBox2.Text = "" TextBox2.PasswordChar = "*" Format button controls Button1.Text = "Show scalar" Button2.Text = "Add x" Button3.Text = "Remove x" End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Specify a connection to MyNWTables database with User ID and Password specified in text box controls cnn1 = ConnectToMyNWTables(TextBox1.Text, _ TextBox2.Text) Display connection string for diagnostic purposes; comment this line out in production version MsgBox(cnn1.ConnectionString) Connect or exit procedure after displaying diagnostic message if attempt to connect fails Try cnn1.Open() Catch exc1 As System.Exception MsgBox(exc1.Message, , _ "Error message from Open method") Exit Sub End Try Declare and instantiate an OleDbCommand object Dim cmd1 As New System.Data.OleDb.OleDbCommand() Assign Connection and CommandText property values for the cmd1 OleDbCommand object cmd1.Connection = cnn1 cmd1.CommandText = _ "SELECT Count(CategoryID) FROM Categories" Save result set from command in int1 memory variable if ExecuteScalar method succeeds; otherwise , display diagnostic message Dim int1 As Integer Try int1 = cmd1.ExecuteScalar Catch exc1 As System.Exception MsgBox(exc1.Message, , _ "Error message from ExecuteScalar method") Exit Sub End Try Display result from query MsgBox("The number of categories is " & _ int1.ToString & ".") Close connection cnn1.Close() End Sub Function ConnectToMyNWTables(ByVal inID As String, _ ByVal inPW As String) As OleDb.OleDbConnection Check for missing User ID or Password If inID = "" Then MsgBox("Input a UserID please.", _ MsgBoxStyle.Exclamation, _ "Input error message") End If If inPW = "" Then MsgBox("Input a Password please .", _ MsgBoxStyle.Exclamation, _ "Input error message") End If Designate string for connection to MyNWTables database first, specify provider second, specify workgroup information file third, specify target database fourth, designate a user id and password Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 &= _ "Jet OLEDB:System database=MyNWTables.mdw;" str1 &= _ "Data Source = MyNWTables.mdb;" str1 &= _ "User ID=" & inID & ";Password=" & inPW Instantiate an OleDbConnection object and assign a connection string to the object Dim cnn1 As New System.Data.OleDb.OleDbConnection() cnn1.ConnectionString = str1 Return the Connection object Return cnn1 End Function 

Updating a Secure Access Database with ADO.NET

The second user-level security sample illustrates how to manage data manipulation with a secure Access database. The presentation of the sample starts with a demonstration of how the code operates. Then, the presentation shifts to a review of the code elements.

Demonstration

This user-level security sample demonstrates the impact of the Read Update permission. This permission enables a user account to update a column value in a table or a query. The ReadWriteUser account has this permission for the Customers table, but the ReadOnlyUser account does not. Therefore, an attempt to update the Customers table succeeds for the ReadWriteUser account but fails for the ReadOnlyUser account. As you saw, the ReadOnlyUser account was successful in the prior sample. This distinction confirms your ability to manage permissions for various kinds of database tasks.

This user-level security sample relies on event procedures associated with two buttons . The first button ( Button2 ) has a Text property value of Add x . This is because the button adds an x to the end of the CompanyName column value for the customer with a CustomerID column value of ALFKI in the DataTable object of a local dataset. The application synchronizes the DataTable object in the local dataset with the Customers table in the MyNWTables database. After adding the x to the local dataset, the procedure updates the database through an OleDbDataAdapter object. The second button ( Button3 ) has a Text property value of Remove x . This button simply removes the last character from the same row and column referenced by Button2 in the DataTable object for the local dataset before updating the database to reflect the change.

Figure 13-6 shows a series of three windows that illustrates the operation of Button2 . The top window shows Form1 with the ReadWriteUser account specified as the user ID. Because this account has the Read Update permission for the Customers table, the account has the authority to add an x to the CompanyName column value for the row with a CustomerID column value of ALFKI . The second window shows an Access Datasheet view of the Customers table with the appended x that results from clicking Button2 . Clicking Button3 displays an intermediate message before removing the last character from the CompanyName column value from the row with ALFKI as a CustomerID column value. This message reminds the user that the removal of the last character is based on the connection for the Add X button. That is, Button3 does not make a fresh connection to the database; instead, it uses the existing connection for the last time the local dataset was populated . The sample code takes advantage of the disconnected dataset object in ADO.NET. Therefore, it operates on the local dataset created when the user last clicked Button2 . You can confirm that the x is removed by going to the Access database and refreshing the second window shown in Figure 13-6.

click to expand
Figure 13-6: The operation of the second user-level security sample, with a valid account for updating the Access database

Figure 13-7 shows the operation of the second user-level security sample with a user account that is invalid for updating the Customers table in the MyNWTables database. The ReadOnlyUser account has Read Data permission for the Customers table but does not have Update Data permission. The change to the local dataset will succeed because Access user-level permissions do not apply to that table. However, when the application invokes the Update method to synchronize the changed local dataset with the Customers table in the Access database, the application throws an Exception object. A Try Catch End Try statement traps the Exception object and displays the message returned by the Jet engine in the second window shown in Figure 13-7.


Figure 13-7: The operation of the second user-level security sample, with an invalid account for updating the Access database

Code Behind the Form

The code behind Form1 for the update of the Customers table in the Northwind database relies on elements in the listing from the prior sample, including data adapter ( dap1 ) and dataset ( das1 ) declarations at the module level and the ConnectToMyNWTables Function procedure. Of course, specialized code that targets the unique needs of this user-level security sample also exists. The following listing includes click event procedures for Button2 and Button3 as well as a function procedure, ConnectForUpdate , that builds on the function procedure from the prior sample to enable updating a secure Access database over an OleDbConnection object.

The Button2_Click procedure has three primary objectives. First, the procedure readies the application to update the Customers table in the MyNWTables.mdb database file. The procedure meets this goal by testing the return value from the ConnectForUpdate Function procedure. A return value of <;$MI>1 signals a failed attempt to connect. In response to this return value, the code halts by exiting the Button2_Click procedure. Second, the procedure updates a local DataTable object by appending an x to the value in the second column of a row whose first column value equals ALFKI . The first step in this update initializes the local DataTable object based on the values in the Customers table of the MyNWTables.mdb database file. Third, the procedure invokes the Update method for the dap1 variable instantiated in the ConnectForUpdate Function procedure. By invoking the Update method in a Try Catch End Try statement, the procedure can detect an attempt by an unauthorized user to update the Customers table. If the Update method succeeds, the procedure ends normally. If a user causes the application to invoke the Update method with a user account that does not have Update Data permission, the procedure traps the resulting Exception object and displays the associated message from the Jet engine.

The Button3_Click procedure generally follows the design of the click event procedure for Button2 , except that the first step is decidedly different. As you have learned, the role of this procedure is to undo the change implemented by the Button2_Click procedure. The first step in the Button3_Click procedure does not create a connection to the MyNWTables.mdb file. Instead, it checks to see whether the das1 dataset, initially populated by the invocation of the ConnectForUpdate Function procedure in the Button2_Click procedure, has any DataTable objects. If the dataset has no DataTable objects, the code will have nothing to undo and halts by exiting the Button3_Click procedure. If at least one DataTable object exists in the das1 dataset, the procedure prints a message reminding the user it will work with the Connection object created by the click to the Add X button ( Button2 ).

The second step in the Button3_Click procedure differs only slightly from the same step in the Button2_Click procedure. The Button3_Click procedure removes the last character from the second column value from the DataTable object s row, whose first column value is ALFKI . This process allows the Button3_Click procedure to remove the x added by the Button2_Click procedure. The third step in the Button3_Click procedure is identical to the third step in the Button2_Click procedure. Its goal is to synchronize the change to the local DataTable with the Customers table in the MyNWTables.mdb file.

The ConnectForUpdate Function procedure provides two services. First, it makes a connection to the MyNWTables.mdb file. It does this by calling the ConnectToMyNWTables Function procedure while passing the user account name and password value in TextBox1 and TextBox2 , respectively. The review of the code for the preceding sample describes the operation of the ConnectToMyNWTables Function procedure. The second step involves using the connection created in the first step to assist in the definition of an OleDbDataAdapter object that facilitates updating a database table. You learned in Chapter 8 that this task requires the use of parameters in the specification of properties for an OleDbDataAdapter object. In fact, the code in this procedure specifying the ability of the data adapter to populate and update a dataset is an excerpt from the code listed in the Data Manipulation Setup Code section of Chapter 8.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click If did not fill dataset, exit procedure If ConnectForUpdate() = -1 Then Exit Sub End If Select row to modify Dim drw1 As DataRow = _ das1.Tables(0).Rows.Find("ALFKI") Perform row modification by appending an x drw1(1) &= "x" Update Access database file and local dataset row state for found DataRow Try dap1.Update(das1, "CustomerIDsNames") Catch exc1 As System.Exception MsgBox(exc1.Message, , _ "Error message from Update method") End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click If no DataTable object in das1, exit procedure If Not (das1.Tables.Count > 0) Then MsgBox("No DataTable to update.", _ MsgBoxStyle.Exclamation, _ "Missing DataTable message") Exit Sub Else MsgBox("Preparing to remove x with " & _ "Connection from Add x", _ MsgBoxStyle.Information, _ "Message from Remove x") End If Select row from DataTable in das1 to restore Dim drw1 As DataRow = _ das1.Tables(0).Rows.Find("ALFKI") Restore by removing last character Dim int1 As Integer = Len(drw1(1)) - 1 drw1(1) = Mid(drw1(1), 1, int1) Update Access database file and local dataset row state for found DataRow Try dap1.Update(das1, "CustomerIDsNames") Catch exc1 As System.Exception MsgBox(exc1.Message, , _ "Error message from Update method") End Try End Sub Function ConnectForUpdate() as Integer Specify a connection to MyNWTables database with User ID and Password specified in text box controls cnn1 = ConnectToMyNWTables(TextBox1.Text, _ TextBox2.Text) Specify SelectCommand and UpdateCommand properties dap1.SelectCommand = New OleDb.OleDbCommand( _ "SELECT CustomerID, CompanyName " & _ "FROM Customers", cnn1) dap1.UpdateCommand = New OleDb.OleDbCommand( _ "Update Customers SET CustomerID = ?, " & _ "CompanyName = ? WHERE CustomerID = ?", cnn1) Specify parameters for UpdateCommand dap1.UpdateCommand.Parameters.Add("@CustomerID", _ OleDb.OleDbType.Char, 5, "CustomerID") dap1.UpdateCommand.Parameters.Add("@CompanyName", _ OleDb.OleDbType.VarChar, 40, "CompanyName") dap1.UpdateCommand.Parameters.Add("@oldCustomerID", _ OleDb.OleDbType.Char, 5, "CustomerID"). _ SourceVersion = DataRowVersion.Original Populate das1 if you have Read permission for database table behind local DataTable object Try dap1.Fill(das1, "CustomerIDsNames") Catch exc1 As System.Exception MsgBox(exc1.Message, , _ "Error message from Fill method") Return -1 End Try Define a primary key for the DataTable das1.Tables(0).PrimaryKey = _ New DataColumn() _ {das1.Tables(0).Columns("CustomerID")} End Function 
 


Programming Microsoft Visual Basic. NET for Microsoft Access Databases
Programming Microsoft Visual Basic .NET for Microsoft Access Databases (Pro Developer)
ISBN: 0735618194
EAN: 2147483647
Year: 2006
Pages: 111
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net