By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

recordset cashing

P: n/a

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)

Feb 23 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Feb 23 '06 #2

P: n/a
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)

Feb 24 '06 #3

P: n/a
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...

Feb 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.