Share via

Where Should You Store Data?

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Many applications involve storing and managing data at some level. Microsoft's OLE DB technology makes it easier to access data in any format. If you must work with existing data, you can access it directly through OLE DB or through Open Database Connectivity (ODBC). If you are creating an application to store new data or if your customer wants to move existing data into the new application, you can choose the data-storage strategy that makes the most sense for your application.

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 are building an Office-based application, you are 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 comfortable with Microsoft® Visual Basic® for Applications (VBA) programming in Excel but are intimidated by relational databases, such as Access and SQL Server, which require an understanding of relational database design concepts. Some developers end up using Excel to build data-management applications that would be better off as relational databases.

Excel is best for storing small amounts of data on which you must perform calculations, or that you want to present in a grid format. In addition, Excel is good for storing disparate types of information — numeric, text, and graphical data, such as charts, that does not conform easily to a particular structure.

For larger data sets, Excel might not be the ideal application for storing the data, but it is a superior tool for analyzing and presenting data that is stored in another format. You can import data into Excel from any OLE DB or ODBC data source and use the Excel calculation and analysis tools to analyze the data however you choose.

Here are a few questions to ask when trying to decide whether to use a relational database or 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.
  • Must you be able to expand the system with more data or more users in the future? If so, it is likely your application 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 require. A relational database is a better choice for scalability.
  • Must you 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 might be sufficient. For example, if your application pulls daily stock quotes from a Web page but does not have to track stock histories, Excel is ideal.
  • Must your application be multiuser? If more than one person might have to access the data at the same time, you can take advantage of the built-in multiuser features in Access.
  • Do you have to be able to control and validate data entry? Generally, Access is better suited for this than Excel. Access provides controls with built-in data binding with which you can impose validation rules easily. You can create bound controls and perform data validation on a UserForm in Excel, but it is more work.

If you decide to store your data in a relational database, you have several options. If you are building either a single-user application or a multiuser application for a small workgroup, you can store the data in an Access database (.mdb) or in a Microsoft® SQL Server™ 2000 Desktop Engine database.

A Microsoft SQL Server 2000 Desktop Engine database uses a database engine that is similar to the one found in SQL Server, however, a desktop engine database cannot support as many users; best performance is achieved with five or fewer users. The advantage to using the desktop engine is you can use it to create a SQL Server database from Access without actually having SQL Server on your computer. It is a good tool for prototyping and designing an enterprise solution. You eventually will migrate to SQL Server, because you can run a desktop engine 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 to 50 users. An Access database is best for small workgroup-based applications.

See Also

Which Technologies Should You Use? | Workflow: Technology Backgrounder | Designing a Relational Database | Thinking About Data Entry and User Input | Retrieving, Analyzing, and Presenting Data | Building Add-ins, Wizards, and Templates