MySQL Forums
Forum List  »  Microsoft Access

Re: Access to MySQL - Newbie
Posted by: Sebastien Caisse
Date: May 03, 2006 08:21AM

Vernon Robinson wrote:
> ODBC
> 1. Is this operationally the fastest way to
> connect Access to MySQL?
Pretty much, yes.

> 2. Must I use JET to work with MySQL with Access
> as the front end? Access Projects seem limited to
> SQL Server.
Access always uses JET so you have little choice (well, except as mentioned using MS SQL and Projects instead).

> 3. Will I pay a performance penalty using JET?
> What are some of the common gotchas to watch out
> for in regards to performance?
Not really; I mean the goal of SQL is to leave as much data/processing on the server and retreive the results on the client. This can be an issue when retreiving data on a really slow connection so you have to make sure all results queries from the server are small - and in the case of the MySQL ODBC driver (though this could happen on the ODBC, Jet or even Access level), sometimes the queries essentially download the entire table from the server and does the "filtering" on the client side. This is obviously very slow, so you'll need to make sure this doesn't happen. There are 2 main ways to avoid this: First is to design and test the queries to get the proper things done (I find that to check this it's a good idea to leave the query log open on the server during development/changes). The other is to send passthrough queries.

> Bound Controls
> 1. I can bind the controls to the tables. They
> seem to work ok. This is not the desired way.
> Normally I would use Queries in Access to restrict
> rows. Is this the recommneded way?
It depends on your design needs, but for most people this would be recommended behaviour - specially since it allows Access to know about references to every object in the program to avoid errors (such as the "Name Autocorrect" feature).

> 2. I have read how to rewrite the querydef on the
> fly. This would allow me to restrict the number of
> records that I retrieve. Will Jet process this or
> will it pass it along to MySQL to process.
You can do both. See point 3 above.

> JET Processing versus Server Side Processing
> 1. There are some Access specific
> statements/functions and some MySQL specific
> functions. Is there a listing that says these
> statements/functions will be handled by JET
> instead of MySQL?
You can find the JET reference in the Access documentation, select the table of contents, it's in the "Microsoft Jet SQL Reference" section. The MySQL docs are http://dev.mysql.com/doc/refman/5.0/en/index.html

Note that the Jet/ODBC layer "transaltes" any JET specific (since JET supports VBA scripting in queries) it sends to the MySQL server (see limitations statement in first point 1.). To force MySQL "syntax" you'll need to use passthrough queries.

> 2. How do I return an updateable recordset from
> MySQL that uses MySQL specific implementation?
> Passthrough Queries seem to be non-updateable.
I use .execute with INSERT or UPDATE statements instead.
Sorry for lacking any more info about this...

Options: ReplyQuote


Subject
Views
Written By
Posted
2158
May 03, 2006 04:20AM
Re: Access to MySQL - Newbie
1856
May 03, 2006 08:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.