Backend Issues

  • Thread starter Aaron E via AccessMonster.com
  • Start date
A

Aaron E via AccessMonster.com

I’m using mySQL 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 5.0.11 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
 
A

Aaron E via AccessMonster.com

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
 
A

Andrew Backer

Have you tried running .Find on the recordset itself?
Me.Form.RecordSet.Find/FindFirst "field=value"? I have never actually
*used* FindRecord to find a record before, but .Find has always been
fast with sql server/mdb back ends.

Does mySql support views? Only in 5 right? IMHO it's not even a valid
db if it can't do what an access db can (and to think, people scoff!).
If you are using 5, do views work better?

Have you tried running a trace and seeing what access is outputting?
MySQL must have some sort of trace tool to look at this stuff, and it
might be higly illuminating.

- Andrew
 
A

Aaron E via AccessMonster.com

Hi Andrew,

Don't know the Me.Form.RecordSet.Find method (apparently Access doesn't
either). Maybe it's an Access XP only construct (I'm doing a 2000 project).

Aaron
 
Top