Moving from an Access Back End

[throwdini]

I have created an Access application that to this point has both its front end and back end in MS Access. The application has worked with multiple back ends some ranging from 25 - 40,000 kb to 1 gig and could possibly grow even larger (not from bloating).

Obviously the performance isn't as great with the larger sized back ends, and there is the possibility that we may want to go web/server based to allow remote access to the data. I have played around with both SQL Server and MySQL and would prefer to use MySQL as the back end.

I would have thought that using MySQL would have improved the performance with the larger back ends, but it hasn't. In fact my application now hangs up when it tries to pull data for any form or report.

I have installed MyODBC 3.51.06, and I think it is set up correctly. Any help would be greatly appreciated.
Thanks,
Jay

 

[sleipnir214]

First, there's proper indexing of tables.

Then I'd look at the advice on this page:

http://www.mysql.com/doc/en/MyODBC_clients.html

I also recommend using pass-through SQL queries wherever possible. Those parts of the ODBC subsystem that reside in Access can really slow down database apps.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[throwdini]

Can you give me an example of the pass through query? For example, I have a form or report that has a record source saved as a query. What, if anything would need to be changed?
Thanks,
Jay

 

[sleipnir214]

There's nothing different about the query itself, so long as you are creating your queries with SQL.

The difference is how Access passes the query to MySQL. When you pass a normal query to ODBC, the ODBC subsystem interprets the query into a universal intermediate form, then uses the database-specific driver to create a database-specific form of the query. Why go through all that when MySQL already understands SQL, which was the form the query started in? A pass-through query just just that -- it passes the query directly to the driver, passing throught the interpretation layers.

In Access, once you have opened the dialog box to create a new query, a "Query" menu will be available. In that menu select "SQL Specific" then it's submenu "Pass-Through". You will notice that the title bar of the query dialog will change from something like "Query 1: Select Query" to "Query 1: Pass-through query". The method of entering the query immediately changes to SQL -- you can't use the GUI to create the query directly. You can use the query gadget to create a regular query, switch to SQL view mode, then copy and paste the query into a pass-through query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[throwdini]

I will try that. Thanks.

 

[throwdini]

I have successfully created pass through queries, but it doesn't seem to have solved the problem. Using the ODBC driver linked tables or the pass through queries work with a smaller back end, but it still just hangs up with a larger data set.

Could it be a timing out problem? When I just run the query, informaiton is returned in a "reasonalbe" amount of time, but as a record source to a form or report it still just hangs up?

Any suggestions?

 

[sleipnir214]

In the options configuration for your DSN, what are the settings of the following options:
Return Matching Rows
Allow BIG Results
Enable Dynamic Cursor

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[throwdini]

On my DSN configuration I have "Don't Optimize Column Width" and "Return Matching Rows" checked, and everything else is left blank.

Also I have tried where I create a DSN file (?) and as a System Data source, do either of these work better than the other?
Thanks

 

[sleipnir214]

You might want to try creating a system DSN to your MySQL database and set those three options.

I'm sorry I can't be more specific. Nearly all my experience with MySQL has been through PHP, which doesn't use ODBC to communicate with MySQL.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[throwdini]

Thanks for the help. Can you direct me to more information on PHP. Can it be used to link an access front end to a mysql back end?

 

[sleipnir214]

I don't understand what you mean by "link".

PHP cannot replace VBA as a programming language for programming Access applications, if that is what you mean.

Also, PHP does not need any other application to enable it to communicate with MySQL, not even ODBC. It has the ability to communicate natively with MySQL.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[throwdini]

Ok.
I think I completely misunderstood PHP. My thought was that it could be the intermediary between MySQL and MS access, as the ODBC drivers currently do.

I gather from your reply that PHP would be used to create a completely different kind of front end?

 

[sleipnir214]

That is correct.

The primary use of PHP is for dynamically producing web pages. It compares most directly to ASP/VBScript of any of Mi¢ro$oft's products.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[KUZZ]

leipnir214

I was thinking the same about changing my back end to MySQL.

The problem with it is as follows. MSAccess FE communicates with it's MS Access BE direclty, and when you update anything on a form on the FE the BE receives the data changes instantly.

If you want to use MySQL for back end you must use passthrough querries. Pasthrough querries only transactions information back and forth as you tell them to explicitely. So for example if you make a passthrough query on the FE to pull your main table from the BE it will only pull the data into the FE in the style of a readonly table. Now when you try to do some data changes from a form that feeds off of the main table you will not be able to just by jumping to the next field or record. You wold need to do all your changes on the form and then click a button (or have an automatic run of code after the update of the last field) in order to send sql code like UPDATE fields X from table MainTable with the stuff entered in the form. so you would have to run update querries for your tables on the mysql end every time you intend to change something. That would require perfection with mysql code and very qood knowledge of you table's structure. Is it worth the effort - I think yes, but only when you NEED! to run that database through TCP/IP.

otherwise explore options like VPN, and keep your Access BE.

Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[sleipnir214]

Just keep in mind a couple of things.

Access, on a shared database, is a network resource hog. In order to maintain that dynamic recordset, Access must be either constantly checking on the records it's showing to check for updates or locking them to keep other instantiations of Access from doing the same.

Access gets squirrelier the larger the database gets.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!