Troubleshooting

Jet Tables Fail to Upsize

I receive error messages during the upsizing process, and some tables don't upsize

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 middle of this chapter, there little probability of encountering table upsizing failures.

MSDE Performance Problems

Performance of my multiuser Jet applications is significantly slower after upsizing the tables to MSDE 2000 or other SQL Server 2000 editions

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 TOP n [PERCENT] queries, but ODBC doesn't. Thus the server must return all records to the client for TOP n 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 XP or 2000 Professional, and 256MB for Windows 2000 or .NET Server (without Active Directory installed). Start with at least 384MB RAM if your Windows 2000/.NET Server running MSDE is a domain controller. MSDE will run under Windows NT 4.0 Workstation or Server with 128MB of RAM. Double the amount of RAM if you experience a slowdown in performance due to disk page swapping as you add more users.

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

Invalid Path Errors Occur when Users Open a Secured Jet Database

After changing the location of the workgroup file to a network share, users encounter "'d:\path\filename.mdw' isn't a valid path" messages

You specified a logical drive mapped to the share, instead of the UNC path (\\ServerName\ShareName\System.mdw) to the .mdw file. You can't depend on users to assign specific logical drive letters to a share. Always use UNC to specify the location to the .mdw file.