Desktop and Server Databases

Introduction to Data Import/Export

Introduction

Microsoft Access objects are probably the easiest objects to import because, created with the same application, these objects are already formatted and recognizable. You can import objects of a Microsoft Access type of any version. To import objects from another Microsoft Access database, on the Ribbon, you can click the External Data tab. In the Import section of the Ribbon, click the Access button . This would open the Get External Data - Access Database wizard. You should first locate the folder that contains the database, and then select the database.

Practical Learning:  Importing a Microsoft Access Object

1. Start Microsoft Access and open the Clarksville Ice Cream1 database you created in Lesson 2 
2. On the ribbon, click External Data. In the Import section, click Access
3. In the first page of the Get External Data - Access Database wizard, click Browse
4. Locate the folder that contains the resources for these lessons and select it
5. In the list of files, click Clarksville Ice Cream.mdb and click Open
6. On the wizard, click OK
7. In the Tables property page of the Import Objects dialog box, click Select All
    
   
8. Click OK
9. On the dialog box, click Close

Splitting a Database

Most of the databases we will create in our lessons are referred to as desktop databases because they are meant to be installed in the computer where they will be used. For maintenance purposes, the problem with this approach is that, if you distribute your database to the users or if you have distributed the database to a few people already, if you modify the database, you will have to visit each one's computer to install a new version of the database or to modify the database on each user's computer. For example, imagine that some or all users of the database call you and say that a certain form or report seems to produce a wrong result and you suspect that an expression in the database is false. Once you find out what needs to be done to correct the problem, you would have to visit each computer to make the change. Also, if you want to backup the database, although you need only the values stored in the tables, you may have to visit each computer. Sometimes this is not possible and it is unprofessional. Microsoft Access provides an alternative to this problem.

You can divide your database in two parts. One part would be installed on one computer that can be a server or just another computer. This is referred to as back-end. The other part would be installed in each user's computer. These users computer can then connect to the back-end database that stores the actual database. The users computer have only the graphical interface that allows them to work. If you modify the database (for example if you correct an expression that was producing wrong results), you can do it on the back-end database and the users' computers would receive the change. To implement this scenario, you split your database.

To split a database, on the Ribbon, you can click Database Tools. In the Move Data section, you can click the Access Database button (if you receive a Microsoft Office Access Security Notice, read it and click Open). This would open the Database Splitter dialog box with four paragraphs and two buttons. After reading them, you can click Split Database. This would open the Create Back-End Database dialog box. It would suggest a name of a database that is made of the original name of the database followed by _be. You can keep that name or change it. Once you are ready, you can click Split. The splitting process would start. When the splitting is over, you will receive a message box and you can click OK.

After splitting a database, the tables would be removed from the Navigation Pane. The linking tables would have new icons and each icon would appear with an arrow that indicates that the table is only linked to an actual table in the back-end database. Also, after splitting a database, since the actual tables would have been removed from the Navigation Pane, you cannot modify their structure from a user's computer. That is, you cannot add a column or change the data type of a field.

After splitting the database, you can install the _be file, that was created when splitting, to the server or the computer that will host it. Then install the original database on the user's computers. You should (must) then establish a connection between each user's computer and the back-end server.

Practical Learning:  Splitting a Database

1. From the resources that accompany these lessons, open the Bethesda Car Rental2 database.
   Notice the objects in the Navigation Pane
    
   
2. On the Ribbon, click Database Tools
3. In the Move Data section, click Access Database
    
   
4. You receive a Database Splitter dialog box. Read the paragraphs and click Split Database
5. In the Create Back-End Database dialog box, change the name of the file to bcr5 and click Split.
   When the splitting is over, a dialog box appears
    
   
6. Read it and click OK
7. Open the bcr5 database. Notice the icons of the tables in the Navigation Pane and the absence of the forms
    
   
8. Re-open the Bethesda Car Rental5 database and notice the new icons of the tables in the Navigation Pane
    
   
9. In the Cars section, right-click Cars and click Design View
10. You receive a message box. Read it and click No (if you open one of the forms, create a new record, and open the bcr5 database, you will notice that the new record you created exists)

ACCDE Files

If you have created a database and decide to distribute it or to give it to a customer, colleagues or employees, they may be tempted to open the forms in Design View and possibly modify their structures. There are two disadvantages to such an action. They may compromise or corrupt your hard work, possibly rendering it obsolete. Some bad eye may decide to cheat on your VBA code. Microsoft Access allows you to make some or most of these actions difficult or impossible. An ACCDE file is a database compiled by Microsoft Access so the users of the database cannot change the design of forms or reports, create new forms or reports, view its VBA code, or import forms, reports or VBA code.

Before creating an ACCDE file, you should enable the security. To do this, on the Message Bar, you can click Options... In the Microsoft Office Security Options dialog box, click the Enable This Content radio button and click OK. To create an ACCDE file of your database, after opening it, on the Ribbon, you can click Database Tools. In the Database Tools section, you can click Make ACCDE. You will receive the Save As dialog box with a suggested file. You can accept that file name or replace it. After this, you can click Save.

Microsoft Access and Microsoft SQL Server

Introduction

If you have a Microsoft SQL Server database and you want to use it in a Microsoft Access database, you have various options. You can import the objects from the server database or you can create a Microsoft Access project that is a database whose tables link to the server.

Importing Microsoft SQL Server Tables

If you have one or more tables that are part of a Microsoft SQL Server database but for some reason you do not want, do not need, or are not allowed, to work directly on the database server, you can import the objects to Microsoft Access. You first need to create the recipient database in Microsoft Access.

Microsoft Access treats Microsoft SQL Server as one of the ODBC family of databases (the Microsoft Windows operating system has a driver that allows different database environments to communicate or interact with the same database, as long as they follow some rules of the ODBC driver; for example, an application created with Delphi and a PHP web site both can communicate with a Microsoft Access database or a Microsoft SQL Server database). Therefore, before importing a Microsoft SQL Server table (or a table from any ODBC compliant database), you should first create a data source. You can create the data source from Microsoft Access or you can first create it using Control Panel:

• To start the data source from Control Panel, in Microsoft Windows XP, you can open the Control Panel, open the Administrative Tools, and double-click Data Sources (ODBC). This would open the ODBC Data Source Administrator:

  

  You can then click Add.
• To import a table in Microsoft Access, on the Ribbon, you can click External Data and, in the Imxport section, click More -> ODBC Database. This would open the Get External Data -> ODBC Database dialog box. If you want to import a table, you should accept the first radio button and click OK. This would open the Select Data Source dialog:

  

  If you had already created the data source, you can select it and click OK. If the Data Source is not displaying but you know where it is located, you can click the Up One Level button to locate its folder and select it. Otherwise, you can create a new data source. To do this, click the New button.

In both cases, you would get the Create New Data Source dialog box:

From there, you can click the type of database you want to import. In this case, you would click SQL Server. If you started from Control Panel, you would click Finish. If you had started from Microsoft Access, you can click Next. You would be prompted to enter a name for the data source:

After typing the name, you can click Next and click Finish. Next, the Create a New Data Source dialog box with the name you previously specified. You are allowed to type a small description of the database. You can type anything. Then, in the Server combo box, select the name of the server where the database is located. If the database is in the same computer you are using, you can type (local):

You can then click Next:

You can accept all defaults or, if you are creating a sensitive database that would require people to log in, you can click the second radio button, then provide a user name and a password. When ready, click Next. The next page of the wizard allows you to select the actual database that has the table(s) you want to import. To select it, you should click the top check box to access its combo box. Then click the arrow of the combo box to display the list of databases and select the one you want:

After selecting the database, you can click Next. The last page of the wizard allows you to set some options. Once you are ready, you can click Finish:

To make sure everything is alright, you can click the Test Data Source button. If everything looks alright, you can click OK. If you had started from the Control Panel, the process would end. If you add started from Microsoft Access, the Select Data Source dialog box would come back with the data source you had just created. You can then select it:

And click OK. A dialog box would display, containing all the objects that belong to the database you had selected. The names of the tables start with the schema name, in this case dbo:

Because there are many objects that Microsoft SQL Server creates for each database, you certainly would not need all of them. Therefore, you should select only the table (and probably the views). To select an object, simply click it, then click each of the other objects you would need. Once you are ready, click OK. Microsoft Access would start analyzing and importing the table(s). When the process is over, a dialog box would let you know. You can then click Close. Once you do, the names of the tables would appear in the Navigation Pane. Each starts with the schema name and an underscore. In most cases, you may have to rename each by removing the schema prefix.

Linking to a Microsoft SQL Server Database

Instead of importing the whole database or some of its table from a Microsoft SQL Server application, you can create a project in Microsoft Access that would connect to the table from the server. That way, if you enter data in the Microsoft Access database, the data would be stored on the server. This also makes it possible to create visually good looking objects in Microsoft Access instead of the somewhat less friendly tables of Microsoft SQL Server (in reality, you never want to let users work on the server and in fact, a visual interface in Microsoft Access allows you to exercise a great level of control over what your users can do or cannot do with your database).

To create an application in Microsoft Access that can communicate with the objects stored in a Microsoft SQL Server database, you can link the tables from Microsoft Access to tables of a Microsoft SQL Server database or you can create what is called a database project or simply a project. You have various options.

After creating a database in Microsoft SQL Server, to create tables linked to it, in Microsoft Access, on the Ribbon, click External Data and, in the Export section, click More -> ODBC Database. In the Get External Data - ODBC Database dialog box, click the second radio button:

If you have created a database in Microsoft Access and want to use it in Microsoft SQL Server, you can export it. Before doing this, you must create a database in Microsoft SQL Server. It would also be a good idea to create a data source, although you can create it from Microsoft Access.

To export a table:

• In the Navigation Pane, right-click a table, position the mouse on Export, and click ODBC Database
• In the Navigation Pane, click a table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> ODBC Database

This would open the Export dialog box that asks you to specify the name of the table that will be created in Microsoft SQL Server. By default, the Export dialog box would suggest the same name as the database that was selected:

You can accept it or enter a new one. After specifying the name of the table, click OK. The Select Data Source dialog box would come up. If you had already created a data source, you can select its name. Otherwise, use the New button to launch a wizard that would assist you to create a data source. After specifying the data source of the database that will receive the table, click OK. If everything is alright, the table would be exported. Once the table has been exported, a dialog box would come up to let you know. You can then click Close.

Upsizing a Microsoft Access Database to a Server Database

Microsoft Access has some limitations as compared to Microsoft SQL Server. For example, Microsoft Access has limits on both the size of a database and the number of people who can connect to a back-end database at the same time. If you find out that Microsoft Access is becoming too restraint to accommodate your expanding database, you can upgrade it to a formal server database. That is, you can transfer the Microsoft Access database to a Microsoft SQL Server database where it would enjoy more options.

Before upsizing a database, you should enable the security. To do this, on the Message Bar, you can click Options... If the Microsoft Office Security Options dialog box, click the Enable This Content radio button and click OK.

To upsize a Microsoft Access database, open the database. On the Ribbon, you can click Database Tools. In the Move Data section, click the SQL Server button . This would open the Upsizing Wizard:

The first page of the Upsizing Wizard allows you to specify whether you will use an existing Microsoft SQL Server database or you want to create a brand new one. If you had already created a database on the server and you only want to add the tables (and queries) to it, you can select the first option. In this case, you must create a data source for the existing database, and follow the wizard. If you did not first create the server database, you can accept the second radio. Once you are ready, click Next. If you had selected the first radio button, clicking Next would open the Select Data Source dialog box. If you had selected the second radio button in the first page of the wizard before clicking Next, the second page of the wizard would come up:

The second page of the wizard allows you to specify the name of the server where the database will be installed. You must also specify how you will be authenticated. The bottom section of the page displays a text box with a suggested name of the server. The name is made of the name of the Microsoft Access database you started from and appended with the "SQL" suffix. You can accept that name or change it. Once you are ready, click Next. The wizard would analyze the database and display the names of the tables in the third page of the wizard:

The third page allows you to select what tables should be created in the database server:

After selecting the tables, you can click Next:

The fourth page of the wizard allows you to specify some options that would be applied to the database on the server. After accepting the defaults or making your selections, you can click Next:

In the same way, the fifth page presents some options. Once you have reviewed it, accepted or making changes, you can click Next (or Finish). The last page only lets you know that all information is ready to apply on the server:

You can then click Finish. The wizard would then start creating the database or applying the changes:

When the process is over, a report would be presented to you.

Lesson Summary

MCAS: Using Microsoft Office Access 2007 Topics