The example near the end of Chapter 18, "Text File Processing," proposed a method for storing 650,000 records in an Excel worksheet. At some point, you need to admit that even though Excel is the greatest product in the world, there is a time to move to Access and take advantage of the Access Multidimensional Database (MDB) files.

Even before you have more than 65,000 rows, another compelling reason to use MDB data files is to allow multi-user access to data without the headaches associated with shared workbooks.

Microsoft Excel offers an option to share a workbook, but you automatically lose a number of important Excel features when you share a workbook. After you share a workbook, you cannot use automatic subtotals, pivot tables, group and outline mode, scenarios, protection, Autoformat, Styles, Pictures, Add Charts, or Insert worksheets.

By using an Excel VBA front end and storing data in an MDB database, you have the best of both worlds. You have the power and flexibility of Excel, and the multi-user access capability available in Access.

ADO Versus DAO 402

Case Study 403

The Tools of ADO 404

Adding a Record to the Database 404

Retrieving Records from the Database ____406

Updating an Existing Record 408

Deleting Records via ADO 410

Summarizing Records via ADO 410

Other Utilities via ADO 411

Next Steps 414

¡¡j MDB is the official file format of both Microsoft Access and Microsoft Visual Basic.This means that 0 you can deploy an Excel solution that reads and writes from an MDB to customers who do not have Microsoft Access. Of course, it helps if you as the developer have a copy of Access because you can use the Access front end to set up tables and queries.

0 0

Post a comment