ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

This procedure demonstrates how to grant a login database access.  Then, the code uses the login to connect with the northwind database and write the output from a datareader to the immediate window.  the procedure also references a class (ADOObjs) that can return either a connection object or a command object.  The full listing for the procedure and class follow.

Private Sub Button3_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button3.Click

'Open the SQL Server Northwind
'database based on the sa login and
'create a command
Dim cnn1 As SqlClient.SqlConnection = _
ADOObjs.MakeACnn( _
"Northwind", "sysadminlogin", "sysadminloginpassword")
Dim cmd1 As _
SqlClient.SqlCommand = _
ADOObjs.MakeACommand(cnn1)

'Grant hcvs user access to Northwind
Try
cmd1.CommandText = _
"EXEC sp_grantdbaccess 'hcvs'"
cmd1.ExecuteNonQuery()
Catch ex1 As Exception
If InStr(ex1.Message, _
"'hcvs' already exists") Then
Exit Try
Else
Console.WriteLine( _
ex1.Message & ControlChars.CrLf & _
ex1.GetType.ToString)
End If
Finally
cnn1.Close()
End Try

'Open the SQL Server Northwind
'database based on the hcvs login and
'create a command
Dim cnn2 As SqlClient.SqlConnection = _
ADOObjs.MakeACnn( _
"Northwind", "hcvs", "passhcvs")
Dim cmd2 As _
SqlClient.SqlCommand = _
ADOObjs.MakeACommand(cnn2)

'Read subset of Products table from
'Northwind database and print to
'the Output window
Try
cmd2.CommandText = _
"SELECT ProductName, QuantityPerUnit, UnitPrice, UnitsInStock FROM Products"
Dim rdr1 As SqlClient.SqlDataReader = cmd2.ExecuteReader
Do While rdr1.Read
Console.WriteLine("{0}, {1}, {2}, {3}", _
rdr1.GetString(0), _
rdr1.GetString(1), _
rdr1.GetDecimal(2), _
rdr1.GetInt16(3))
Loop
Catch ex1 As Exception
Console.WriteLine( _
ex1.Message & ControlChars.CrLf & _
ex1.GetType.ToString)
End Try

End Sub



Public Class ADONETObjects


Function MakeACnn( _
ByVal DbName As String, _
ByVal LoginName As String, _
ByVal PassName As String) _
As SqlClient.SqlConnection

'Return SQL Server connection
Dim cnn1 As New _
SqlClient.SqlConnection( _
"Data Source = " & _
"(local); " & _
"Initial Catalog = " & DbName & "; " & _
"user id=" & LoginName & _
"; password=" & PassName)
cnn1.Open()
Return cnn1

End Function

Function MakeACommand( _
ByVal cnn1 As SqlClient.SqlConnection, _
Optional ByVal strSQL As String = "") _
As SqlClient.SqlCommand

Dim cmd1 As _
SqlClient.SqlCommand = _
cnn1.CreateCommand
cmd1.CommandText = strSQL
Return cmd1

End Function

End Class

Learn more about VB.NET programming from either Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET or Programming Microsoft Visual Basic .NET for Microsoft Access Databases.

Copyright 2003 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.