Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 10-05-2018, 08:12 AM   #1
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 106
Thanks: 23
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
optimizing DB w/ access front end & MySQL backend

So the day was upon us where our database system(s) needed to upgrade our back end to something that could handle all our data. The decision was made to consolidate our largest and most used databases into one front end and one SQL server for the data. Were early on in the consolidation but my big question is with regards to the most efficient way of pulling the data from the MySQL back end to the access front end.

I currently have one main form with a large number of queries so optimizing this process is crucial.

first off, which index(es) are used, the ones with MySQL or with Access? Does access require its own indexes?

secondly, would a Pass through query be best or would it be better if i created custom views within MySQL and then link to those views in my front end?

Can i create a pass-through query in VBA, currently most of the SQL is VBA generated based on the user selections and actions, which is then queried to a recordset or assigned to a recordsource property, can this same thing be done and then passed through?

running a MySQL back end and connecting via ODBC is new territory for me so I want to make sure i get this right. were early on in this transition and currently many of our forms with built in queries are pretty slow, enough to be frustrating.

iv'e googled for days now and haven't come across a good source for this kind of situation, any suggested reading would be appreciated.

TIA!

JJSHEP89 is offline   Reply With Quote
 

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Windows 7 users unable to access backend MySQL jetatu General 5 04-01-2014 01:44 PM
Convert Exisitng Access Database to Access Frontend MySQL backend DBnub11 SQL Server 12 04-01-2011 08:15 AM
Access Front End and MySQL Database coyote General 26 05-01-2009 03:57 AM
MS Access distribution with MySQL backend. The_Vincester General 1 02-03-2007 12:34 PM
Using Access as a front end to MySQL davesmith202 General 1 09-16-2005 05:08 PM




All times are GMT -8. The time now is 07:07 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World