3.2. Working with Various Databases
You've read about flat file and relational databases and now know the differences. There is some additional information you need to know about the available relational databases and their platforms. Before talking about Microsoft Access and SQL Server specifically, there is some terminology you need to get comfortable with.
3.2.1. File Server versus Client Server
File server databases are those where the database is stored in a folder on a file server. When you access the database, all the data is brought over the network and locally processed. Microsoft Access is a file-server-based database system.
Client/server databases are stored on a server, but when it comes time to process the data, the processing is performed on the server, and just the necessary data is brought over the network. Microsoft SQL Server is a client/server product.
With most development environments, how you develop against the two types of database platforms will vary. With InfoPath, you use the same methods for both platforms.
3.2.2. Front and Backends
When working with database applications, you have front- and backends. The database containing the data is the backend. The application created to control the input and output of the data is called the frontend. The application contains forms, reports, and other programming elements. When connecting to a database, the data source, such as Microsoft Access or SQL Server, is the backend and the InfoPath form is the frontend.
The next sections describe the two databases that Microsoft InfoPath connects to directly: Microsoft Access and Microsoft SQL Server.
3.2.3. Microsoft Access
Perfect for small to medium-sized solutions when used as a backend, Microsoft Access is a popular database system with thousands, if not millions, of installations. Access can also be used as a frontend, but that is a topic for another book. You can see Access with the Coffee.mdb open with tblCustomers displayed in Figure 3-5.
Figure 3.5. Figure 3-5
3.2.3.1. Benefits and Issues of Microsoft Access
There are quite a few benefits to using Access as opposed to other database products. Some of the positive aspects of Access are:
Established Application: Access has been around for quite a few versions, with Microsoft enhancing the product with each version. The current version is Microsoft Access 2003.
Powerful Report Writer: It includes a banded report writer, with bands set up for Report Headers/Footers, Group Headers/Footers, Page Headers/Footers, and Detail. You can embed reports within reports. The Access report writer is commonly used by other products, including Visual Basic and SQL Server, to create reports.
Can Be Used as a Frontend and/or Backend: It is almost as common to find Access used as a frontend for a SQL Server database as it is to see it used strictly with Access. It can be used with SQL Server by linking tables in a *.mdb or by using an Access *.adp, which is a database project specifically set up to be a frontend for a SQL Server database.
Macro Language for Beginners and VBA for Developers: Access provides a powerful forms package with the Visual Basic for Applications (VBA) development language behind it, or a macro-type language.
Easy-to-Transfer Database Files: Just by using Windows copy and paste, you can move or copy Access databases locally over a network, or you can even store the database in a compressed folder and e-mail the folder.
There are some issues that can arise when using Access for your databases:
Large Databases Bog Down: You can run into problems with large databases, if those databases are not carefully created.
Large Number of Users Bog Down the System: You need to be very careful when creating an application for a large number of users, as the application can bog down when users are querying and updating data. This is especially true with large databases, as mentioned in the previous bullet.
Form Designer Can Be Confusing to Use: As powerful as they both are, the form and report designers in Access can be confusing to use when moving beyond the basics.
Not Built for Use with the Internet: Because Access is a file server product and not meant for the truly high volume you get when using database over the Internet, Access is made more for use on a local area network (LAN).
Access works very well with InfoPath as a backend when you have a limited number of people accessing the data. Access is also a great database to prototype applications in, for later use in another development language such as Visual Basic with the data moved to SQL Server. You also can also use the Access database from other products without having to have a version of Access installed, which makes Access even more worthwhile to use with InfoPath.
3.2.3.2. Microsoft Access Objects Used with InfoPath
With InfoPath, you will be using the following objects in Access:
Queries: Used to recombine data using Select statements; also provide a means of adding, updating, and deleting data from tables
3.2.4. Microsoft SQL Server
Built for small to enterprise-wide databases, SQL Server is designed for use with other development products and includes no form management tools of its own.
You can see SQL Server's Enterprise Manager with the CoffeeSQL database open in Figure 3-6.
Figure 3.6. Figure 3-6
3.2.4.1. Benefits and Issues of Microsoft SQL Server
As with Microsoft Access, there are a number of benefits to using Microsoft SQL Server. Some of those benefits are:
Established Application: As with Access, SQL Server has been around for quite a few versions. At the time of the printing of this book, SQL Server 2000 is the current version.
Robust Set of Client Tools for Data Management: Headed by Enterprise Manager and Query Analyzer, SQL Server has a number of tools that help you to manage data in your databases.
Extensive SQL Language for Data Manipulation: Using Transact SQL you can create stored procedures that can manipulate data in just about any way necessary. Also available is Data Transformation Services (DTS), which enables you to create packages to schedule tasks for working with the information in your database.
Large Amounts of Data Can Be Handled: SQL Server is made for large amounts of data. On a server that has been properly set up, you can store many gigabytes of data. In addition to residing on the proper system, the data needs to be normalized and care taken when creating views and stored procedures.
Works Well with the Internet: Because SQL Server works well with large databases and a large number of users, it works well as a database for use with the Internet. Of course, you do need to be conscientious when creating the database.
There are some issues that can arise when using SQL Server for your databases:
Tools Are Not End-User-Friendly: Where Access's tools are built for end-user use, SQL Server's tools are made for use by system administrators. So while the tools are powerful, they are not very friendly.
No Form Management Tool: You will have to use other products as frontends to SQL Server databases, because it doesn't have any type of forms management. There is now a reports management tool available.
Not Convenient for Transferring Databases: While it can be done, you need to use the tools available for the system administrators for detaching/attaching databases to SQL Server, or use code. You also can back up and restore databases, again using a tool or code. Once these procedures are done, you can then copy and paste the files using Windows commands.
SQL Server works well using InfoPath for the frontend, and is easy to connect to databases for data sources.
3.2.4.2. Microsoft SQL Server Objects Used with InfoPath
When using SQL Server as a backend to InfoPath, you will be using the following objects in Microsoft SQL Server:
Tables: Used to store data. You can see the tblCustomers table displayed using the SQL Server Enterprise Manager in Figure 3-7.
Figure 3.7. Figure 3-7
Views: Used to recombine data using Select statements, views allow you to display data as needed in various ways.
Stored Procedures and Functions: Provide means for adding, updating, and deleting data from tables.
There is a version of Microsoft SQL Server called the MSDE, which stands for Microsoft SQL Server Desktop Edition. This version of SQL Server comes with various products such as Visual Studio .NET, and can be redistributed for use with systems that don't have the full version of SQL Server installed.
3.2.5. Other Databases
When developing InfoPath forms and wanting to work with other databases such as Oracle, Informix, and others, you have to use Web services to connect to them. You can read a brief introduction on Web services in the section called "Introduction to Web Services," found later in this chapter. Developing Web services is discussed in Chapter 15, "Creating and Working with Web Services."