Anyone successfully using Access front ends to MySQL and using stored
procedures in MySQL to serve as efficient data sources to Forms and Reports.

I have been reading about such and found one very nice source of how to
create/modify a stored procedure at run time to serve as the source to a
form or report:

http://www.pcreview.co.uk/forums/sen...-t2924178.html

1)So if anyone has done such successfully I am interested in how they
modify stored procedures at run time to serve as the form/report source

2) for anyone doing such, do you have users accessing across the internet?
a)If so how is performance?
b)Did you have to set up symmetric internet connection,
providing high speed outbound - from the server location -
internet service?

Bob

On 6/29/2011 1:04 PM, BobAlston wrote:

Just learned that the technique listed in the above link is called
"dynamic sql". apparently one disadvantage is it does not allow SQL to
re-use execution plans. apparently there is an alternative:

http://www.sqlteam.com/article/imple...c-where-clause

Enjoy.

Also I would be interested also in people who have done such with SQL
server as well.

bob

On 6/29/2011 1:40 PM, BobAlston wrote:
performance questions on the article's approach and another has a nice
alternative.

Bob

BobAlston wrote:
I don't use MySQL so I won't have much to add here.


Yes, that's certainly the approach I used to take when limited to using
querydefs and tables as form and report datasources. These days, however,
ADO recordsets can be used, so there is a cleaner way to execute stored
procedures that is not subject to the sql injection dangers mentioned in the
comments:

dim cn as adodb.connection
set cn=Application.CurrentProject.AccessConnection
dim rs as new adodb.recordset
cn.MyStoredProcedure parm1,parm2, rs

This works regardless of the data provider so it can be used with either sql
or mysql

The "bible" for dynamic search conditions in SQL Server is Erland
Sommarskog's articles on the subject:
http://www.sommarskog.se/index.html
I did not provide a direct link to the article because
1. there are multiple versions of the article geared toward different SQL
versions
2. there are other must-read articles that would be missed if I provided a
direct link

BobAlston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:iufrj8$s1f$1 (AT) dont-email (DOT) me:

Most servers will cache dynamic SQL and they are smart about it.
That is, they will treat these two SQL statements the same:

SELECT Inventory.*
FROM Inventory
WHERE Inventory.InventoryID = 1

SELECT Inventory.*
FROM Inventory
WHERE Inventory.InventoryID = 2

The only thing that is different between them is the value in the
WHERE clause, so they will have the same execution plan.

I don't know that MySQL does this, but I do know that some
interfaces that can be used with MySQL (such as ADODB, which is not
our ADO/OLEDB) will do this for you.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 6/29/2011 1:50 PM, BobAlston wrote:
access forms with either MySQL or SQL server or is everyone using
dynamic datasource where you dynamically set the sql statement in the
datasource in the open event for the form?

Or other approaches?

bob

mySQL sucks... Move to SQL Server and utilize Access Data Projects. I bind forms to sprocs with a single click That INCLUDES full paramaterization.

Hi Bob,

I do most all of my SQL in code for Access based apps. That is to say
I write the SQL as needed via code, and then execute it. MySQL can be
set up to cache extremely well, as can most servers. The reason I do
this is to have to only maintain code in a single place. I have not
found a reason to use use sprocs when using Access as you can codee
whatever handling is required into the app itself, and the permissions
systems on todays servers is more than sufficient for safeguarding
user actions.

That being said, I have a colloeague who writes everything for input
and retrieval in sprocs / views. His reasoning is that input should
only be allowed in specified form that he defines. I do that in the
app with code. Views are used by both of us, but him far more often
than me. I have found no performace difference between the two
approaches, nor have I found one to be more reliable over the other.
My approach is probably a little easier to debug than having to work
in two places at once.

If I were to use a BE server for a web based application than I would
definitely use sprocs, but that is a different scenario from an
internal app written in Access. If I were to create a web app
interface to the same app as I have written in Access then I would use
sprocs for both (the same sprocs for both that is). I suppose you
could say it is deployment dependant.

Moving from one model of building an app to another is not difficult,
but it can be tedious. One of the things I do to minimise traffic over
the wire is to push new records into the underlying table(s) by
dynamically creating the SQL or using a sproc, but then for the local
recordset I use code to modify it. The local recordset is never
updated back to the server - ie/ it is disconnected. I chose this path
when my (now old) workplace started to shift everything to wireless
networks and I could not guarantee consistent connectivity, and I
encountered problems with consistency. I am sure that there are many
approaches to doing this and to some degree it is a matter of
necessity what you choose and in another it is personal taste. The
method I described above does not work for all scenarios, particularly
if you are looking up data that is being worked on by other people at
the same time. I would have to use an approach that indiciated when
another person worked on the same record at the same time, or if there
were updates to a record since the recordset was created.

In short there is no perfect answer, regardless of the BE server you
choose to use, and you have to design the solution dependant on the
individual requirements of the problem you are trying to solve. The
'thinner' the connection your user has to the BE the more careful you
have to be with bandwidth and if dealing with wireless or web then you
have to work around statelessness issues. Sometimes this is hard, and
sometimes not - it all depends on what you are doing.

Can you provide us an example of what you are trying to solve?

The Frog