I'm with the Corgi on this one. I've done many migrations from native Access to Access front end with SQL Server back end. I used the upsizing wizard once many years ago and never used it since then. I examine each table and create it on the SQL Server side now.

There's no substitution for understanding what is going on at both ends and being somewhat expert at both SQL Server and Access.

For bound forms based on queries to linked tables the queries usually arrive at the SQL Server side fairly intelligently - if the tables have the proper indexes on them (always re-link the table after adding an index). There is, however, a tipping point in what Access can handle in a bound form. I had an order entry system that maxed out at around 45,000 orders and then it became slow. The order form was bound to the orders table and the order detail sub form bound to the order detail table. There were a few bound controls - mostly combo boxes - that were bound to customers and products and so forth.

Remember that when you open a bound form, the entire record set is read. When the tipping point is reached you have to come up with a different strategy for the user forms. There's no way around it that I've been able to come up with. My solution to the order entry problem was to create local Access tables for orders and detail in the Access MDB. The form was bound to those local tables (each user had a copy of the MDB on their desk top). There was VBA code that could read in the desired order/order detail into the local tables. The user would edit that and then submit the changes (kind of like a web front end) and then VBA code would push it back out to SQL Server. This has been in place for 10 years with the number of orders now at around a million. There is no performance problem.

The idea is that when you reach the tipping point, you have to get creative, write code and create search forms that will find what the user wants and only deal with that in the main forms.

If you haven't reached the tipping point, then you have to analyze the queries that Access is sending. Get a test server and database for SQL Server and link your Access tables there. Run the Profiler and capture what Access is sending when you open a form. Are your joins on indexed columns and so forth.

Todd Fifield