Guidelines for Access FE changes required for SQL backend? MySQLbackend?

[Bob Alston]

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?

Bob

[Rick Brandt]

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

[Bri]

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?

Bob

I agree with almost everything that Rick said in his response with a few
exceptions (for both MS SQL and MySQL):
- Include a Timestamp Field in all tables. In MS SQL this is a field
that you can't use for anything else, but in MySQL it is a user readable
DateTime field so it needs a special default value to work in this
capacity. The timestamp field allows Access to determine if another user
has edited the record while you have been editing it. You won't be able
to do bound edits without it. Adding it to your tables does not impact
what you have coded in your FE.
- Editing Recordsets require that you open them with the dbSeeChanges
option.
- When adding new records via .AddNew in a recordset, the AutoNumber
field is not generated until AFTER the Update rather than BEFORE it (Jet
does it at the .AddNew). So, if you add records via recordsets where you
need to know the PK of that record so you can add Child records, you
will need to adjust your code. Since it doesn't create the Autonumber
until after the Update you no longer have the record current. There are
a few ways to deal with this, the best being to try and not add records
this way and come up with an SQL Insert replacement.
- Queries with several tables and joins make Access think that it can't
send efficient request to the BE make it bring over huge amounts of data
to do it locally. These will make themselves evident fairly quickly.
These can be converted to PassThrough queries if you want/need to keep
the business logic in the FE or into Views or SPs if you don't.

There are a few other things to watch for, but are fairly rare. There is
a page out there somewhere with a lot more info in it on this topic....
OK, found it:
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Hope that helps

--
Bri