Bob Alston wrote:
Anyone know of any list of changes required in an Access FE to make
the Access FE work with a SQL backend?
ditto for Access FE working with a MYSQL backend?
It depends on how the question is framed.
If you replace every table in your Access app with a link to a table on a SQL
Server having the same field definitions then the app will work even if you do
nothing else at all. Access and whatever VBA code you might be using doesn't
really care where that linked table resides. Only that it be named and have the
appropriate field names for what Access is trying to do with it.
Now...will it work well or even as well as before? That is a different
question. Most would be surprised to learn that it might work pretty darned
well just like that. Access/Jet does a good job of getting the server to
perform much of the work in that scenario. This might not be "client server
optimized", but rather than having to do a major overhaul what you usually find
is that there will be some areas that perform slowly or which perform "okay" but
where you can tell that an unnecessary amount of data is being pulled over the
LAN and those are the areas to concentrate on for rework.
Rework will normally involve replacing some of the built in mechanisms like
"find" with methods more suited to a server back end, building some server side
Views and Stored Procedures where complex joins and/or processing is being done,
and generally making sure that the app only pulls the minimum data for any given
task. Forms that just open against unfiltered tables with thousands of records
in them for example is a bad idea in a client server app.
Notions that the entire app must be re-written using ADO instead of DAO and that
ALL interaction with the server must be done with Views and Stored Procedures
are just flat wrong.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com