Access front end with mysql back end

[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.