Are all records retrieved

[Rashapoo]

I have just completed a basic course in Access. I have some relational
b/g in DB2 and Cobol. I have a question my instructor couldn't answer
(or maybe I didn't explain myself)

I design a simple form that has data from one table. When I open this
form to display data, are all matching records retrieved into memory
rightaway even though only 1 record is displayed? So when I use
navigation keys to move forward (or back) is data being retrieved from
database or is it coming from memory?

Thanks

[Anthony England]

<Ra******@gmail .com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.com.. .

I have just completed a basic course in Access. I have some relational
b/g in DB2 and Cobol. I have a question my instructor couldn't answer
(or maybe I didn't explain myself)

I design a simple form that has data from one table. When I open this
form to display data, are all matching records retrieved into memory
rightaway even though only 1 record is displayed? So when I use
navigation keys to move forward (or back) is data being retrieved from
database or is it coming from memory?

Thanks

A certain amount is retrieved but not all of it. For example, if your table
had 5000 rows in it, then normally you would not want to open a form based
on the unfiltered table. However, if you did, you might be surprised to
find how good performance was. The form will show you that it has 5000
records and you can scroll through them, but behind the scenes, Access is
doing some clever stuff.

If you wanted a test. Create a table with a few hundred rows and save this
back end data. Now create a new datatbase DB1 which has a single linked
table to it. Save and close the file and make a copy of it: DB2. Now you
can open up both front ends so you can see the first 10 or 20 records in the
table. Alter a column in DB1 and move to a new record. Then view DB2 and
notice the screen has not updated. Now go to DB1 and alter the last record.
When you switch back to DB2 and go to the last record - you will see the
same version. Does that make it clear what is happening?

[Rashapoo]

Thanks
I think I get what you said. I initially tested this with an
underlying table that had only 200 rows. In this case when I changed
the last record in DB1 and navigated to the same last record in Db2 I
did not see the change.
This must be because since table is small Access had loaded whole thing
into memory and the change on last record in Db1 wouldn't have impacted
the last record in Db2 which is being displayed from memory.

When I tried to repeat the test with a much larger table (this had 5K
rows), I got a failure at the get go. When I changed a column on DB1
and tried to get to the next record, I received a message "No current
record". Which seems to be because

"This error occurs after the unsuccessful application of one of the
Find methods or the Seek method, when the underlying Recordset contains
no records or the record has been deleted. Move to or select another
record, and try the operation again. If the Recordset is empty, you
cannot position to a current record. Check the BOF and EOF properties."

I guess I will find the answer to this in a different thread.

But, the gist of what you are saying is if there are a large number of
records in the underlying table then since all are not loaded into the
memory in Db2 (unlike the case with 200 records) when I navigate to the
last record it is retrieving this from the database thus showing me
what was updated in DB1.

Does this apply only to linked tables or also to tables that are in the
database?

[Anthony England]

<Ra******@gmail .com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

Thanks
I think I get what you said. I initially tested this with an
underlying table that had only 200 rows. In this case when I changed
the last record in DB1 and navigated to the same last record in Db2 I
did not see the change.
This must be because since table is small Access had loaded whole thing
into memory and the change on last record in Db1 wouldn't have impacted
the last record in Db2 which is being displayed from memory.

When I tried to repeat the test with a much larger table (this had 5K
rows), I got a failure at the get go. When I changed a column on DB1
and tried to get to the next record, I received a message "No current
record". Which seems to be because

"This error occurs after the unsuccessful application of one of the
Find methods or the Seek method, when the underlying Recordset contains
no records or the record has been deleted. Move to or select another
record, and try the operation again. If the Recordset is empty, you
cannot position to a current record. Check the BOF and EOF properties."

I guess I will find the answer to this in a different thread.

But, the gist of what you are saying is if there are a large number of
records in the underlying table then since all are not loaded into the
memory in Db2 (unlike the case with 200 records) when I navigate to the
last record it is retrieving this from the database thus showing me
what was updated in DB1.

Does this apply only to linked tables or also to tables that are in the
database?

The errors that you are getting do not sound normal. I would start a new
database, then import the old table into the new database and do a compact
and repair.
Apart from that, you seem to have understood exactly what I am saying -
although I don't have any precise facts or figures for you.
I used the example of linked tables, because that is generally the area of
concern. A multi-user database where you are worried if the data you see on
your screen matches what your colleague next door see on his. But the same
thing is true of local tables: you could show this by creating two forms
based on the same table, opening then at the same time and experimenting by
editing the records.