• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint


Jet Tables Fail to Upsize

Q1:I receive error messages during the upsizing process, and some tables don't upsize.
A1: The most common cause of failure is the presence of complex Jet or VBA expressions in table or field validation rules. SQL Server has counterparts for many Jet query expressions, but only a few for VBA functions. SQL Server 2000's extended properties accommodate Jet input masks, data display formatting, subdatasheets, and lookup fields. The Upsizing Wizard, however, handles a surprisingly broad range of validation rules. For example, you can upsize with no difficulty the Forms14.mdb application whose HRActions table has several table and field validation rules.

If you adhere to the recommendations in the “Modifying Table Properties to Assure Successful Upsizing” section near the beginning of this chapter, there little probability of encountering table upsizing failures.

MSDE Performance Problems

Q1:Performance of my multiuser Jet applications is significantly slower after upsizing the tables to MSDE 2000 or other SQL Server 2000 editions.
A1: If your MSDE 2000 back end appears to be running out of steam as you add more users, the first step is to optimize your queries to minimize the amount of data returned to the client. Revisit all queries with SELECT * statements to determine whether you need all columns returned. For example, don't include the shipping address fields of Northwind.mdb's Orders table in your query if you're only interested in order dates or customer billing information. Avoid Jet-specific or VBA expressions in WHERE clause criteria, because the server must return all records for processing by the Jet expression service. Both Jet and SQL Server support SELECT TOPn [PERCENT] queries, but ODBC doesn't. Thus the server must return all records to the client for TOPn processing by Jet.

Try to design your form queries with WHERE clause criteria that return fewer than 100 rows. Such queries require only a single connection to the server.

After you've streamlined your queries, the next step is to add RAM. Minimum RAM for reasonable performance with five or fewer users is 128MB for Windows 2000 Professional or Server (without Active Directory installed) or 64MB for Windows NT 4.0. Start with 256MB RAM if your Windows 2000 Server running MSDE is a domain controller. Double the amount of RAM if you experience a slowdown in performance due to disk page swapping as you add more users. (SDRAM was very inexpensive when this book was written.)

If the server running MSDE also runs other server-based applications, such as Exchange 2000, consider purchasing a dedicated server for MSDE. (Running SQL Server and Exchange 2000 on the same machine is not a recommended practice.) You can buy a server with a built-in 9+GB RAID 5 array for less than the US$4,995 estimated retail price (ERP) of an unlimited-user, single-CPU SQL Server 2000 Standard Edition license. For example, a Dell PowerEdge 1400 server with two 9GB system drives and a 9GB RAID 5 array for data (without monitor) cost less than US$2,750 in early 2001.

If some—but not all—users experience performance problems, check their client PCs for adequate RAM to run Office XP or the Access 2002 runtime version. The “System Requirements for Access 2002” section of the Introduction lists the RAM requirements for Office XP. Network connectivity between the client and server also can be a problem; solving networking issues is beyond the scope of this book.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint