This blog is meant to shed some lights why grid reconstruction happens.
Grid is an object in form that can show the records in a table view. When you do not use .RecordSource property, it will grab whatever is the current active table and use. When we use RecordSource property, then it will show the records of the source. While there are four (4) RecordSourceType to choose from, I will focus only on two (2) of those here:
a. If I am using Safe Select (2 cursors) Approach, then it is .RecordSourceType = 1 - Alias.
b. If I am using a single cursor approach (the same cursor gets overwritten over and over again), then it is .RecordSourceType = 4 - SQL Statement, for me.
Depending on my need, I switch either with RecordSourceType 1 or 4.
When you use Safe Select (or what I call 2 cursors approach), then you won't have a grid reconstruction because the cursor that is bound to the grid "never" gets destroyed. Its original records are just ZAPPED away and a new set is APPENDED onto that from cursor#2. That is why it is called Safe Select.
The reason why I sometimes favor the single cursor approach (letter b above) is because Safe Select, since it involves manipulating records of two cursors, is slower than the single cursor approach. However, if you are not aware how to manipulate the grid and the cursor using only a single cursor, then a grid reconstruction may occur.
Why does a grid undergo reconstruction anyway?
I will use single cursor approach for this explanation:
We bound the grid to a cursor/table via its .RecordSourceType and .RecordSource properties. Here is a sample of single cursor approach, let us say I have this in Form's Init Event:
So the grid is now bound to the cursor myCursor. Any changes to its records will be reflected back to the grid once you issue .Refresh. The grid will never reconstruct just because a change in its records occur. So you can add, edit and delete the contents of a cursor (if it has READWRITE clause) and a simple .Refresh will update the grid with the latest records.
How, when and why then does a grid undergo a reconstruction?
Still working on a single cursor scenario, when you perform another SQL SELECT ...INTO CURSOR < The Same Cursor >, then what happens is the original cursor which is the grid's RecordSource is destroyed first before a new one with the same name is built on top of it. During when the original recordsource of the grid is destroyed is when the grid gets reconstructed as well because it cannot find anything anymore to bind to at that time. The recordsource suddenly disappeared that as a fail safe protection against crash, it simply reverts the .ColumnCount back to zero thereby destroying all the columns and all the objects within those columns. Naturally all the codes inside those destroyed/removed objects will then be lost too. What will be left is just a plain white box which is the grid container itself, plus the codes within its own events and methods.
Grid is an object in form that can show the records in a table view. When you do not use .RecordSource property, it will grab whatever is the current active table and use. When we use RecordSource property, then it will show the records of the source. While there are four (4) RecordSourceType to choose from, I will focus only on two (2) of those here:
a. If I am using Safe Select (2 cursors) Approach, then it is .RecordSourceType = 1 - Alias.
b. If I am using a single cursor approach (the same cursor gets overwritten over and over again), then it is .RecordSourceType = 4 - SQL Statement, for me.
Depending on my need, I switch either with RecordSourceType 1 or 4.
When you use Safe Select (or what I call 2 cursors approach), then you won't have a grid reconstruction because the cursor that is bound to the grid "never" gets destroyed. Its original records are just ZAPPED away and a new set is APPENDED onto that from cursor#2. That is why it is called Safe Select.
The reason why I sometimes favor the single cursor approach (letter b above) is because Safe Select, since it involves manipulating records of two cursors, is slower than the single cursor approach. However, if you are not aware how to manipulate the grid and the cursor using only a single cursor, then a grid reconstruction may occur.
Why does a grid undergo reconstruction anyway?
I will use single cursor approach for this explanation:
We bound the grid to a cursor/table via its .RecordSourceType and .RecordSource properties. Here is a sample of single cursor approach, let us say I have this in Form's Init Event:
With This.Grid1 As Grid
.RecordSourceType = 4
.RecordSource = "Select *
from mytable INTO CURSOR myCursor NOFILTER"
.Refresh
Endwith
So the grid is now bound to the cursor myCursor. Any changes to its records will be reflected back to the grid once you issue .Refresh. The grid will never reconstruct just because a change in its records occur. So you can add, edit and delete the contents of a cursor (if it has READWRITE clause) and a simple .Refresh will update the grid with the latest records.
How, when and why then does a grid undergo a reconstruction?
Still working on a single cursor scenario, when you perform another SQL SELECT ...INTO CURSOR < The Same Cursor >, then what happens is the original cursor which is the grid's RecordSource is destroyed first before a new one with the same name is built on top of it. During when the original recordsource of the grid is destroyed is when the grid gets reconstructed as well because it cannot find anything anymore to bind to at that time. The recordsource suddenly disappeared that as a fail safe protection against crash, it simply reverts the .ColumnCount back to zero thereby destroying all the columns and all the objects within those columns. Naturally all the codes inside those destroyed/removed objects will then be lost too. What will be left is just a plain white box which is the grid container itself, plus the codes within its own events and methods.
So if the original cursor gets destroyed first before a new one is built on top of it resulting to grid reconstruction, how then can we avoid that?
Remove the binding/linking of the grid into cursor first:
Thisform.Grid1.RecordSource = '' && You release the linking/binding. It is not bound to anything now
The purpose is so when the cursor gets destroyed and a new one is built on top of the old one, the grid being free of any linking/binding won't be affected by that major change in the cursor. After the above, you can now perform your new SQL SELECT on the same cursor without any worries. After the cursor is created, bind it back to the grid (and in some cases issue a grid.refresh).
Having said, here is a way to avoid grid reconstruction on a single cursor:
Remove the binding/linking of the grid into cursor first:
Thisform.Grid1.RecordSource = '' && You release the linking/binding. It is not bound to anything now
The purpose is so when the cursor gets destroyed and a new one is built on top of the old one, the grid being free of any linking/binding won't be affected by that major change in the cursor. After the above, you can now perform your new SQL SELECT on the same cursor without any worries. After the cursor is created, bind it back to the grid (and in some cases issue a grid.refresh).
Having said, here is a way to avoid grid reconstruction on a single cursor:
* Grid update method:
With this.Grid1 as Grid
.RecordSource = '' && Remove binding first
.RecordSourceType = 4
.RecordSource = "Select *
from mytable WHERE < condition > "+;
"INTO CURSOR myCursor
NOFILTER" &&
Rebind to new sets
.Refresh
Endwith
I hope I have helped explain the grid reconstruction.
It was very interesting and easy to understand.
ReplyDelete