View Single Post
Old 10-09-2018, 10:29 AM   #14
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
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by CJ_London View Post
because you are referencing a form variable, the ODBC driver will return everything to then compare against the form variable

you also have # in a field name - do not use non alpha numeric characters (except underscore) it can cause issues

with regards the iif - bad bit of coding. I'm not going to try to understand why you are doing it that way - would suggest wear etc should be in a lookup table and PCL_Category should be numeric linked to the lookup PK

with regards the vba, the build would be something like

Code:
 
dim sqlStr as string
sqlStr="SELECT......FROM.....WHERE PCL_ToolNumber=" & me.txtTM_ToolNumber & " AND PCL_Category IN (" & 
me.recordsource=sqlStr
a lot of this code is pieced from older & other databases, and some from back when i knew a lot less about what i was doing....

I've significantly sped this form up just by eliminating a few IIF's, and Dlookups from some of my queries (i also deleted an entire tab that was running a pretty intense query) now i'm down to less than 1.5 sec to open which i can live with for now.
JJSHEP89 is offline   Reply With Quote