Access front end via ODBC to MySQL

[Steve Woodward]

I am trying to migrate membership data from a local Access table to MySQL on
the web. I installed ODBC and set up the DSN and was able to export my local
data directly to MySQL (*way* cool!).

Queries and reports run great, but using a form to access a single record
takes forever. When in the form, I click on the member number fiield (which
is the key of the record) and then click on "find" to get to the record I
want. There are about 4500 records. If I choose one towards the end of the
file it takes more than a minute to return that record.

What should I be looking at?

Thanks, ~Steve

 

[Banana]

I am trying to migrate membership data from a local Access table to MySQL on
the web. I installed ODBC and set up the DSN and was able to export my local
data directly to MySQL (*way* cool!).

Queries and reports run great, but using a form to access a single record
takes forever. When in the form, I click on the member number fiield (which
is the key of the record) and then click on "find" to get to the record I
want. There are about 4500 records. If I choose one towards the end of the
file it takes more than a minute to return that record.

What should I be looking at?

Thanks, ~Steve

When you move to a server-based RDBMS, many of things you could do in
Access UI would be quite bad and "Find" (which I think is actually
Filter) is one of such case because they create an extra round-trip.

Generally, you would have a means of entering the criteria and wrapping
this into a recordsource's WhereCondition or modifying the SQL by using
parameter queries or myriad of other techniques.

Maybe this will help: tinyurl.com/ODBCGuide

Best of luck!

 

[Rick Brandt]

When you move to a server-based RDBMS, many of things you could do in
Access UI would be quite bad and "Find" (which I think is actually
Filter) is one of such case because they create an extra round-trip.

Generally, you would have a means of entering the criteria and wrapping
this into a recordsource's WhereCondition or modifying the SQL by using
parameter queries or myriad of other techniques.

Maybe this will help: tinyurl.com/ODBCGuide

Totally agree. "Find" against ODBC sources is brutally slow. A simple
alternative is a bit of code to apply a filter.

Me.Filter = "[MemberNumber] = 1234"
Me.FilterOn = True

If MemberNumber has an index (which is a given if its your primary key), the
above should produce almost instantaneous results.