Creating and Opening Access Projects
There are three ways to open Access projects. First, you can open an existing Access project with a link to a SQL Server database. This is typically the way users work with Access projects. Second, you can create a new Access project to connect to an existing SQL Server database. Third, you can create a new Access project and concurrently create a new SQL Server database for it.
| Note | This book often uses the term SQL Server database to refer generically to a database maintained by one of the versions of SQL Server 2000 or MSDE 2000. |
Opening an Existing Access Project
An existing Access project is stored in an .adp file just like an Access database file is stored in an .mdb file. You can find an existing Access project to open through the File, Open command. This command presents the Open dialog box. Specify files of type Microsoft Access Projects to search for .adp files. If you previously compressed an .adp file to an .ade file like an .mdb file to an .mde, then you can specify ADE Files as the file type. See the "Using .mde Files" section in Chapter 10 for more detail on compressed files.
If you opened an Access project recently, you can click on its name in the Open section of the Getting Started task pane. The Open section depicts the most recently used files as links along with another link, named More, which presents the Open dialog box. See Chapter 8 for more programming samples that manage the task pane.
Figure 11-2 shows the Database window for the NorthwindCS Access project. This project is a sample file that you can load with the Office 2003 setup installation program. You should have MSDE 2000 or SQL Server 2000 on your workstation before loading the NorthwindCS sample. This is because the sample Access project installs the NorthwindCS database the first time that you open it if no other version of the Northwind database exists on the local SQL Server machine. In any event, you must have a version of either the NorthwindCS or Northwind database installed on the local SQL Server machine for the NorthwindCS Access project file to open successfully the first time. After the Access project file connects to a database on a server, you can open it from another computer or you can make a copy of the Access project file and then open that copy from another computer.
Figure 11.2: The Database window for the NorthwindCS Access project.
| Note | If the NorthwindCS project file does not successfully open the first time because you do not have SQL Server installed on the current computer, you can manually specify the project's connection to a SQL Server database on another computer. Use the File, Connection command to open the Data Link Properties dialog box. Then complete the dialog box by typing the remote server's name, your authentication information, and the name of the database to which you want to connect the project. |
Do not confuse the reference to the Northwind database in the preceding paragraph with a reference to the Access Northwind database file. When you install SQL Server 2000, the setup program automatically installs a Northwind database. This SQL Server database has the same data tables as the Access Northwind database file. However, it does not have all the same queries. The SQL Server Northwind database contains no forms, reports , or Web pages because these are not SQL Server database objects. The Database window shown in Figure 11-2 has its Queries class selected in the Objects bar. Notice that the list of objects shows two types of icons next to the object names . The icon comprised of two datasheets represents views. The icon depicting text on a page represents stored procedures. In T-SQL, views return the output from a single SELECT statement that has no parameters or ORDER BY clause, unless the SELECT statement includes a TOP clause. Although stored procedures enable this function, they also permit parameters and an ORDER BY clause without a TOP clause, as well as some additional capabilities. If the NorthwindCS database contained any user-defined functions, they would appear in the Database window shown in Figure 11-2. Just before the listing of objects, you'll notice three list items for launching wizards that permit you to graphically design views and some types of stored procedures and user-defined functions. We'll take a closer look at these components later in the chapter, in the sections, "SQL Server Views," "Creating and Using Stored Procedures," and " User -Defined Functions."
When you use Access projects in a multiuser application, every user should have his or her own copy of the Access project file. This is because Access opens these files exclusively. If two users open the same Access project, the second user to open the file gains read-only access. Recall that the Access project file is the client component of a client/server application. Every user needs his or her own copy of the client component. The sharing takes place from the server component.
Opening an Access Project for an Existing Database
To open a new Access project for an existing database, click the Project Using Existing Data link on the Getting Started task pane. If you do not see the link, click the More link. The Task Pane opens by default when you start Access 2003. Clicking the Project Using Existing Data link opens the File New Database dialog box (see Figure 11-3). Access assigns a default name to the Access project file in the style of adp x .adp. You can override adp x with a custom name of your choice, and you can change the destination folder for the file by changing the selection in the Save In drop-down list. By clicking Create in the dialog box shown in Figure 11-3, the user creates an Access project with a filename of adp2.adp in the My Documents folder.
Figure 11.3: The File New Database dialog box in an Access window immediately after a click on the Project Using Existing Data link on the Getting Started task pane.
At this point, the project has no connection to a database. Therefore, Access automatically opens the Data Link Properties dialog box. Fill in the server name, user authentication information, and database name to link the project to a database. Figure 11-4 shows a completed Data Link Properties dialog box that connects an Access project to the Northwind database on the OfficeDeveloper SQL Server instance on the cab2000 server with Windows NT integrated security. Windows NT integrated security becomes particularly convenient when organizations can readily manage a single login account for Windows and SQL Server.
Figure 11.4: The Data Link Properties dialog box for connecting a new Access project to the Northwind database on the cab2000 server.
SQL Server supports the installation of multiple SQL Server instances on a single computer. The first installed SQL Server is the default installation. Its name is the name of the computer, such as the CabSony1 SQL Server on the CabSony1 computer. Subsequent installs of other SQL Server editions or versions, such as MSDE 2000 installed after the SQL Server Standard Edition, take a two-part name where a backslash (\) delimits each part of the name. In Figure 11-4, the default instance is cab2000, and the nondefault instance to which the Data Properties dialog box refers is the cab2000\OfficeDeveloper. This edition of SQL Server 2000 is the one that shipped with the Microsoft Office XP Developer Edition. Since I did not install the NorthwindCS database on this SQL Server instance, I pointed the Access project at the Northwind SQL Server database.
If you fail to specify the Data Link Properties dialog box entries correctly or you don't complete the dialog box, the new Access project will be disconnected from a data source when it opens. An Access project can be disconnected from a data source upon opening because your server is stopped . Once you get the Access project to connect to a server, you can open the Data Link Properties dialog box with the File, Connection command and respecify its settings.
Manually Opening an Access Project for a New Database
Perhaps the most powerful way to open a new project is to open one that creates a new SQL Server database at the same time. To do this, click the Project Using New Data link on the New File task pane. This opens the File New Database dialog box. Use the dialog box to designate the destination folder and the filename for the new Access project. For this example, I named the file Chapter11 and clicked Create. Access automatically added the .adp file extension so that the file name is Chapter11.adp.
Except for clicking the Project Using New Data link, this process is identical to the one for creating a new Access project to connect to an existing database. After you click Project Using New Data, Access presents the initial dialog box for the Microsoft SQL Server Database Wizard. This dialog box enables you to specify the parameters for a new SQL Server database. These parameters include the name of the server on which you will store your new database, the type of authentication you will use for the SQL Server user ID that creates the new database, and the name of the new database.
Figure 11-5 shows an example of a completed dialog box for creating a new database. This dialog box specifies the local server. This example assumes you have SQL Server or MSDE 2000 running on the local server. The drop-down list that initially displays "(local)" enables you to select the name of any other SQL server to which you are connected. To connect to an instance of MSDE 2000 running on a Windows 98 computer, you must type the name of the server into the box. Notice that you can use either a trusted connection or an account with CREATE DATABASE privileges on a server. Your Windows account has these privileges if you install SQL Server on your computer with the default selections. This example selects the Use Trusted Connection check box, which designates the Windows account of the person attempting to create the database. Chapter 10 will discuss SQL Server security, including accounts and permissions. The database creation wizard automatically names the database after the Access project by appending a suffix of SQL to the Access project name. In this case, the resulting name for the new database is Chapter11SQL. You can override this default name with any other you prefer.
Figure 11.5: The initial dialog box of the Microsoft SQL Server Database Wizard.
After completing the entries referenced in the preceding paragraph, click Next on the initial wizard dialog box. Then click Finish on the second wizard dialog box. This creates the new database for the new Access project. When the process completes, your new Access project opens with a connection to the newly created SQL Server database on the server indicated in the Microsoft SQL Server Database Wizard dialog box for the database creation wizard.
Programming an Access Project for a New Database
You can also program the creation of a new SQL Server database and link it to an Access project without using the Microsoft SQL Server Database Wizard. Managing this process programmatically allows you to automatically create Access projects with attached databases. In addition, enabling the task programmatically allows you to fine-tune the process so that it meets your clients ' precise requirements.
Creating a SQL Server database for an Access project requires two distinct steps. First, you need to create a new database. Then you need to assign that database to an Access project. This second step is analogous to entering the database settings into the Data Link Properties dialog box, and it creates the OLE DB connection between an Access project and the SQL Server database. You can also persist your changes to an Access project on a hard drive.
The following four procedures comprise an application for creating a new SQL Server database and assigning it to the current project. The first procedure, CreateDBForAccessProject , is the main routine. It designates the database name and a path for the database files ”namely, its .mdf and .ldf files. The second procedure, CreateDBOnCurrentProjectServer , uses the information the first routine passes to it to create the database files. The third procedure, AssignConnectionForNewDB , assigns the new database to the current project. The fourth procedure, PersistNewAccessProject , is called from the third procedure. This fourth procedure creates a new Access project file in a path and assigns a connection pointing at the new database created by the CreateDBOnCurrentProjectServer procedure.
| Note | The .mdf file type is the primary file for a SQL Server database. In many applications, this file holds all the database objects in a SQL Server database, but SQL Server does permit other secondary database files to hold database objects. The .ldf file stores changes to a SQL Server database. The .ldf file can be used to recover a database. Each database must have a .mdf file and a .ldf file. |
As you can see, the first procedure assigns the name foo2 to the new database. This name must conform to SQL Server conventions for identifiers as well as to a special restriction for database names in the CREATE DATABASE T-SQL statement. Your database name must be no more than 123 characters . The path can follow standard Windows path requirements. After assigning the database name and path to the database files, the main routine passes both parameters to the procedure for creating a new database but passes only the database name to the procedure.
The second procedure in the following code invokes the CREATE DATABASE T-SQL statement with an ActiveX Data Objects (ADO) Connection object. The procedure accepts the database name and path for the creation of the .mdf and .ldf files. You aren't required to store database files in the default directory, but it's a good practice that makes finding your database files easy. The CREATE DATABASE statement creates a database based on the SQL Server model database. The model database is one of the ones that SQL Server sets up upon installation. If you have special requirements for your databases, you can modify the model database's design. For example, you can create a special table design or even a special table that is populated with preset values. The sample procedure accepts the default model database settings for the initial database size, its maximum size, and the amount that the database grows when nearing its current size limit.
 |
Every object in SQL Server, including a database and its elements, can have an identifier. The identifier is the name by which you reference the object. Identifiers have four general rules, and individual objects and special settings can further impact the valid names for SQL Server objects.
The first rule states that an identifier must begin with a letter from the Unicode Standard 2.0. These include but are not limited to the uppercase and lowercase letters of the English alphabet and those of other languages. It's good practice to avoid starting identifiers with characters that SQL Server uses for special purposes ”such as the underscore (_), at sign (@), pound sign (#), double at sign (@@), and double pound sign (##) ”unless you explicitly mean to reference the special purpose. For example, @ is a prefix for denoting a local variable in a stored procedure. A local variable in a stored procedure functions similarly to a memory variable that's local to a single procedure in Microsoft Visual Basic for Applications (VBA). In some contexts, starting a table name with @ invites SQL Server to treat the table as a local variable instead of a table.
The second rule for identifiers is that all characters after the first one can be letters, decimal numbers, or any of the following symbols: @, $, #, or _. Again, the letters and numbers are specified by the Unicode Standard 2.0. Third, you cannot specify object identifiers that are SQL Server reserved words. This page on the Microsoft Web site lists the SQL Server 2000 reserved words: http://msdn.microsoft.com/library/psdk/sql/ts_ra-rz_9oj7.htm . And finally, identifiers cannot contain embedded spaces or other special characters.
You can circumvent some of these rules by using double quotes or brackets, but you will create more universally acceptable identifiers by following all four rules.
| |
The third procedure uses the BaseConnectionString property for the current project as a basis for specifying the connection string for a project connected to a new database ”namely, the one created by the second procedure. A significant advantage of this approach is that it doesn't need to specify a valid connection string from scratch for an Access project. The "Initial Catalog" setting in the connection string points at the database name. Therefore, the procedure excises "Initial Catalog" and its setting and replaces the connection string with a setting that points at the new database. After composing the new connection string in str1 , the procedure invokes the OpenConnection method for the CurrentProject object. The method takes a single argument, which is the name of the string with the edited BaseConnectionString property value.
In its last line, the third procedure calls the PersistNewAccessProject procedure while passing the edited BaseConnectionString property value as str1 . This fourth procedure uses automation to launch another Access session. Then, with the NewAccessProject method, the procedure persists the session to disk as an Access project pointing at the database specified within str1 .
SubCreateDBForAccessProject() DimDBNameAsString DimDBFilenameAsString DimLogFilenameAsString DimDBFilePathAsString 'Setstringconstants DBName= "foo2" DBFilePath= "c:\programfiles\microsoftsqlserver\mssql\data\" 'Createadatabase CreateDBOnCurrentProjectServerDBName,DBFilePath 'Openthecurrentprojecttothedatabase AssignConnectionForNewDBDBName EndSub SubCreateDBOnCurrentProjectServer(DBNameAsString,_ DBFilePathAsString) Dimcnn1AsADODB.Connection Dimstr1AsString 'AcceptModelDBdefaultsforinitialsize, 'maximumsize,andgrowthsteps str1= "CREATEDATABASE " &DBName& " " &_ "ON " &_ "(NAME= " &DBName& "_dat, " &_ "FILENAME='" &DBFilePath&DBName& "dat.mdf') " &_ "LOGON " &_ "(NAME='" &DBName& "_log', " &_ "FILENAME='" &DBFilePath&DBName& "log.ldf')" 'PointConnectionobjectatcurrentprojectand 'executeSQLstringinstr1 Setcnn1=CurrentProject.Connection cnn1.Execute(str1) EndSub SubAssignConnectionForNewDB(DBNameAsString) Dimstr1AsString DimBeginposAsInteger DimEndposAsInteger 'SaveBaseConnectionStringforediting str1=CurrentProject.BaseConnectionString 'Optionalprintouttoseethe line being edited; 'removecommentprefixtoseeline in the Immediate window Debug.Printstr1 'Extractstartingandendingpositionsfordatabaseassignment 'intheBaseConnectionString Beginpos=InStr(1,str1, "InitialCatalog",1) Endpos=InStr(Beginpos,str1, ";") 'Replacetheolddatabaseassignmentwithanewone str1=Left(str1,Beginpos-1)& "InitialCatalog=" &_ DBName&Right(str1,Len(str1)-Endpos+1) 'PassupdatedconnectionstringtoCurrentProject 'andpersistedAccessproject CurrentProject.OpenConnectionstr1 PersistNewAccessProjectstr1 EndSub SubPersistNewAccessProject(str1) Dimobj1AsAccess.Application 'InstantiateAccesssession Setobj1=CreateObject("Access.application") 'SaveAccesssessionasAccessprojectpointing 'atstr1 obj1.NewAccessProject "c:\access11files\foo2",str1 'Cleanupobjects obj1.Quit Setobj1=Nothing EndSub The preceding sample uses specific paths and file names that are written into the code, so as not to obscure the main points of this section on creating databases with T-SQL and managing connections for current and new Access projects. You probably will need to edit one or more hard-coded settings for the procedure to work in your environment. In addition, after you run the sample once, it will fail if you try to run it again. This failure stems from all the changes to settings that the sample makes. You can program around these failures, but that just adds to the sample's length while obscuring its main point. As I tested the application, it became useful to reset the settings altered by the sample. The following procedure illustrates logic for restoring the settings. First, it makes the current project point back at the Chapter11SQL database instead of the foo2 database. Second, it kills the persisted Access project file. Third, it drops the foo2 database from the SQL Server for the current project. After running ResetFromCreateDBForAccessProject , you will be able to rerun the initial sample successfully. Even if you don't rerun the initial sample, the Chapter11.adp file will point at the database used for most of the samples throughout the remainder of this chapter.
SubResetFromCreateDBForAccessProject() Dimstr1AsString DimBeginposAsInteger DimEndposAsInteger Dimcnn1AsADODB.Connection 'ResetCurrentProjecttoChapter11SQL str1=CurrentProject.BaseConnectionString Beginpos=InStr(1,str1, "InitialCatalog",1) Endpos=InStr(Beginpos,str1, ";") str1=Left(str1,Beginpos-1)& "InitialCatalog=" &_ "Chapter11SQL" &Right(str1,Len(str1)-Endpos+1) CurrentProject.OpenConnectionstr1 'KillcreatedAccessprojectfile Kill "c:\access11files\foo2.adp" 'Dropcreateddatabase Setcnn1=CurrentProject.Connection cnn1.Execute("DROPDATABASEfoo2") EndSub 
EAN: 2147483647
Pages: 144