Access front end with mysql back end (1 Viewer)

[Tskutnik]

All - i hope you are well.
Can anyone send a few good links for a newbie like me to convert my access db to MySQL? I assume the queries should be moved too so access is just the front end.
Thoughts and links welcome.

 

[theDBguy]

Queries can stay in Access/FE depending on what you use them for. Some of them might be better saved in the BE as views and some of them may need to be converted into stored procedures (or whatever is the equivalent in MySQL).

 

[Tskutnik]

DB - thanks - I read that if the queries stay in Access then all the data has to be called from the DB first and then calculated, which would slow down performance. I'll have a lot of data and some complex queries so speed will matter.
Is this what you meant by the stored procedure route?

 

[theDBguy]

DB - thanks - I read that if the queries stay in Access then all the data has to be called from the DB first and then calculated, which would slow down performance. I'll have a lot of data and some complex queries so speed will matter.
Is this what you meant by the stored procedure route?

That's part of it. If you want the server to process the request, then you move the queries to the server. If you want Access to process the data, then you keep the query in Access. So, it depends...

 

[CJ_London]

then all the data has to be called from the DB first and then calculated

not true unless your queries include things like domain or user define functions or other functions which do not have a mySQL equivelent which can be translated by the ODBC driver

 

[Tskutnik]

OK - Im new to this so I'll have to read up on it all first. Thanks a lot for the help

 

[zeroaccess]

not true unless your queries include things like domain or user define functions or other functions which do not have a mySQL equivelent which can be translated by the ODBC driver

Does that include using IIf and Nz on query fields?

 

[CJ_London]

nz will translate (in TSql the equivalent is isNull - would need to check for mysql). iif won't translate - the TSql equivalent is case when. See this link https://www.databasejournal.com/fea...alents-for-Microsoft-Access-VBA-Functions.htm

My recommendation would be to use stored procedures or passthrough queries which would be written in the native sql to the rdbms, In that way all processing takes place in the rdbms server.

The rsbms won't necessarily be any faster than Access - they are pretty much the same in general terms and might even be slower at the front end if network connections are slower. However sql server/mysql do have a broader vocabulary and consequently can do some things in a more efficient way than access.

Developers who have used the 'wizard' options in access will generally have a bigger hill to climb in terms on maintaining performance when converting to sql server/mysql. For example forms should be based on queries returning only one record or perhaps 20/30 in a continuous form, not the whole table. Ditto for subforms and combos.

 

[Pat Hartman]

then all the data has to be called from the DB first and then calculated

I would avoid the author who gave you that advice. Access "attempts" to pass through every query. As others have mentioned, some things might prevent that so you do need to avoid some VBA and all UDF functions in your where clauses in particular. If you use VBA or UDF functions in your where clause, that will not prevent Access from sending the rest of the query to the server for processing. But if it can't send the where clause, Access will ask for all rows to be returned. If the WHERE clause can be sent then only the desired records are returned. Access can then apply your non-translatable VBA functions and your UDF's on the records that are returned.

If you don't use criteria in your query then ALL rows are returned from the table and this is what you want to avoid at all costs.

Keep in mind that Access is a RAD (Rapid Application Development) tool and it does things the "Access" way. If you don't want to do things the "Access" way, do NOT use Access. You will be very unhappy and will cause yourself an excessive amount of work. pass-through queries bound to forms make the forms NOT updateable. Nor are forms bound to stored procedures. SP's are frequently used as the recourdsource for reports or to do batch processing. They are rarely used in forms.

Views are very useful for making joins more efficient.

Applications that were originally built for Jet/ACE BE's can be difficult to convert since they rarely use good client/server techniques like binding forms to queries with criteria that severely limits the rows returned. Their forms rely on form filters. If you were to simply convert the tables in one of these apps and not change anything else, the app would most likely be slower than it was when bound to Jet/ACE which always surprises people.

What is your objective in converting the BE? All my apps are built with the ability to convert to SQL Server or another RDBMS whether the client intends to convert them or not. The point being, using good client/server techniques does not interfere with using Jet/ACE. But, using them from the beginning makes me able to convert an app from Jet/ACE to SQL Server in hours rather than days or weeks. I even have one app which is sold to the public that allows the purchaser to choose to install the SQL Server BE or use an ACE BE. There are a couple of places where I needed parallel code but the rest of the app is built for ODBC and Jet/ACE are just fine with it.