Adding a Field to a Form

[SusanV]

Yep - that's how I have most of the multi-table forms set up. I've noticed
though that that tends to cause some slow performance opening some forms...

 

[Rick Brandt]

Yep - that's how I have most of the multi-table forms set up. I've
noticed though that that tends to cause some slow performance opening
some forms...

There should be no difference in performance between using a SQL Statement
for your Form's RecordSource compared to using a saved query that contains
the same SQL. Saved queries have a slight performance advantage compared to
SQL statements that are generated on the fly in code, but those differences
don't exist when compared to SQL Statements in Form or Report RecordSources.
Those have saved optimization plans exactly the same as saved queries do.

 

[SusanV]

Good morning Rick,

I understand what you're saying, but haven't seen it as I don't store
queries. What I do in cases of complex SQL is assign the Recordsource via
VBA in On_Open events - this seems to be MUCH faster, especially with
complex joins. For instance, for one report, the following SQL joins 4
tables; using the SQL directly in the form's recordsource takes approx 5
seconds to load. As a test, I saved the SQL as a stored query - as you said,
same performance:

SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
[tblPMO].[SubForApp], [tblSched].[LCode], [tblLSGCode].[TITLE],
[tblLSGCode].[NARR], [tblLSGCode].[ManHours], [tblCauseCodes].[CauseCode],
[tblPMO].[Author] FROM tblPMO INNER JOIN ((tblLSGCode INNER JOIN
tblCauseCodes ON [tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) INNER JOIN
tblSched ON [tblLSGCode].[LCode]=[tblSched].[LCode]) ON
[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE ((([tblSched].[OpStat])='FOS'));


On the other hand, using this VBA instead, the form opens in milliseconds:

Private Sub Report_Open(Cancel As Integer)

Dim src As String

src = "SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
" _
& "[tblPMO].[SubForApp], [tblSched].[LCode], [tblLSGCode].[TITLE], "
_
& "[tblLSGCode].[NARR], [tblLSGCode].[ManHours],
[tblCauseCodes].[CauseCode], " _
& "[tblPMO].[Author], [tblSched].[LOM] FROM tblPMO INNER JOIN " _
& "((tblLSGCode INNER JOIN tblCauseCodes ON
[tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) " _
& "INNER JOIN tblSched ON [tblLSGCode].[LCode]=[tblSched].[LCode])
ON " _
& "[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE
((([tblSched].[OpStat])='FOS') " _
& "And (([tblSched].[LOM])='L4')); "

Me.RecordSource = src
DoCmd.Maximize
End Sub


Why is there such a difference?


SusanV <~~ wants to understand the nuts and bolts, not just "how-to"

 

[Douglas J Steele]

I think it may be a case that when you have the recordsource "preassigned"
the recordset gets populated before the form opens. In your approach, the
form is opening blank, and then you assign a recordsource to it, so you're
seeing the data as it's being pulled out of the tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good morning Rick,

I understand what you're saying, but haven't seen it as I don't store
queries. What I do in cases of complex SQL is assign the Recordsource via
VBA in On_Open events - this seems to be MUCH faster, especially with
complex joins. For instance, for one report, the following SQL joins 4
tables; using the SQL directly in the form's recordsource takes approx 5
seconds to load. As a test, I saved the SQL as a stored query - as you said,
same performance:

SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
[tblPMO].[SubForApp], [tblSched].[LCode], [tblLSGCode].[TITLE],
[tblLSGCode].[NARR], [tblLSGCode].[ManHours], [tblCauseCodes].[CauseCode],
[tblPMO].[Author] FROM tblPMO INNER JOIN ((tblLSGCode INNER JOIN
tblCauseCodes ON [tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) INNER JOIN
tblSched ON [tblLSGCode].[LCode]=[tblSched].[LCode]) ON
[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE ((([tblSched].[OpStat])='FOS'));


On the other hand, using this VBA instead, the form opens in milliseconds:

Private Sub Report_Open(Cancel As Integer)

Dim src As String

src = "SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
" _
& "[tblPMO].[SubForApp], [tblSched].[LCode], [tblLSGCode].[TITLE], "
_
& "[tblLSGCode].[NARR], [tblLSGCode].[ManHours],
[tblCauseCodes].[CauseCode], " _
& "[tblPMO].[Author], [tblSched].[LOM] FROM tblPMO INNER JOIN " _
& "((tblLSGCode INNER JOIN tblCauseCodes ON
[tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) " _
& "INNER JOIN tblSched ON [tblLSGCode].[LCode]=[tblSched].[LCode])
ON " _
& "[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE
((([tblSched].[OpStat])='FOS') " _
& "And (([tblSched].[LOM])='L4')); "

Me.RecordSource = src
DoCmd.Maximize
End Sub


Why is there such a difference?


SusanV <~~ wants to understand the nuts and bolts, not just "how-to"

There should be no difference in performance between using a SQL Statement
for your Form's RecordSource compared to using a saved query that contains
the same SQL. Saved queries have a slight performance advantage compared
to SQL statements that are generated on the fly in code, but those
differences don't exist when compared to SQL Statements in Form or Report
RecordSources. Those have saved optimization plans exactly the same as
saved queries do.

 

[SusanV]

Ah, that makes sense!

Thanks Doug!

;-)

SusanV

I think it may be a case that when you have the recordsource "preassigned"
the recordset gets populated before the form opens. In your approach, the
form is opening blank, and then you assign a recordsource to it, so you're
seeing the data as it's being pulled out of the tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good morning Rick,

I understand what you're saying, but haven't seen it as I don't store
queries. What I do in cases of complex SQL is assign the Recordsource via
VBA in On_Open events - this seems to be MUCH faster, especially with
complex joins. For instance, for one report, the following SQL joins 4
tables; using the SQL directly in the form's recordsource takes approx 5
seconds to load. As a test, I saved the SQL as a stored query - as you said,
same performance:

SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
[tblPMO].[SubForApp], [tblSched].[LCode], [tblLSGCode].[TITLE],
[tblLSGCode].[NARR], [tblLSGCode].[ManHours],
[tblCauseCodes].[CauseCode],
[tblPMO].[Author] FROM tblPMO INNER JOIN ((tblLSGCode INNER JOIN
tblCauseCodes ON [tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) INNER JOIN
tblSched ON [tblLSGCode].[LCode]=[tblSched].[LCode]) ON
[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE ((([tblSched].[OpStat])='FOS'));


On the other hand, using this VBA instead, the form opens in
milliseconds:

Private Sub Report_Open(Cancel As Integer)

Dim src As String

src = "SELECT [tblPMO].[RepNum], [tblPMO].[Title], [tblLSGCode].[MCODE],
" _
& "[tblPMO].[SubForApp], [tblSched].[LCode],
[tblLSGCode].[TITLE], "
_
& "[tblLSGCode].[NARR], [tblLSGCode].[ManHours],
[tblCauseCodes].[CauseCode], " _
& "[tblPMO].[Author], [tblSched].[LOM] FROM tblPMO INNER JOIN " _
& "((tblLSGCode INNER JOIN tblCauseCodes ON
[tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode]) " _
& "INNER JOIN tblSched ON
[tblLSGCode].[LCode]=[tblSched].[LCode])
ON " _
& "[tblPMO].[RepNum]=[tblSched].[RepNum] WHERE
((([tblSched].[OpStat])='FOS') " _
& "And (([tblSched].[LOM])='L4')); "

Me.RecordSource = src
DoCmd.Maximize
End Sub


Why is there such a difference?


SusanV <~~ wants to understand the nuts and bolts, not just "how-to"

SusanV wrote:
Yep - that's how I have most of the multi-table forms set up. I've
noticed though that that tends to cause some slow performance opening
some forms...

There should be no difference in performance between using a SQL Statement
for your Form's RecordSource compared to using a saved query that contains
the same SQL. Saved queries have a slight performance advantage compared
to SQL statements that are generated on the fly in code, but those
differences don't exist when compared to SQL Statements in Form or Report
RecordSources. Those have saved optimization plans exactly the same as
saved queries do.

 

[Joan Wild]

Your arguments make sense, especially with calculated queries. I just
wish my users wouldn't mess with them. In particular, I have 2 users
who know how to make queries, which is fine - but they tend to be
lazy and simply modify existing ones - breaking all sorts of forms
and reports etc. They drive me batty!

You could provide them with a separate frontend mdb where they can make all
the queries they like, but not mess with the production queries.

I
know I *could* use Access Security, but management has decided that
the engineers have enough passwords to deal with so that's not an
option (?!?) <sigh>

You can secure it so that no login is required. See
http://www.jmwild.com/SecureNoLogin.htm

 

[SusanV]

Hi Joan,

If I put the queries in the backend, how would I reference them from the
frontend MDE? I like the idea, but have no idea how to implement it. They
have a frontend MDE now, which keeps them from messing with table design.

TIA,

SusanV

 

[Joan Wild]

I don't think I suggested putting the queries in the backend.

I'm suggesting you give them two frontend files. One is the production one
that everyone uses. Give them a separate frontend, that contains the table
links to the backend, but nothing else. They can create all the
queries/reports they like.

 

[SusanV]

Sorry, I misunderstood!

I don't think I suggested putting the queries in the backend.

I'm suggesting you give them two frontend files. One is the production
one that everyone uses. Give them a separate frontend, that contains the
table links to the backend, but nothing else. They can create all the
queries/reports they like.