Porting Access to MySQL

[Jonathan]

I have to port a MS Access DB to anotherdatabase engine and I would
like to use MySQL because I am familiar with it and is easy to
integrate with the web and php what is the ned point of my 'quest'.

However there are paramter queries in the access database and I would
really like to keep the access database as a backup as long as I'm in
the progress of moving/porting.

I have taken a look at the ODBC connector (3.51) and have tried a few
things. There is only one problem which gives me a headache.

Is there a way to support parameterized access queries in the MS
Access frontend with the ODBC connector and the MySQL backend?

Jonathan

[Albert D. Kallal]

"Jonathan" <jo******@heelal.nl> wrote in message
news:mk********************************@4ax.com...

Is there a way to support parameterized access queries in the MS
Access frontend with the ODBC connector and the MySQL backend?

As far as I know, you can use parameters in quires, even when they are ODBC.
About the only problem is that often JET does not do a great job of
optimizing the sql sent to the server. (especially so when using tables with
joins).

And, if you can, you should try and use a pass-through query, then you
by-pass jet altogether, and eliminate performance bottle necks. However, I
have not tried pass-though queries with parameters...and not sure if that
works!

As a work-around just build the sql in-line and pass that to the server (the
useful ness of this tip is going to depend on your existing designs.)..
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Jonathan]

On Sun, 16 Jan 2005 00:23:38 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

"Jonathan" <jo******@heelal.nl> wrote in message
news:mk********************************@4ax.com.. .

Is there a way to support parameterized access queries in the MS
Access frontend with the ODBC connector and the MySQL backend?

As far as I know, you can use parameters in quires, even when they are ODBC.
About the only problem is that often JET does not do a great job of
optimizing the sql sent to the server. (especially so when using tables with
joins).

Do you happen to know some documentation about this, because I have
not found anything untill now.
And, if you can, you should try and use a pass-through query, then you
by-pass jet altogether, and eliminate performance bottle necks. However, I
have not tried pass-though queries with parameters...and not sure if that
works!
Can you give me a hint on pass-through queries. I would like to have a
look of I can use this to implement as a solution.

As a work-around just build the sql in-line and pass that to the server (the
useful ness of this tip is going to depend on your existing designs.)..

Thanks,

Jonathan

[Albert D. Kallal]

"Jonathan" <jo******@heelal.nl> wrote in message
news:gn********************************@4ax.com...

Gee, I don't drop by this NG very much...so sorry for the delay..

As far as I know, you can use parameters in quires, even when they are
ODBC.
About the only problem is that often JET does not do a great job of
optimizing the sql sent to the server. (especially so when using tables
with
joins).
Do you happen to know some documentation about this, because I have
not found anything untill now.

Just connect and setup a table link to a table on MySql. If you run the
query with parameters...you will get prompted as you always did.
I don't see any reason why queries with parameters don't work with
MySql..they should work with any ODBC database..

And, if you can, you should try and use a pass-through query, then you
by-pass jet altogether, and eliminate performance bottle necks. However, I
have not tried pass-though queries with parameters...and not sure if that
works!

Can you give me a hint on pass-through queries. I would like to have a
look of I can use this to implement as a solution.

Again, pass through queries is simply a option in the query builder. What
this means is that the sql you write is UN-TOUCHED by ms-access, and the raw
sql text is sent to MySql. This gives you the highest performance. (just
check out pass-through in the ms-access help). While in the query builder,
just go from the menu query->sql specific->Pass-through..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal