Part VII: Appendixes
1246
Very few Excel users understand the need to break out “child” data (like phone numbers and
addresses) into separate worksheets to minimize data maintenance. And they shouldn’t be con-
cerned — after all, Excel is a spreadsheet, not a database, even though many Excel users work with
Excel worksheets as if they were database tables.Recent versions of Excel allow more than 16,000 columns in a single worksheet. Inevitably, you’ll
be asked to import a humongous Excel worksheet into access, or to link to an Excel spreadsheet so
reports can be produced and printed in Access. You’ll find that you can’t import or link to Excel
spreadsheets when the Excel column count exceeds 255. Don’t blame Access! Instead, look for
workarounds, such as segmenting the Excel data into named ranges and importing or linking the
named ranges separately. Sure, it’ll be a hassle, but there’s no reason Microsoft should abandon the
performance and utility of a relational database system (like Access) just to accommodate the infre-
quent situation in which Access’s database limits seem unfair or inappropriate.Another common suggestion is to increase the maximum database size beyond the current 2GB
limit. There are several workarounds to this issue, such as linking a front-end database to multiple
back-end databases. Although linking to multiple back-end databases can impose serious perfor-
mance problems on some queries (databases have trouble joining tables from diverse data sources
because relationships can’t be properly established between tables in different database files), care-
fully planning how the data is distributed among the back-end databases should minimize these
issues.And, of course, linking to blob (binary large object) data such as images and video clips, is a much
more efficient use of database space than embedding the data directly into the database.Generally speaking, if you have a database that contains more than 2GB of data that can’t be han-
dled by splitting out as multiple back-end databases or other tricks, it’s probably time to consider
upsizing to SQL Server. Because its database size is 4GB, SQL Server Express might help with the
initial move. The Access upsizing tools are very easy to use and virtually foolproof. And SQL Server
Express is a free download and may be freely distributed with your Access applications.Cross-Reference
Turn to Chapter 38 for more on the Access upsizing tools.Always keep in mind that Access is a file-based database system. The Access database file, whether
it’s an .accdb, an .mdb, or any other type, is just a Windows file. Access databases don’t support
logging, rollbacks, archiving, or other administrative tasks intended to protect a database’s data.
There is a point at which it really doesn’t make good business sense to continue storing vast
amounts of mission-critical data in a file-based database system. Server database systems (like SQL
Server) provide all the tools necessary to properly administer and protect very large amounts of
data.49_475348-bapp01.indd 124649_475348-bapp01.indd 1246 4/1/10 2:01 PM4/1/10 2:01 PM