Beginning Access 1002002 Vba [Electronic resources] نسخه متنی

When you have No GUI

There are several third-party tools available on the Internet ranging from freeware to expensive proprietary solutions. However, all is not lost and there are ways in which you can work with both SQL Server and MSDE via code.

SQLDMO

SQL Distributed Management Objects (SQLDMO) is an object model we can reference from an ADP to work with SQL Server and MSDE. In order to work with this object model, you must set a reference to it in your project. We'll show you how to do this now.

System Stored Procedures

In addition to SQLDMO there are many system stored procedures designed to return information about your server and its databases. System stored procedures are supplied with SQL Server and can be executed from the client interface. Where appropriate, we will demonstrate both the SQLDMO method and the system stored procedure when working with database objects.

All system procedures are prefixed as

sp_procedurename and are stored in the master database on SQL Server.

Security Procedures

System Management

To view a full range of system stored procedures, please see Books online.

To Set the Reference to SQLDMO

Open a code

Module.






In the VBE, select

Tools | References.






Select

Microsoft SQLDMO Object Library and check the checkbox.





Click

OK to set the reference.

That's it - the reference is set and you're ready to go.

In the remainder of this chapter, we will show you how you can:

Connect to SQL Server





View databases and database objects





Amend database objects





Try It Out-Connecting to SQL Server and Displaying the Available Databases




In this example, we will create some code to connect to SQL Server and list all the available databases.





Enter the following into the code window:


Dim IceServer As New SQLDMO.SQLServer 
Dim dbs As New SQLDMO.Database 
IceServer.LoginSecure = True 
IceServer.Connect "HOME" 
For Each dbs In IceServer.Databases 
Debug.Print dbs.Name 
Next 








Important

Remember to set a reference to Microsoft SQLDMO library. Note that the name of the server may differ.








Running the above code will list the available databases on the

HOME SQL Server to the

Immediate window.







How It Works


The

Dim statements should be familiar by now, so we will look at the other new items:

IceServer.LoginSecure = True




Because we're using Windows Integrated security we tell the procedure this by setting the

LoginSecure property to

True . If we were using SQL Server Security, then we would have to change the syntax and pass a

username and

password to the procedure. For example:

IceServer.connect ,servername,username,password







Many book examples show a blank password value for examples like this. Do not leave passwords blank on any installation of SQL Server. Many hackers use this blank password as a means to get into the server. In addition users have been known to play around with logins. You do not want an unauthorized user to login into your server as an

sa or system administrator. Unless of course you like working weekends!





Using the

Connect property of the

Server object, we pass the name of our SQL Server to the procedure:

IceServer.Connect "HOME"




The next statement uses a

For

Each loop to iterate the available databases on the named server, printing them out to the

Immediate window:

For Each dbs In IceServer.Databases
Debug.Print dbs.Name




We then use the

Next statement to move through the available databases in the collection and print the name of each out:

Debug.Print dbs.Name




Within an ADP we can also execute a system stored procedure, which will return similar information.




Try It Out-Executing a System Stored Procedure




The

sp_databases system stored procedure returns information on the databases on a SQL Server. The procedure will return the database name, database size, and remarks. This data is held within the SQL Server system tables. For this example, we are simply going to execute the stored procedure from within another stored procedure. From the Database window:





Click

Queries.






Click

New.






Select

Create Text Stored Procedure.






Replace the template with the following:

ALTER PROCEDURE usp_listtables 
AS 
Exec sp_databases 








Execute the procedure responding to the

Save As... prompt.







In addition to listing the databases, we can also view the tables in a specific database. Again we will use both methods to view tables and their columns.




Try It Out-Listing Columns and Tables




Again we will first use SQLDMO to list the tables in the

Icecream SQL Server database. The code to do this is very similar to that used to list the databases. The only changes we need to make are to loop through the table collection for a specific database rather than loop through the database collection:

Sub listtables() 
Dim IceServer As New SQLDMO.SQLServer 
Dim IceTable As New SQLDMO.Table 
dbName = "Icecream2002SQL" 
IceServer.LoginSecure = True 
IceServer.Connect "HOME" 
For Each IceTable In IceServer.Databases(dbName).Tables 
If IceTable.TypeOf = SQLDMOObj_UserTable Then 
Debug.Print IceTable.Name 
End If 
Next 
End Sub 




How It Works


As before we are using a trusted connection to SQL Server:

IceServer.LoginSecure = True




Then connecting to the server known as

HOME :

IceServer.Connect "HOME"




We then use a

For Each loop to iterate through the tables. In doing so, we use the

SQLDMOObj_UserTable property to check that the table is a user table and not a system table. If it is a user table, we print it out to the debug window.

When executed, the above code will list all the user tables within the Ice Cream SQL Server database to the

Immediate window.







SQLDMO and Security




Not only can we work with tables and columns, we can also use SQLDMO to work with security.




A Note on Security




When you leave the world of Jet behind, you also leave behind Microsoft Access's security model and move into the world of SQL Server security. SQL Server security is much more sophisticated than that of Access, but also has much in common with it. Security on the server is multi-layered and comprises:

Logins




Users must have a valid login to the server. This can be either a Windows login or a SQL Server login. Windows Authentication is supported by default. When working in a total Windows Operating System environment this is the recommended way to proceed; however, some concerns have been expressed about how connection pooling is handled by SQL Server, which can lead to performance problems.




Users




Once given permission to access the server, you must then create a user with specific database permissions.




Roles




Roles are defined job functions within SQL Server. For example, all clerical staff could be assigned to a clerical role. If you are using Windows security with SQL Server, than it is possible to assign a Windows group to a role.

SQL Server 2000 can use two types of security model:





Windows Authentication, which maps all users and groups to Windows accounts





A mixed model, where you can use both Windows and SQL Server security to manage security





For all examples in this chapter, we will be using Windows Authentication.







Important

A full discussion of security is outside the scope of this chapter, but it is an area you must get familiar with before you make any application live. SQL Server Books Online provides information on the various security issues and the latest security information can be found at http://microsoft.com/sql/default.asp .





Try It Out-Adding a Login to SQL Server




Creating the login and user with SQLDMO is a three step process:





Connect to the server





Create a new login





Add the new login to the server









Enter the following procedure into a new module in the module window. Remember you need a reference set to SQLDMO for this to work.

Sub IceLoginAdd() 
  Dim IceServer As New SQLDMO.SQLServer 
  Dim newlog As SQLDMO.Login 
  Dim login_name As String 
  Dim password As String 
  Dim default_databse As String 
  default_database = "Icecream2002SQL" 
  password = "mypassword" 
  login_name = "Ice1" 
  IceServer.LoginSecure = True 
  IceServer.Connect "HOME" 
  Set newlog = New SQLDMO.Login 
  newlog.Name = login_name 
  newlog.Database = default_database 
  newlog.SetPassword ", password 
  IceServer.Logins.Add newlog 
  Set newlog = Nothing 
  IceServer.Disconnect 
  Set IceServer = Nothing 
End Sub 










How It Works


The first step is to dimension all our variables before use. This process should be very familiar to you by now:

Dim IceServer As New SQLDMO.SQLServer
Dim newlog As SQLDMO.Login
Dim login_name As String
Dim password As String
Dim default_databse As String




The next line may, however, be new to you:

default_database = "Icecream2002SQL"




When they login into SQL Server, users are given access to a default database. In this case, the default database for this login is our upsized

IceCream database. Failure to set a default database could lead to the user accessing the master database used to manage SQL Server. You need to be careful that this does not happen.

Next we assign the password and login for this user to the variables

password and

login_name:


password = "mypassword"
login_name = "Ice1"




Note that plain text has been used for the password for illustration only. When creating passwords in SQL Server use strong passwords, that is, a mixture of text and numbers.

Finally we create the new login with password:

Set newlog = New SQLDMO.Login
newlog.Name = login_name
newlog.Database = default_database
newlog.SetPassword ", password




Once done, we clean up after ourselves by resetting the server and closing the connection. That's the login created. Now we need to create a user for the database. In this case, we are going to add our new login

ICE1 to the database as a user.

The above example uses Mixed Security to establish the login. However, it's also useful to know how to carry out the same procedure for Windows Authentication. In this next example, we will use most of the same code, only this time we will create a login for an existing Windows User. Before we look at adding users, let's have a very quick overview of database roles.




Database Roles




Roles within SQL Server allow you to group individuals who require the same permissions into specific named roles. For example, you may have a group of users who can only read data from the database, or others who have both read and write permissions. You could simply group your users together and assign them to a role with read-only permissions. However, this gives you very little flexibility, unless, of course, this is a requirement for that group of users. In addition, if you have a low number of users you may find that creating a role for, for example, two of the users to be too much bother, and it is much simpler to give permissions to individual user. Database Roles are particularly useful when you have a large number of users and they make the management of permissions on database objects very straightforward.

SQL Server 2000 comes with some database roles already in existence. We will be using these for our examples. The default roles are:






































Role	Comment
db_owner	Has the highest level of control on all databases
db_accessadmin	Can manage user and group security
db_datareader	Can read data from all tables within a database
db_datawriter	Can change and add data to the database
db_ddladmin	Can change or remove objects
db_securityadmin	Can manage database roles and permissions on objects
db_backupoperator	Can backup the database
db_denydatareader	Cannot select data
db_denydatawriter	Cannot change data




Once we create a login to the server, we can then create a user account for the specific database and assign our user to one of the existing database roles. In order to create the new user, we need to create a new user object and add the user to its collection. Once this is done, we then add our user to one of the fixed database roles. This fits into the three-tier model of security: create the login to the server, then create the user, and assign the user to a specific database. Once we grant the user permission to access the database, we must then provide access to the objects within that database. In this case we are doing that via a database role.







Important

All database users are by default members of the public role and you cannot delete this role. What you can do, however, is to set permissions for this role. In that way a basic set of permissions on database tables and objects is available by default for every user added to the database.





Try It Out-Create the Whole Works




For this example we are going to:





Create a server login





Create a user





Assign the user to a database





Assign the user to a default database role









Right let's go! Enter the following code into a module:

Public Sub Createsecurity() 
  Dim databasename As String 
  Dim loginname As String 
  Dim username As String 
  Dim DbRole As String 
  Dim default_databse As String 
  Dim iceserver As SQLDMO.SQLServer 
  Dim logIn As SQLDMO.logIn 
  Dim IceUser As SQLDMO.User 
  'Connect to the Icecream Databse on SQL Server 
  'We are using Windows security so we set login to true 
  Set iceserver = New SQLDMO.SQLServer 
  iceserver.LoginSecure = True 
  iceserver.Connect "HOME" 
  'Make sure the new login is to the correct default database 
  default_database = "Icecream2002SQL" 
  'Add the login 
  Set logIn = New SQLDMO.logIn 
  logIn.Name = "Martin" 
  logIn.SetPassword ", (reid01) 
  iceserver.Logins.Add logIn 
  'That's the login to the database server created. 
  'The default database is set to the IceCream example 
  'Now we must create the user account 
  Set IceUser = New SQLDMO.User 
  IceUser.Name = "MReid" 
  IceUser.logIn = logIn.Name 
  iceserver.Databases(default_database).Users.Add IceUser 
  'Now assign the user to a fixed database role 
  'Set up our user to be a data reader only 
  DbRole = "db_datareader" 
  iceserver.Databases(default_database).DatabaseRoles(DbRole).AddMember 
  IceUser.Name 
End Sub 










How It Works


We have already seen the login code, and the details on connecting to the database. We shall therefore only look at creating the user, and assigning them to a database role. This is done with the following code:

Set IceUser = New SQLDMO.User
IceUser.Name = "MReid"




The first line of this code adds the user to the

Users Collection of the database. We then assign the username

MReid to the new user. The following two lines then assign the user to one of the fixed database roles, in this case

db_datareader, which permits the user to read data from any table within the Ice Cream SQL database.

IceUser.logIn = logIn.Name
iceserver.Databases(default_database).Users.Add IceUser