Converting Access 2000 (linked tables via SQL 2000) to ADP 2

[jcg6]

I have an access 2000 database that uses linked tables via a SQL 2000 database as its source. I am having problems with this type of setup because we have people accessing the database on multiple networks - slow performance.

I have been reading that ADP could help with my slow performance issues (instead of using linked SQL tables with an Access frontend)? Does anyone have any comments about this?

Also, I have tons of queries with parameters in them relating back to forms, etc. I also have mass amounts of SQL statments embedded within VBA code. I really don't know how to replicate those same functions in ADP. Any guidance or hints would be much appreciated. Thanks very much.

 

[cjowsey]

In my experience ADP is definitely faster than MDBs with linked tables as the data access method is superior. Also, the more you move to a true client server model with stored procedures, functions, views and triggers doing the bulk of the work on the data server, the faster your application becomes. For example, ADPs have a ServerFilter property which filters the results of an SQL view or query on the server and only returns the matching results to the client. With an MDB, Access filters the results after they have all been received by the client. Therefore there can be a lot more network traffic through this alone.

However, I think the biggest single factor affecting performance is database design. A poorly normalised database and badly written SQL can create an enormous overhead. It is worthwhile objectively reviewing your database design as part of the process.

Queries with form parameters will have to be rewritten as they are not supported by SQL Server. You can replace them with User Defined Functions (UDFs) if you are using SQL Server 2000 or with stored procedures. Either way, you will probably have to do some coding in both the client and SQL Server.

Embedded SQL should not be a problem as long as it is true ANSI-92 SQL. You may need to create an ADO wrapper function in your application to set up the connection, execute the SQL and get the results back. However, as with my comments in the first paragraph, it will be faster if the SQL is precompiled on the server as a stored procedure or view.

Be aware that Access SQL is not fully ANSI compliant. One of the big differences is the IIF() function which is not supported by SQL Server. You have to replace any IIF() calls with a CASE statement or a UDF in your SQL Server code.

Hope this helps,
Clive