This comment is meant to contribute of this discussion about alternatives to using Microsoft Access.
In particular, it's about moving the back end data tables to SQL Server. I did this about 14 years ago, keeping the front end forms in Microsoft Access, but linking to SQL server via ODBC. It has about 30 tables, one of them several hundred of thousand of records, many with tens of thousands of records. In the last year about 20 of the other Access databases I've built and looked after have hit the "corrupted again and again problem". One has been fixed using the registry setting, as suggested by Daniel Pineault a number of times in this same chain of comments. In the others, the medium to large organisations hosting the Access databases are unwilling to change registry settings to attempt this version of the fix because they are fearful of collateral damage to other unrelated programs, and the problem continues, but does seem to be a little less frequent over time for some reason unknown to me.
The database with the SQL Server back end has never had the "corrupted again and again" problem, verifying what Daniel Pinault and others have suggested as a possible fix.
My comment is relevant to questions about the level of success and the level difficulty of the SQL Server conversion. In terms of the level of success, Access queries were taking 30 seconds or even minutes to return. The same queries, re-constructed as in pass-thru queries to SQL Server, have consistenly taken only a few seconds.
The conversion took hundreds of VBA programming hours and a good bit of learning about SQL Server, partly because the database has around 100 forms and quite a few tables and relationships. It's also partly because of some good advice from Microsoft's Kalen Delaney, in her 2001 book, "Inside Microsoft SQL Server 2000".
The rest of this entry is not my words but is a quote from Kalen Delaney. It's the introduction to her advice on SQL Server conversions from products like Access, taken from her now 21 year old Microsoft Press Book. You can see this advice at https://flylib.com/books/en/3.172.1.104/1/
Before you proceed, you must understand something crucial about using cursors: You should never let cursors turn SQL Server into a network ISAM.
You might recall from Chapter 1 that the initial grand plan for SQL Server called for it to be a higher performance back end for Ashton-Tate's dBASE IV. dBASE was really a record-oriented, ISAM-like data management system (that is, it did sequential, row-by-row processing). It was not set based. At the time, SQL Server didn't use cursors, which made a difficult task even more difficult. The original plan to make the two record-oriented and set- based systems totally compatible and seamlessly interchangeable was doomed from the outset because of inherent differences in the models. If the original SQL Server had featured the rich cursor model it has now, cursors would have been heavily used to build the dBASE IV front end from the start. But it was probably better that the impedance mismatch became obvious. It forced the SQL Server development team to reexamine its basic goals and dramatically improve its plans. It also highlighted the importance of orienting oneself to work with sets of data, not individual records as one would do with an ISAM. Had cursors existed then, they probably would have been misused, with the result being a bad SQL Server front end.
Cursors can be an important tool when used prudently. However, because cursors are record oriented, developers who are familiar with ISAM systems (such as IMS, dBASE, VSAM, or the Microsoft Jet database engine used in Microsoft Access) are often tempted to use cursors to port an application from an ISAM system to SQL Server. Such a port can be done quickly, but this is also one of the fastest ways to produce a truly bad SQL Server application. In the basic cursor example shown previously, the operation to fetch each row of the authors table is much like an ISAM operation on the authors file. Using that cursor is an order of magnitude less efficient than simply using a single SELECT statement to get all authors. This type of cursor misuse is more common than you'd think. If you need to port an ISAM application to SQL Server, do a deep port that is, go back and look at the basic design of the application and the data structures before you do the port. A shallow port making SQL Server mimic an ISAMis appropriate only if you're one of those programmers who believes that there's never time to do the port right but there's always time to do it over.
For example, even a modestly experienced SQL Server programmer who wants to show authors and their corresponding book titles would write a single SELECT statement similar to the one below that joins the appropriate tables. This SELECT statement is likely to yield subsecond response time even if all the tables are large, assuming that appropriate indexes exist on the tables.
SELECT A.au_id, au_lname, title FROM authors A JOIN titleauthor TA ON (A.au_id=TA.au_id) JOIN titles T ON (T.title_id=TA.title_id) ORDER BY A.au_id, title
NOTE When we refer to response time, we mean the time it takes to begin sending results back to the client applicationin other words, how long it takes until the first row is returned.
In the example above, all the join processing is done at the back end and a single result set is returned. Minimal conversation occurs between the client application and the serveronly the single request is received and all the qualifying rows are returned as one result set. SQL Server decides on the most efficient order in which to work with the tables and returns a single result set to satisfy the request.
An ISAM programmer who doesn't know about an operation such as a join would approach this problem by opening the authors "file" (as an ISAM programmer would think of it) and then iterating for each author by scanning the titleauthor "connecting file." This programmer would then traverse into the titles file to retrieve the appropriate records. So rather than write the simple and efficient SQL join described above, the programmer might see cursors as the natural solution and write the code shown below. This solution works in a sense: it produces the correct results. But it is truly horrific in terms of its relative complexity to write and its performance compared to the set-based join operation. The join would be more than 100 times faster than this query.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
