ADOX catalog tables slow performance

[ericgj]

Why is any access to items in the tables collection (and perhaps other
collections in ADOX) so incredibly slow? Can anything be done to speed it
up? This seems like a well-known problem, but I have yet to see any
work-around. I have tried referencing by name or by index, it makes no
difference.

FYI, Ultimately what I am trying to do is get the back-end database path
based on a particular linked table in the front-end database. I thought the
easiest way would be to look at the "Jet OLEDB:Link Datasource" property of
the table object in the ADOX.catalog. But trying to access the tables
collection is a performance killer. Here's the code:

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

Set cnn = CurrentProject.Connection
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables(strTable) ' hangs up for a good 10 seconds on
this line
If tbl.Type = "LINK" Then _
getSourceMDB = tbl.Properties("Jet OLEDB:Link Datasource").value


Thanks for any suggestions.

 

[Tom Wickerath]

I've never attempted to use ADOX to obtain this information, so I don't know
if it would be slow on my machine, or if this is a configuration issue on
your PC. Here is an abbreviated portion of a Form_Open procedure that I use
to get the same information:


Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

'PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
'http://support.microsoft.com/?id=306435
'
'ADO has the ability to open row-level locking; DAO does not.
'The following code is used to implement row-level locking in DAO.
'If the database is opened first in row-level locking in ADO,
'subsequent attempts to open the database in ADO and DAO will use the
'same mode.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strBEDatabase As String

'Get the path to the BE database, using the connect property in the
Jet4AdminMessage table
Set db = CurrentDb()
Set tdf = db.TableDefs("Jet4AdminMessage")
strBEDatabase = Mid(tdf.Connect, 11)


etc.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Stefan Hoffmann]

hi Eric,

Why is any access to items in the tables collection (and perhaps other
collections in ADOX) so incredibly slow?

Any reason why you don't use DAO instead of ADOX here?


mfG
--> stefan <--

 

[David W. Fenton]

Ultimately what I am trying to do is get the back-end database
path based on a particular linked table in the front-end database.
I thought the easiest way would be to look at the "Jet OLEDB:Link
Datasource" property of the table object in the ADOX.catalog. But
trying to access the tables collection is a performance killer.

First off, is this from an Access database? If so, then there is no
such thing as an Access linked table that uses OLEDB to access Jet
data.

It's silly to use any variety of ADO for this when DAO provides it
without any complications at all.

 

[ericgj]

Thanks for everyone's suggestions. My first attempt was to use DAO and it
does work fine, and faster than ADO.

My concern with using DAO however is that it does not appear to refresh the
TableDef connect property after re-linking tables. You have to quit Access
and go back in. This is the case even if you try to do RefreshLink().

I had written a program using DAO to re-link tables, but anytime then I
tried to access a linked table through ADO it gave me an error. So I
re-wrote the re-linking program using ADO (and I think that the Linked Table
Manager also links using ADO). It works fine, except after you re-link, that
DAO tabledef connect property is not refreshed.

Maybe there is some kind of DAO/ADO incompatibility? It is not a very big
deal practically speaking and I will look for some kind of work-around.

The general question however remains: why is ADOX tables collection so
incredibly slow, and is there any workaround short of using DAO?

Eric

 

[Tom Wickerath]

I had written a program using DAO to re-link tables, ...

You might want to try Access MVP Armen Stein's relinking code, which he just
recently made available on his web site:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

It's the first download listed, "J Street Access Relinker".


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Paul Shapiro]

With DAO, after refreshing all the linked table connections, and using
..RefreshLink on each, you can try currentdb.tabledefs.refresh. I have
similar code and haven't seen this problem at all. Your code should close
any open forms before updating the link properties.

 

[David W. Fenton]

With DAO, after refreshing all the linked table connections, and
using .RefreshLink on each, you can try
currentdb.tabledefs.refresh. I have similar code and haven't seen
this problem at all. Your code should close any open forms before
updating the link properties.

Any call to CurrentDB refreshes all collections (which is why
CurrentDB is so much slower than DBEngine(0)(0), which does not
refresh collections), so a manual refresh is unnecessary.

Where it *doesn't* refresh is if you've cached a db reference
created with CurrentDB. For instance:

Set db = CurrentDB

[...]

db.TableDefs.Append

In that case, you'd need to do a refresh of the TableDefs collection
to see the newly appended tables (or linked tables).

But if you're doing this:

Set db = CurrentDB

[...]

db.TableDefs.Append

[...]

For Each tdf in CurrentDB.TableDefs
[...]
Next tdf

there would be no need to refresh the TableDefs collection, since
you're using a new instance of CurrentDB, which initializes in all
cases with a refresh of all collections.

 

[David W. Fenton]

Thanks for everyone's suggestions. My first attempt was to use
DAO and it does work fine, and faster than ADO.

My concern with using DAO however is that it does not appear to
refresh the TableDef connect property after re-linking tables.
You have to quit Access and go back in. This is the case even if
you try to do RefreshLink().

I explained this in another post. If in your DAO code, you're using
a database variable initialized with CurrentDB and then appending
linked tables, you need to refresh the TableDefs collection before
doing anything with the linked tables.

For what it's worth, my memory is that if you add linked tables
while the MDB is open in the Access UI and displaying the tables,
you won't see the new tables listed until you go to, say, queries,
and come back to tables. This is a problem of the Access UI, not of
the actual linked tables being available for use (as long as you've
refreshed the TableDefs collection of any database variable you
initialized in code before appending new linked tables).

I had written a program using DAO to re-link tables, but anytime
then I tried to access a linked table through ADO it gave me an
error.

What error? How were you "accessing a linked table" with ADO?

Why would you need to do that in the first place? DAO should
suffice.

So I
re-wrote the re-linking program using ADO (and I think that the
Linked Table Manager also links using ADO).

I may be wrong on this, but I don't think linked tables are
creatable through anything other than DAO (or TransferDatabase, I
guess).

It works fine, except after you re-link, that
DAO tabledef connect property is not refreshed.

See above -- it's probably a matter of refreshing the TableDefs
collection on a cached database reference.

Maybe there is some kind of DAO/ADO incompatibility?

Mixing the two seems inadvisable to me. I never use ADO for anything
at all, since linked tables by definition use Jet, and ADO is only
useful for accessing data *other* than Jet (and for the handful of
features ADO offers that DAO lacks).

It is not a very big
deal practically speaking and I will look for some kind of
work-around.

The general question however remains: why is ADOX tables
collection so incredibly slow, and is there any workaround short
of using DAO?

I've never ever under any circumstances in about 13 years of
professional Access development needed to use the ADOX tables
collection. DAO has always served perfectly.

My recommendation is to stop using ADO. Period.

 

[Arvin Meyer [MVP]]

I've never ever under any circumstances in about 13 years of
professional Access development needed to use the ADOX tables
collection. DAO has always served perfectly.

My recommendation is to stop using ADO. Period.

I have 16+ years using Access and I agree with David. While I've learned
some ADO, "just because it's there" I have never had a compelling need to
use it, even with SQL-Server tables, except, of course, with Access Projects
(ADPs) and I no longer use them. Had I ever had a performance problem with
DAO, I may have been tempted, but that hasn't been the case.

 

[ericgj]

I would gladly switch to use DAO, I've found it generally faster and less of
a pain. And given Microsoft support for ADODB seems to be vanishing, there
is no reason to use it for the sake of some future portability.

Is there a safe equivalent in DAO to the "SELECT @@IDENTITY" syntax, to
select the last value of autonumber/key field after an INSERT within a
transaction? That is the one reason I am using ADODB vs DAO in a number of
my procedures.
(see http://support.microsoft.com/kb/232144)

Thanks again for your help.
Eric

 

[Tony Toews [MVP]]

Is there a safe equivalent in DAO to the "SELECT @@IDENTITY" syntax, to
select the last value of autonumber/key field after an INSERT within a
transaction? That is the one reason I am using ADODB vs DAO in a number of
my procedures.
(see http://support.microsoft.com/kb/232144)

I think that works in DAO. IIRC I was quite surprised when someone
mentioned that recently as I never knew that.

For DAO I use

RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I think that works in DAO.

Indeed, it does -- it's not an ADO feature, but a Jet 4 feature.

IIRC I was quite surprised when someone
mentioned that recently as I never knew that.

For DAO I use

RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

I have a lot of legacy code that was written to run in A97, and we
had to add records in a recordset if we wanted to return the PK of
the newly-added record. I'm not sure why you wouldn't just do:

rs.AddNew
[set field values]
lngTableListID = rs!stlid
rs.Update

Dunno why you need to go to the last modified record if you're on it
already.

 

[Tony Toews [MVP]]

I have a lot of legacy code that was written to run in A97, and we
had to add records in a recordset if we wanted to return the PK of
the newly-added record. I'm not sure why you wouldn't just do:

rs.AddNew
[set field values]
lngTableListID = rs!stlid
rs.Update

Dunno why you need to go to the last modified record if you're on it
already.

I found the first example somewhere, somewhen so I've kept using it.
Also your example won't work in SQL Server or other environment where
the autonumber value isn't created until the record is updated.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I have a lot of legacy code that was written to run in A97, and we
had to add records in a recordset if we wanted to return the PK of
the newly-added record. I'm not sure why you wouldn't just do:

rs.AddNew
[set field values]
lngTableListID = rs!stlid
rs.Update

Dunno why you need to go to the last modified record if you're on
it already.

I found the first example somewhere, somewhen so I've kept using
it. Also your example won't work in SQL Server or other
environment where the autonumber value isn't created until the
record is updated.

True. But with SQL Server, you had SELECT @@IDENTITY available to
you before Jet 4 did, so you didn't have to add records with a
recordset -- you could use a SQL APPEND and then retrieve the
last-added identity value.

(not that I was doing SQL Server work back then; I also don't have
any SQL Server back ends that use anything other than
custom-assigned PK values)

 

[Tony Toews [MVP]]

But with SQL Server, you had SELECT @@IDENTITY available to
you before Jet 4 did, so you didn't have to add records with a
recordset -- you could use a SQL APPEND and then retrieve the
last-added identity value.

I also thought that triggers adding additional records in other tables
could cause you problems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I also thought that triggers adding additional records in other
tables could cause you problems.

Hmm. Dunno about that. That predated my work with SQL Server.