SQL Server - Is it faster? Speeding ACCESS Up! (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
43,275
Before moving to SQL, you need to modify the RecordSources of all forms/reports so that they refer to queries (querydef's is my preference) that have criteria to limit the rows returned to only ONE record wherever possible. Typical Access apps bind forms to tables and filter locally. That is veeeeeeeeeeeery inefficient when the BE is SQL Server. you want to limit the data transferred across the LAN to as few records as possible. It is far better to run a hundred queries over the course of a day to retrieve one record each than to download an entire table and process it locally, especially if the form will be opened and closed all day causing the data to be retrieved again each time.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 28, 2001
Messages
27,186
In terms of EDI, the U.S. Navy had TONS of that going on. When I first started with the US Navy Reserve Personnel system, there used to be a daily process we called a "tape cut" and it literally meant writing an EDI file on a big-reel magtape. Some Navy couriers got into a van and drove the batch of daily tapes once every few days and brought back the responses to the previous transfers. When the Navy finally got ALL of their machines on the same network (with lots of arm-twisting), we started to have daily FTP operations that we still called "tape cuts" - but the couriers were all ticked off because the driving assignment was considered a cushy assignment. Part of the arm-twisting was having to add security codes (essentially, digital signatures) so that we would know that the record was correctly received. The old-line commanders of the various departments finally, grudgingly acceded to modern technology.

At the height of our operation, the Navy Reserve personnel system had a schedule of daily and weekly EDI transfers with 18 different agencies and the files were transactions ranging from 20 Kb to 20 Mb per transaction file. They had all sorts of protocol meetings because a data file might start when a reservist joined the reserves. Then we sent data to BuPers, who forwarded stuff to DFAS, RCCPDS, the Headquarters system (that I ran), and one of the RIMS sites. Eventually, some sort of acknowledgement made it to the reservist's home reserve site and at that point, it was official. But that transaction got relayed to at least half a dozen places before making it back where it was needed. The more amazing part was that if there were no errors in the initial submission and the reserve unit had a proper billet for the new person, it might take only 3 days to get the final notice back down to the reserve site. But you don't want to know what it looked like if any errors were in the submission.
 

Users who are viewing this thread

Top Bottom