I’m using mySQL 5.0.11 as a back end for this Access project I’ve been working on. Trying to complete it this week too.

Anyway, I just learned that multi-table Views in mySQL that use Outer Joins are not updatable. What a bunch of %%#*$^!

This really bums me out, as all my forms are based on views, many of them multi-table views that use outer joins.

So I’m guessing I have two choices: Keep the views on the client side as Access queries, or change my forms’ Record Source properties to do the joins themselves.

So what I’m wondering if one approach is faster than the other. Also, is there a property that might point my Access queries directly to the backend tables, rather than to the ODBC links to the backend tables (skipping the middle-man in effect)?

Thanks

Aaron

I've also noticed that the realy SLOOOOOOWWW parts of my application, now that I've moved the data to MySQL, are not the ADO commands, but the basic Find commands using the Form, e.g.

DoCmd.FindRecord ProjectNumber, , , , , acSearchAll, , acCurrent

Also, any time I do a Find on the form using ctrl-F are painfully slow. This was not the case when it was in Access, and has not been the case when I've used SQL Server as the backend (not an option this time, unfortunately). It's really wierd because the actual query results come back from mySQL very, very fast.

Any help would be forever appreciated. Thanks.

Aaron

It sounds like it could be an index issue with the mySQL tables on the second issue. Perhaps?

You should also be able to create a direct connection to a dbms without a DSN. DSN's are solid, but slower than a direct connection. Have you ever done this with SQL Server? If not, do a search in VBA for data provider statements. I am not sure what mySQL is expecting in the string. You want the article "Using providers with ADO." Here is the syntax for SQL Server from that article. Note the lack of a reference to a DSN.:

"Provider=SQLOLEDB;Data Source=serverName;Initial Catalog=databaseName; User ID=userName;Password=userPassword;"

HTH


mmcdonal

Thanks. I got a lot of information from this page here:

http://support.microsoft.com/default...b;en-us;286222

Thank you though.

Aaron