Microsoft Office 2000/Visual Basic Programmer's Guide

Building a Solution to Store New Data

Where should you store your data if you have the freedom to design your data storage from scratch? Although there are many options, if you're building an Office-based solution, you're most likely going to store data in a Microsoft Excel workbook, in a Microsoft Access database (.mdb), or on a database server, such as Microsoft SQL Server.

Should You Store Data in Excel or in a Relational Database?

Many people are well-versed with VBA programming in Excel, but are intimidated by relational databases like Access and SQL Server, which require an understanding of relational database design concepts. Some developers end up using Excel to build data-management solutions that would be better off as relational databases.

Excel is best for storing small amounts of data on which you need to perform calculations, or that you want to present in a grid format. It's also good for storing disparate types of information — numeric, text, and graphical data, such as charts, that doesn't easily conform to a particular structure.

For larger data sets, Excel may not be the ideal application for storing the data, but it's a superior tool for analyzing and presenting data that's stored in another format. You can import data into Excel from any OLE DB or ODBC data source, and use Excel's calculation and analysis tools to analyze the data however you choose. For more information about using Excel for data analysis, see Chapter 15, "Retrieving and Analyzing Data."

Here are a few questions to ask yourself when trying to decide whether to use a relational database rather than Excel to store data:

• Does your data duplicate items that could be stored in one place? For example, are you typing a customer's address repeatedly each time you take an order for that customer? If so, you would be better off storing your data in a relational database, where you can enter customer information in a single table and create relationships with other tables that use that data.
  
  
• Do you need to be able to expand the system with more data or more users in the future? If so, it's likely that your solution will become extremely complicated as you add data. Again, you would be better off storing your data in a relational database and using queries to get to the data that you need. A relational database is a better choice for scalability.
  
  
• Do you need to store or archive old data? Access is better suited to this task than Excel. On the other hand, if you regularly replace data without archiving it, Excel may be sufficient for your needs. For example, if your application pulls daily stock quotes from a Web page but doesn't need to track stock histories, Excel is ideal.
  
  
• Does your solution need to be multiuser? If more than one person may need to access the data at the same time, you can take advantage of the built-in multiuser features in Access. Only one person at a time can open an Excel workbook file, so data entry in Excel is limited to one person at a time.
  
  
• Do you need to be able to control and validate data entry? Access is generally better suited for this than Excel. Access provides controls with built-in data binding with which you can easily impose validation rules. You can create bound controls and perform data validation on a UserForm in Excel, but it's more work.

If you decide to store your data in a relational database, you have several options. If you're building either a single-user solution or a multiuser solution for a small workgroup, you can store the data in an Access database (.mdb) or in a Microsoft Database Engine (MSDE) database. Either of these options is available to you if you own Microsoft Office 2000 Professional or Microsoft Office 2000 Premium.

An MSDE database uses a database engine that's similar to the one found in SQL Server, but an MSDE database can't support as many users; best performance is achieved with five or fewer users. The advantage to using MSDE is that you can use it to create a SQL Server database from Access without actually having SQL Server on your computer. It's a good tool for prototyping and designing an enterprise solution that you'll eventually migrate to SQL Server, because you can run an MSDE database under SQL Server without modification. A database server such as SQL Server can support hundreds to thousands of simultaneous users. A properly optimized Access database (.mdb) can support up to 255 simultaneous users, although best performance is achieved with 25-50 users. An Access database is best for small workgroup-based solutions.

For more information about which type of relational database you should use to store the data for your solution, see Chapter 14, "Working with the Data Access Components of an Office Solution," and Chapter 16, "Multiuser Database Solutions."