This worked for a large database (150+ tables):
Do While Not rstLinks.EOF
Set td = DB.TableDefs(rs tLinks!Name)
'Debug.Print rstLinks!Name
If InStr(td.Connec t, "corpbe.mdb ") Then
td.Connect = ";DATABASE= " & strDestDatabase & ";TABLE=" &
rstLinks!Name
td.RefreshLink
End If
rstLinks.MoveNe xt
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) 3 1948
BillCo wrote: This worked for a large database (150+ tables):
Do While Not rstLinks.EOF Set td = DB.TableDefs(rs tLinks!Name) 'Debug.Print rstLinks!Name If InStr(td.Connec t, "corpbe.mdb ") Then td.Connect = ";DATABASE= " & strDestDatabase & ";TABLE=" & rstLinks!Name td.RefreshLink End If rstLinks.MoveNe xt 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.Table Defs
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.
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**********@g mail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . This worked for a large database (150+ tables):
Do While Not rstLinks.EOF Set td = DB.TableDefs(rs tLinks!Name) 'Debug.Print rstLinks!Name If InStr(td.Connec t, "corpbe.mdb ") Then td.Connect = ";DATABASE= " & strDestDatabase & ";TABLE=" & rstLinks!Name td.RefreshLink End If rstLinks.MoveNe xt 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)
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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: gary artim |
last post by:
Hi All,
I have a problem using DBIx::RecordSet. I get correct results but continue
to get these messages on stderr. I looked at Compat.pm and it seems to be
pointing out a problem with my call to Setup. Could anyone (much thanks)
shed some light on my tired eyes? See below...
Gary
code sample:
|
by: Tom |
last post by:
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a parameter to
be passed for the stored procedure. I will then use the recordset to loop
thru the recordset, update values from the recordset and then update the
database by passing parmeters to another stored procedure. I would like to
use the recordset object but can it be used to pass a parameter to a stored...
|
by: Adam Short |
last post by:
I am trying to write a routine that will connect a .NET server with a
classic ASP server.
I know the following code doesn't work! The data is being returned as a
dataset, however ASP does not recognise datasets and requires a recordset.
Can the datatypes be converted? At the Classic ASP end or .NET end? Can
SOAP toolkit provide the conversion, can any toolkit provide a conversion?
...
|
by: lenny |
last post by:
Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query works fine, but
passing the resulting recordset back to the sub's caller is not working
out.
|
by: vaidas gudas |
last post by:
I am cashing tge asp.net page for duration=600 and varybyparam="ID". In this
page is the table, which binding the data from the sqlserver. when data is
modifikated using this page he showing the old page with old data.
| |
by: kjvt |
last post by:
Based on a prior posting, I've written a function to convert a
recordset to a dataview. The first call to the function for a given
recordset works perfectly, but the second call always returns a
dataview with a count = 0. Can someone explain why and how I might
work around this problem?
Here is the code for my function:
Public Shared Function GetViewFromRS(ByVal pRS As ADODB.Recordset) _
As DataView
|
by: ADezii |
last post by:
When you create an ADO Recordset, you should have some idea as to what functionality the Recordset does/does not provide. Some critical questions may, and should, be:
Can I add New Records to the Recordset?
Does the Recordset support Bookmarks?
Can we use the Find and/or Seek Methods with this Recordset?
Does the Recordset support the use of Indexes?
Will the Absoluteposition property be able to be used on this Recordset?
etc....
|
by: Oko |
last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access
2002.
One of the reports within the DB uses data that is Dynamic and cannot
be stored on the SQL Server. To resolve this, I have created an
ADODB.Recordset in the reports OPEN event, built the necessary records
inside of it, and then bound the report to this newly created
recordset.
Here's the rub:
|
by: wallconor |
last post by:
Hi,
I am having a problem using Dreamweaver CS3 standard recordset paging behavior. It doesn’t seem to work when I pass parameter values from a FORM on my search page, to the recordset on my results page.
- Recordset Paging works if no parameters are used in the recordset sql code (ie. simple sql code):
SELECT *
FROM db_name
WHERE (db_field1 LIKE ‘%text1%’ OR db_field2 LIKE ‘%text2%’)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |