I have a number of Access 97 applications that I would like to convert to SQL 7 by first converting the back end data to SQL (easy to do) while modifying the Access application to use the SQL database instead of the Access database. Later I will consider a re-write in VB.
I am testing this with a database with a primary data table with about 1.3 million rows of data with two indexed columns, one a date column and the other a text column. I’m using pass through queries to connect to SQL (nolinking of tables).
Access here means the Access back end data, SQL means the same data is in the SQL 7 database. Both databases are located on servers.
Comparison of operation speed:
Open the table (Access) or query (SQL) with no selection criteria:
Access about 5 seconds to display anything, SQL about 1 second. Winner: SQL
Open using a query (SQL is a pass through query with proper syntax for SQL) with date column selected as greater than a date and the results sorted by the indexed text column:
Access about 9 seconds to display anything, SQL about 31 seconds.
SQL should be as fast or faster. What might I be doing wrong? Has anyone experience with this type of conversion?
