[AccessD] Backend database corruption

Arthur Fuller fuller.artful at gmail.com
Fri Feb 20 17:16:14 CST 2015


Janet,

I'll repeat what someone wrote earlier in this thread: the best solution is
to opt for a real database such as SQL Server Express or MySQL. Both are
free. Both will solve the intermittent-connectivity problem.

Both also provide stored procedures, which are ultimately the way to go;
but you can take it in two steps:

1) Move all your named queries to the DBMS, as Views, and while you're at
it, create Views to stand in for all your bound-forms' data sources. (It's
a best practice in real databases never to permit direct access to tables;
hence the Views, which are very easy to create.)

2) If and when time permits, look into replacing the Views with Stored
Procedures. That will result in optimal performance and stability, but
again there's no pressing rush on that conversion.

Also, bear in mind that Access Queries that use Group By and/or Order By
are performed on the client machine, whereas SQL Views are performed on the
server and the final results are sent to the client. That can result in a
significant increase in performance.

Arthur
​


More information about the AccessD mailing list