recordset cashing

[BillCo]

This worked for a large database (150+ tables):

Do While Not rstLinks.EOF
Set td = DB.TableDefs(rstLinks!Name)
'Debug.Print rstLinks!Name
If InStr(td.Connect, "corpbe.mdb") Then
td.Connect = ";DATABASE=" & strDestDatabase & ";TABLE=" &
rstLinks!Name
td.RefreshLink
End If
rstLinks.MoveNext
Loop

rstLinks is a recordset of a query on MySysObjects

But on a smaller database, it loops indefinately - never reaching .EOD

After much head banging, a possible reason dawned on me - that for a
larger recordset access caches the values - but for a smaller recordset
it doesn't.
Cause of failure: td.RefreshLink - for a cached redordset this is not a
problem, but it really upsets a small recordset being read directly
from MSysObjects. As far as I understand MSysObjects is re-formatted on
a td.RefreshLink - so the recordset loses its place.

1. Does this sound like the reason, or am I reaching - is there a
simpler cause?
2. Is there a way to force Access to cache or not to cache a recordset?

(btw I solved the problem by looping directly through DB.TableDefs()
instead)

[salad]

BillCo wrote:

This worked for a large database (150+ tables):

Do While Not rstLinks.EOF
Set td = DB.TableDefs(rstLinks!Name)
'Debug.Print rstLinks!Name
If InStr(td.Connect, "corpbe.mdb") Then
td.Connect = ";DATABASE=" & strDestDatabase & ";TABLE=" &
rstLinks!Name
td.RefreshLink
End If
rstLinks.MoveNext
Loop

rstLinks is a recordset of a query on MySysObjects

But on a smaller database, it loops indefinately - never reaching .EOD

After much head banging, a possible reason dawned on me - that for a
larger recordset access caches the values - but for a smaller recordset
it doesn't.
Cause of failure: td.RefreshLink - for a cached redordset this is not a
problem, but it really upsets a small recordset being read directly
from MSysObjects. As far as I understand MSysObjects is re-formatted on
a td.RefreshLink - so the recordset loses its place.

1. Does this sound like the reason, or am I reaching - is there a
simpler cause?
2. Is there a way to force Access to cache or not to cache a recordset?

(btw I solved the problem by looping directly through DB.TableDefs()
instead)

Here's some code that loops thru the tables. Let's say you have 100
tables. Let's find out if there is a loop. Break on count 150
Dim tdf As TableDef
Dim i As Integer
For Each tdf In CurrentDb.TableDefs
If Left(tdf.name, 4) <> "MSys" Then
i = i + 1
Debug.Print tdf.name
If i > 150 then exit for
End If
Next
MsgBox "done " & i

I am excluding system tables. Who knows, maybe some odd table has
"corpbe.mdb" in the name. Maybe a table in rstLinks is your culprit.

[david epsom dot com dot au]

You would solve the problem by opening a snapshot, instead
of a table-type recordset. So that changes in the table
where not reflected in the snapshot.

But I see that you have a better solution.

(david)

"BillCo" <co**********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

This worked for a large database (150+ tables):

Do While Not rstLinks.EOF
Set td = DB.TableDefs(rstLinks!Name)
'Debug.Print rstLinks!Name
If InStr(td.Connect, "corpbe.mdb") Then
td.Connect = ";DATABASE=" & strDestDatabase & ";TABLE=" &
rstLinks!Name
td.RefreshLink
End If
rstLinks.MoveNext
Loop

rstLinks is a recordset of a query on MySysObjects

But on a smaller database, it loops indefinately - never reaching .EOD

After much head banging, a possible reason dawned on me - that for a
larger recordset access caches the values - but for a smaller recordset
it doesn't.
Cause of failure: td.RefreshLink - for a cached redordset this is not a
problem, but it really upsets a small recordset being read directly
from MSysObjects. As far as I understand MSysObjects is re-formatted on
a td.RefreshLink - so the recordset loses its place.

1. Does this sound like the reason, or am I reaching - is there a
simpler cause?
2. Is there a way to force Access to cache or not to cache a recordset?

(btw I solved the problem by looping directly through DB.TableDefs()
instead)

[BillCo]

i've sloved the problem ok, looping through TableDefs() - you dont need
the i > 150 thingy, "for each in" covers it.

the question is really about recordset caching...