run query in SQL on Form Load

[romanzero]

Hi,

I recently split my database and now everything runs really slow. From other posts I can see that this is a common problem.

One of the solution to the slowness that I have found is to have the SQL run on Form Load instead of referencing the query in the Record Source Property.

I can cut and paste the SQL from the query into VBA, but I am afraid that I don't have a lot of coding experience and simply cutting and pasting will not work.

I was hoping that someone could tell me what I am missing...

Private Sub Form_Load()
Me.RecordSource = "qryFormFilter"

SELECT tblPositionDataPRIMARY.PositiontblID, tblPositionDataPRIMARY.PIN, tblPositionDataPRIMARY.ClassCode, tblPositionDataPRIMARY.Pool, tblPositionDataPRIMARY.Stipend, tblPositionDataPRIMARY.[Standard Position], tblPositionDataPRIMARY.ComputerReplacement, Last(tblBioInfoPRIMARY.LastName) AS LastOfLastName

FROM tblBioInfoPRIMARY INNER JOIN (QRYPRIMARYPERMANENTBUDGET INNER JOIN (tblPositionDataPRIMARY INNER JOIN qryFormFilterJobHistory ON tblPositionDataPRIMARY.PositiontblID = qryFormFilterJobHistory.PositionLink) ON QRYPRIMARYPERMANENTBUDGET.PositionLookUp = tblPositionDataPRIMARY.PositiontblID) ON tblBioInfoPRIMARY.EmployeeTableID = qryFormFilterJobHistory.EmployeeLink

GROUP BY tblPositionDataPRIMARY.PositiontblID, tblPositionDataPRIMARY.PIN, tblPositionDataPRIMARY.ClassCode, tblPositionDataPRIMARY.Pool, tblPositionDataPRIMARY.Stipend, tblPositionDataPRIMARY.[Standard Position], tblPositionDataPRIMARY.ComputerReplacement

HAVING (((tblPositionDataPRIMARY.PositiontblID) Like IIf([Forms]![frmRemoteControl]![PIN Choose Box] Is Null,"*",[Forms]![frmRemoteControl]![PIN Choose Box])) AND ((tblPositionDataPRIMARY.ClassCode) Like IIf([Forms]![frmRemoteControl]![Combo47] Is Null,"*",[Forms]![frmRemoteControl]![Combo47])) AND ((tblPositionDataPRIMARY.Pool) Like IIf([Forms]![frmRemoteControl]![cboPositions]=4,-1,"*")) AND ((tblPositionDataPRIMARY.Stipend) Like IIf([Forms]![frmRemoteControl]![cboPositions]=3,-1,"*")) AND ((tblPositionDataPRIMARY.[Standard Position]) Like IIf([Forms]![frmRemoteControl]![cboPositions]=2,-1,"*")))

ORDER BY Last(tblBioInfoPRIMARY.LastName);

Thanks in adavance for any guidance.

R

 

[AlexMidd]

What is it about it that is not working?

Have fun! )

Alex Middleton

 

[ChakaTheApeMan]

This is a little off topic but… This thread deals with ADP vs MDP and speed issues. I thought you might find it enlightening.

http://www.pcreview.co.uk/forums/thread-1627588.php

I don’t use mdb for anything significant. You know the office pro cd has a copy of developer sql server. SQL server developer Ed. is going to be faster and more manageable then an mdp. You may want to experiment with up sizing to an adp.

 

[AlexMidd]

But whatever you do, don't use the upsizer wizard, or you'll be forever rewriting your queries.

Have fun! )

Alex Middleton

 

[romanzero]

I get a syntax error.

I added " to the beginning of each SQL statement and " & _ to the end.

But I still get the error.

 

[PHV]

Me.RecordSource = [highlight]"[/highlight]SELECT tblPositionDataPRIMARY.PositiontblID, tblPositionDataPRIMARY.PIN, tblPositionDataPRIMARY.ClassCode, tblPositionDataPRIMARY.Pool, tblPositionDataPRIMARY.Stipend, tblPositionDataPRIMARY.[Standard Position], tblPositionDataPRIMARY.ComputerReplacement, Last(tblBioInfoPRIMARY.LastName) AS LastOfLastName[highlight]" _[/highlight]
[highlight] & " [/highlight]FROM tblBioInfoPRIMARY INNER JOIN (QRYPRIMARYPERMANENTBUDGET INNER JOIN (tblPositionDataPRIMARY INNER JOIN qryFormFilterJobHistory ON tblPositionDataPRIMARY.PositiontblID = qryFormFilterJobHistory.PositionLink) ON QRYPRIMARYPERMANENTBUDGET.PositionLookUp = tblPositionDataPRIMARY.PositiontblID) ON tblBioInfoPRIMARY.EmployeeTableID = qryFormFilterJobHistory.EmployeeLink[highlight]" _[/highlight]
[highlight] & " [/highlight]GROUP BY tblPositionDataPRIMARY.PositiontblID, tblPositionDataPRIMARY.PIN, tblPositionDataPRIMARY.ClassCode, tblPositionDataPRIMARY.Pool, tblPositionDataPRIMARY.Stipend, tblPositionDataPRIMARY.[Standard Position], tblPositionDataPRIMARY.ComputerReplacement[highlight]" _[/highlight]
[highlight] & " [/highlight]HAVING (((tblPositionDataPRIMARY.PositiontblID) Like IIf([Forms]![frmRemoteControl]![PIN Choose Box] Is Null,[highlight]'*'[/highlight],[Forms]![frmRemoteControl]![PIN Choose Box])) AND ((tblPositionDataPRIMARY.ClassCode) Like IIf([Forms]![frmRemoteControl]![Combo47] Is Null,[highlight]'*'[/highlight],[Forms]![frmRemoteControl]![Combo47])) AND ((tblPositionDataPRIMARY.Pool) Like IIf([Forms]![frmRemoteControl]![cboPositions]=4,-1,[highlight]'*'[/highlight])) AND ((tblPositionDataPRIMARY.Stipend) Like IIf([Forms]![frmRemoteControl]![cboPositions]=3,-1,[highlight]'*'[/highlight])) AND ((tblPositionDataPRIMARY.[Standard Position]) Like IIf([Forms]![frmRemoteControl]![cboPositions]=2,-1,[highlight]'*'[/highlight])))[highlight]"[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[romanzero]

Thanks PHV,

It brings up the form (with a few bugs, but I think I can sort those out).

However, the form is "frozen." Perhaps the recordset is no longer updatable?

None of my other code (e.g., navigation buttons) work now either.

???