Slowdown when using A2000 frontend with A97 backend on network

[Bradley]

Has anyone else noticed this problem? I converted the back-end to A2000
and the performance problem was fixed. We supply a 97 and 2000 version
of our software so we kept the backend in A97 to make upgrading simple
for users. We've done it like that for years but a new client has been
having severe performance issues... solved by converting the backend to
2000.
--
regards,

Bradley

[Galina]

Nope. My 2 database applications became noticably faster for users
with front-end converted to Windows 2000. Back-end is still windows
97, because some users are not converted yet.
Galina
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<40******@nexus.comcen.com.au>...

Has anyone else noticed this problem? I converted the back-end to A2000
and the performance problem was fixed. We supply a 97 and 2000 version
of our software so we kept the backend in A97 to make upgrading simple
for users. We've done it like that for years but a new client has been
having severe performance issues... solved by converting the backend to
2000.

[Allen Browne]

There is a performance hit working across versions, because A2000 uses JET
4, and the calls must be converted to JET 3.5 to read the data form the A97
back end. The hit is noticable, but not dramatic.

If you have "severe" performance issues, it is more likely associated with
some of the guff in A2000 that needs to be turned off. Name AutoCorrect is
the main culprit. Subdatasheets can't be fixed since they cannot be defined
on the A97 tables. Turning off Row Level locking is also worthwhile - we
found some concurrency issues when A2000 tried to run this and the A97 back
end does not support it.

For general guidance on performance with A2000, see:
http://www.granite.ab.ca/access/performancefaq.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:40******@nexus.comcen.com.au...

Has anyone else noticed this problem? I converted the back-end to A2000
and the performance problem was fixed. We supply a 97 and 2000 version
of our software so we kept the backend in A97 to make upgrading simple
for users. We've done it like that for years but a new client has been
having severe performance issues... solved by converting the backend to
2000.

[Bradley]

Allen Browne wrote:

There is a performance hit working across versions, because A2000
uses JET 4, and the calls must be converted to JET 3.5 to read the
data form the A97 back end. The hit is noticable, but not dramatic.

If you have "severe" performance issues, it is more likely associated
with some of the guff in A2000 that needs to be turned off. Name
AutoCorrect is the main culprit. Subdatasheets can't be fixed since
they cannot be defined on the A97 tables. Turning off Row Level
locking is also worthwhile - we found some concurrency issues when
A2000 tried to run this and the A97 back end does not support it.

For general guidance on performance with A2000, see:
http://www.granite.ab.ca/access/performancefaq.htm
Ta. I'm aware of most of the things you listed (learned them the "hard"
way:)

It's solved by converting to A2000 anyway :)
--
regards,

Bradley

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:40******@nexus.comcen.com.au...

Has anyone else noticed this problem? I converted the back-end to
A2000 and the performance problem was fixed. We supply a 97 and 2000
version of our software so we kept the backend in A97 to make
upgrading simple for users. We've done it like that for years but a
new client has been having severe performance issues... solved by
converting the backend to 2000.

--
regards,

Bradley

[Bradley]

Galina wrote:

Nope. My 2 database applications became noticably faster for users
with front-end converted to Windows 2000. Back-end is still windows
97, because some users are not converted yet.
Galina
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40******@nexus.comcen.com.au>...

Has anyone else noticed this problem? I converted the back-end to
A2000 and the performance problem was fixed. We supply a 97 and 2000
version of our software so we kept the backend in A97 to make
upgrading simple for users. We've done it like that for years but a
new client has been having severe performance issues... solved by
converting the backend to 2000.

We have been running this configuration too for a number of years but a
new client has had major problems (they refuse to use it as it's too
slow). I tested it locally and it was slow for me as well... but yet
other clients are running fine). Converting the back-end to A2000 made a
huge difference.
--
regards,

Bradley

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

Subdatasheets can't be fixed since they cannot be defined
on the A97 tables.

Eh?

I just opened an A2K MDB and linked to A97 data. The linked tables
in the front end automatically had linked subdatasheets where there
were unambiguous relationships defined in the A97 back end. Setting
those linked tables' SUBDATASHEET property to NONE might very well
have a performance benefit, just as it does with pure A2K links.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:40********@nexus.comcen.com.au:

It's solved by converting to A2000 anyway :)

I ran a client's application with an A97 back end and A2K front ends
while I did major redevelopment of the app in A97. When we converted
the back end to A2K, we suddenly experienced frequent reports of
corruption (though no real corruption), and had one occurence of the
lost AutoNumber seed.

We eventually got everything running right by making sure all the
workstations had SR1a or higher and Jet 4.0 SP6 (which was the
latest back then). This meant visiting and updating every
workstation, including Win2K service packs on all the workstations
still running the original release of Win2K (about half of them --
Jet SP6 will only install on Win2K SP1 and higher).

The performance improvement was quite minimal.

Worth it in the long run?

I don't know.

But the lack of corruption might be worth something to somebody.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bradley]

David W. Fenton wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:40********@nexus.comcen.com.au:

It's solved by converting to A2000 anyway :)

I ran a client's application with an A97 back end and A2K front ends
while I did major redevelopment of the app in A97. When we converted
the back end to A2K, we suddenly experienced frequent reports of
corruption (though no real corruption), and had one occurence of the
lost AutoNumber seed.

We eventually got everything running right by making sure all the
workstations had SR1a or higher and Jet 4.0 SP6 (which was the
latest back then). This meant visiting and updating every
workstation, including Win2K service packs on all the workstations
still running the original release of Win2K (about half of them --
Jet SP6 will only install on Win2K SP1 and higher).

The performance improvement was quite minimal.

Worth it in the long run?

I don't know.

But the lack of corruption might be worth something to somebody.

Yeah...

The difference for me was dramatic.. probably caused by a number of
factors.
--
regards,

Bradley

[Allen Browne]

By "fixed", I intended "turned off".

Last time I visted that issue, David, I could not get the SubDatasheetName
setting to hold "[None]" in a linked table attached to an A97 b.e. I assumed
that was because the b.e. did not support the property, so the linked table
in the f.e. did not hold the property.

The result is that you DO get the performance loss and Datasheet issues
related to the presence of subdatasheets in these linked tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

Subdatasheets can't be fixed since they cannot be defined
on the A97 tables.

Eh?

I just opened an A2K MDB and linked to A97 data. The linked tables
in the front end automatically had linked subdatasheets where there
were unambiguous relationships defined in the A97 back end. Setting
those linked tables' SUBDATASHEET property to NONE might very well
have a performance benefit, just as it does with pure A2K links.

[Dimitri Furman]

On Apr 23 2004, 07:27 am, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote in news:40***********************@freenews.iinet.net. au:

By "fixed", I intended "turned off".

Last time I visted that issue, David, I could not get the
SubDatasheetName setting to hold "[None]" in a linked table attached
to an A97 b.e. I assumed that was because the b.e. did not support the
property, so the linked table in the f.e. did not hold the property.

That is correct, however you can add that property as a custom property to
the backend tabledef in A97 and set it to "[None]". Works just fine.

--
remove a 9 to reply by email

[Allen Browne]

Of course!

CreateProperty on the TableDef, named "SubdatasheetName", Type dbText, Value
"[None]". Love it!

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...

On Apr 23 2004, 07:27 am, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote in news:40***********************@freenews.iinet.net. au:

By "fixed", I intended "turned off".

Last time I visted that issue, David, I could not get the
SubDatasheetName setting to hold "[None]" in a linked table attached
to an A97 b.e. I assumed that was because the b.e. did not support the
property, so the linked table in the f.e. did not hold the property.

That is correct, however you can add that property as a custom property to
the backend tabledef in A97 and set it to "[None]". Works just fine.

--
remove a 9 to reply by email

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

By "fixed", I intended "turned off".

Last time I visted that issue, David, I could not get the
SubDatasheetName setting to hold "[None]" in a linked table
attached to an A97 b.e. I assumed that was because the b.e. did
not support the property, so the linked table in the f.e. did not
hold the property.
It seems to work when I change it.
The result is that you DO get the performance loss and Datasheet
issues related to the presence of subdatasheets in these linked
tables.

I don't see it.

Maybe you had an earlier version of A2K than SR1a?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...

On Apr 23 2004, 07:27 am, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

> By "fixed", I intended "turned off".
>
> Last time I visted that issue, David, I could not get the
> SubDatasheetName setting to hold "[None]" in a linked table
> attached to an A97 b.e. I assumed that was because the b.e. did
> not support the property, so the linked table in the f.e. did
> not hold the property.

That is correct, however you can add that property as a custom
property to the backend tabledef in A97 and set it to "[None]".
Works just fine.

Of course!

CreateProperty on the TableDef, named "SubdatasheetName", Type
dbText, Value "[None]". Love it!

Er, what are you talking about?

The subdatasheet property is a property of the table link, and if
you turn it off in the front end, you don't need to turn it off in
the back end. In the case of an A97 back end, there's nothing to
turn off in the back end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Apr 23 2004, 03:49 pm, "David W. Fenton" <dX********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.86:

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...

On Apr 23 2004, 07:27 am, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

> By "fixed", I intended "turned off".
>
> Last time I visted that issue, David, I could not get the
> SubDatasheetName setting to hold "[None]" in a linked table
> attached to an A97 b.e. I assumed that was because the b.e. did
> not support the property, so the linked table in the f.e. did not
> hold the property.

That is correct, however you can add that property as a custom
property to the backend tabledef in A97 and set it to "[None]".
Works just fine.
Of course!

CreateProperty on the TableDef, named "SubdatasheetName", Type dbText,
Value "[None]". Love it!

Er, what are you talking about?

The subdatasheet property is a property of the table link

No, it's a property of the base table in the backend.
, and if
you turn it off in the front end, you don't need to turn it off in
the back end.
You can't turn it off in the front end, cause it's not there. Access will
not complain if you try to do it in table link's property sheet, but it
will silently ignore your changes.
In the case of an A97 back end, there's nothing to
turn off in the back end.

Yes, but that's the point: You give it something to turn off in the back
end. It's just a property, Access doesn't care how it was created, as long
as the name is SubdatasheetName and the type is dbText.

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Apr 23 2004, 03:49 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.86:

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
On Apr 23 2004, 07:27 am, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

> By "fixed", I intended "turned off".
>
> Last time I visted that issue, David, I could not get the
> SubDatasheetName setting to hold "[None]" in a linked table
> attached to an A97 b.e. I assumed that was because the b.e.
> did not support the property, so the linked table in the f.e.
> did not hold the property.

That is correct, however you can add that property as a custom
property to the backend tabledef in A97 and set it to "[None]".
Works just fine.

Of course!

CreateProperty on the TableDef, named "SubdatasheetName", Type
dbText, Value "[None]". Love it!
Er, what are you talking about?

The subdatasheet property is a property of the table link

No, it's a property of the base table in the backend.

If so, then it shouldn't exist in the front end when the back end is
of a version that has no subdatasheets. Yet, it does. And it can be
set to [None] in the table link, even though the back end doesn't
support it.

And if you *don't* set it to none, it works.

So, subdatasheet functionality is all in the front end in the case
of a link to A97. And that's where you can remove it.

, and if
you turn it off in the front end, you don't need to turn it off
in the back end.

You can't turn it off in the front end, cause it's not there.

Have you tried it?

I did.

It's there.

It works just like it does with an A2K back end.

And you can turn it off.

And it stays off.
Access will not complain if you try to do it in table link's
property sheet, but it will silently ignore your changes.

Not it my copy of Access.

In the case of an A97 back end, there's nothing to
turn off in the back end.

Yes, but that's the point: You give it something to turn off in
the back end. It's just a property, Access doesn't care how it was
created, as long as the name is SubdatasheetName and the type is
dbText.

I didn't do anything to the back end, yet, I was able to turn it off
in the front end.

And I was able to use the subdatasheet in the A2K front end before I
turned it off.

Test it, Dmitri.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Apr 23 2004, 10:25 pm, "David W. Fenton" <dX********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Apr 23 2004, 03:49 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.86:

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
> On Apr 23 2004, 07:27 am, "Allen Browne"
> <Al*********@SeeSig.Invalid> wrote in
> news:40***********************@freenews.iinet.net. au:
>
> > By "fixed", I intended "turned off".
> >
> > Last time I visted that issue, David, I could not get the
> > SubDatasheetName setting to hold "[None]" in a linked table
> > attached to an A97 b.e. I assumed that was because the b.e.
> > did not support the property, so the linked table in the f.e.
> > did not hold the property.
>
> That is correct, however you can add that property as a custom
> property to the backend tabledef in A97 and set it to "[None]".
> Works just fine.

Of course!

CreateProperty on the TableDef, named "SubdatasheetName", Type
dbText, Value "[None]". Love it!

Er, what are you talking about?

The subdatasheet property is a property of the table link
No, it's a property of the base table in the backend.

If so, then it shouldn't exist in the front end when the back end is
of a version that has no subdatasheets. Yet, it does. And it can be
set to [None] in the table link, even though the back end doesn't
support it.

Some tests, using one A97 database, and one A2002 database (BE and FE):

1. In A97, create a brand new table "Table2". Close A97, open A2002, link
to that table. From Debug window of A2002, run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"

2. Close A2002, open A97. From Debug window, run
Currentdb.TableDefs("Table2").Properties.Append _
Currentdb.TableDefs("Table2").CreateProperty("Subd atasheetName",dbText, _
"[None]")
Still in A97, from Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]

3. Close A97, open A2002.
From Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]

Note that we never opened the Table2 link in A2002 in design view, and
never did anything with the SubdatasheetName property in A2002, yet it now
appears that it is set to "[None]". In a way, we can call this fenomenon
"property passthrough" - even though we look at the Properties collection
of the table link, we are seeing a property of the base table.

To confirm this, rename the A97 database to break the link, then from A2002
run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"

Now, create a brand new Table3 in A97. Close A97, open A2002, link to
Table3. Open Table3 in design view, go to property sheet of the table, set
the SubdatasheetName property to "[None]". Save and close the table. Reopen
the table in design view again. Observe that the SubdatasheetName property
is still set to "[Auto]".
So, subdatasheet functionality is all in the front end in the case
of a link to A97. And that's where you can remove it.
The functionality is there because it defaults to "[Auto]" when no property
is found. As we see above in Step 1, there is no SubdatasheetName property
on a freshly created A2002 table link to a A97 base table (even though
Access UI does show "[Auto]").

, and if
you turn it off in the front end, you don't need to turn it off in
the back end.

You can't turn it off in the front end, cause it's not there.

Have you tried it?

Well, yes, on a couple of occasions.
It's there.

It works just like it does with an A2K back end.

And you can turn it off.

And it stays off.

I suppose this could be due to differences between A2K and A2002, but this
is definitely not the way it works here. It would be interesting to see the
results of the above tests in your environment.

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:

On Apr 23 2004, 10:25 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Apr 23 2004, 03:49 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.86:

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40***********************@freenews.iinet.net. au:

> "Dimitri Furman" <df*****@cloud99.net> wrote in message
> news:Xn****************************@127.0.0.1...
>> On Apr 23 2004, 07:27 am, "Allen Browne"
>> <Al*********@SeeSig.Invalid> wrote in
>> news:40***********************@freenews.iinet.net. au:
>>
>> > By "fixed", I intended "turned off".
>> >
>> > Last time I visted that issue, David, I could not get the
>> > SubDatasheetName setting to hold "[None]" in a linked table
>> > attached to an A97 b.e. I assumed that was because the b.e.
>> > did not support the property, so the linked table in the
>> > f.e. did not hold the property.
>>
>> That is correct, however you can add that property as a
>> custom property to the backend tabledef in A97 and set it to
>> "[None]". Works just fine.
>
> Of course!
>
> CreateProperty on the TableDef, named "SubdatasheetName", Type
> dbText, Value "[None]". Love it!

Er, what are you talking about?

The subdatasheet property is a property of the table link

No, it's a property of the base table in the backend.
If so, then it shouldn't exist in the front end when the back end
is of a version that has no subdatasheets. Yet, it does. And it
can be set to [None] in the table link, even though the back end
doesn't support it.

Some tests, using one A97 database, and one A2002 database (BE and
FE):

1. In A97, create a brand new table "Table2". Close A97, open
A2002, link to that table. From Debug window of A2002, run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"

First off, subDataSheet is relevant *only* when there's a
relationship available for the subDataSheet to use by default. So,
instead, what you want to do is:

1. create two tables that have RI enforced between them, and link to
*those*. The properties will still show NOT FOUND, but if you open
the linked table, there will be a + sign for the subDataSheet, and
it will be automatically populated if you click it.

Yet, even after utilizing the subdatasheet in the UI, the property
will not be created (since it's not using a stored property, but a
default value for a property that has not yet been created).
2. Close A2002, open A97. From Debug window, run
Currentdb.TableDefs("Table2").Properties.Append _
Currentdb.TableDefs("Table2").CreateProperty ("SubdatasheetName",dbT ext, _ "[None]")
Still in A97, from Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]
So far as I can see, this need be done only in the front end. And
*should* be done only in the front end, as if you do it in the back
end, it's not cached, and has to be looked up from the back end.
Setting it in the front end means there's no need to go to the back
end.
3. Close A97, open A2002.
From Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]
Yes, properties not cached in the tablelink definition are looked up
from back end. That's why you should set this property in the front
end, to avoid the lag time that may occur in looking up the property
from the back end.
Note that we never opened the Table2 link in A2002 in design view,
and never did anything with the SubdatasheetName property in
A2002, yet it now appears that it is set to "[None]". In a way, we
can call this fenomenon "property passthrough" - even though we
look at the Properties collection of the table link, we are seeing
a property of the base table.
But you don't have to do *anything* to the A97 database -- you need
only change the property of the linked table.

In regard to custom properties (i.e., non-Jet properties), Access
has always left them uncreated until you set a value. However, some
properties have defaults (which is the case with SubDataSheetName),
and the default is "[Auto]", which tells Access to use RI to
determine the subdatasheet to be displayed. But even *using* that
default by viewing the subdatasheet does not actually save the
property.
To confirm this, rename the A97 database to break the link, then
from A2002 run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"
The fact that the property is not stored in the link does not mean
it can *not* be stored in the link.
Now, create a brand new Table3 in A97. Close A97, open A2002, link
to Table3. Open Table3 in design view, go to property sheet of the
table, set the SubdatasheetName property to "[None]". Save and
close the table. Reopen the table in design view again. Observe
that the SubdatasheetName property is still set to "[Auto]".

This is not what happens in A2K. The change to the subdatasheet
property in the A2K database is permanent.

Indeed, if you delete the link and recreate it, if you haven't
compacted after the delete, the recreated link inherits the
subdatasheetname property you had assigned before the deletion.

So, subdatasheet functionality is all in the front end in the
case of a link to A97. And that's where you can remove it.

The functionality is there because it defaults to "[Auto]" when no
property is found. As we see above in Step 1, there is no
SubdatasheetName property on a freshly created A2002 table link to
a A97 base table (even though Access UI does show "[Auto]").

One doesn't have to muck about with adding A2K properties to A97
back end tables -- one needs only to set that property to [None] for
all linked tables that participate in relationships.

, and if
you turn it off in the front end, you don't need to turn it off
in the back end.

You can't turn it off in the front end, cause it's not there.

Have you tried it?

Well, yes, on a couple of occasions.

Have you tried it with A2K, which is the subject of this thread?

It's there.

It works just like it does with an A2K back end.

And you can turn it off.

And it stays off.

I suppose this could be due to differences between A2K and A2002,
but this is definitely not the way it works here. It would be
interesting to see the results of the above tests in your
environment.

You simply described the way Access works with its properties --
until a value is assigned, the property is not actually created.
That does *not* mean that the lack of the property assignment does
not cause it to be used, since the default property value will be
used. And that use of the default property value (which has to be
looked up from the back end) seems to me to be the main cause of the
performance hit.

The question to be resolved is whether or not the links in A2K2
behave differently than the ones in A2K. I would suggest that you
compact your front end before deleting and recreating links, and
that you use only tables that have relationships defined on them,
since those are the only ones where the default property value will
have any meaning.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Apr 24 2004, 01:09 pm, "David W. Fenton" <dX********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:

Some tests, using one A97 database, and one A2002 database (BE and
FE):

1. In A97, create a brand new table "Table2". Close A97, open
A2002, link to that table. From Debug window of A2002, run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"
First off, subDataSheet is relevant *only* when there's a
relationship available for the subDataSheet to use by default. So,
instead, what you want to do is:

1. create two tables that have RI enforced between them, and link to
*those*. The properties will still show NOT FOUND, but if you open
the linked table, there will be a + sign for the subDataSheet, and
it will be automatically populated if you click it.

I'm not sure why the presence of RI has any relevance to the presence of
the property and the value of the property, which is what we are
discussing, I think. Sure, RI plays a role where subdatasheets are
concerned, but it's rather a separate issue.
Yet, even after utilizing the subdatasheet in the UI, the property
will not be created (since it's not using a stored property, but a
default value for a property that has not yet been created).
Yes, if there is no property, the default behavior is [Auto], but we are
talking about ways to change that behavior via the use of the property,
rather than the behavior itself.

2. Close A2002, open A97. From Debug window, run
Currentdb.TableDefs("Table2").Properties.Append _
Currentdb.TableDefs("Table2").CreateProperty ("SubdatasheetName",dbT
ext, _ "[None]")
Still in A97, from Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]

So far as I can see, this need be done only in the front end. And
*should* be done only in the front end, as if you do it in the back
end, it's not cached, and has to be looked up from the back end.
Setting it in the front end means there's no need to go to the back
end.

Until it has been clearly demonstrated that property lookup from the
back end has a marked effect on performance, I think that as a practical
matter it is usually more beneficial to set the property on the base table
once and for all and not worry about it every time you create a link.
You may need to link to the back end from multiple places. Depending on
situation, this may or may not be a consideration.

[snippage]
But you don't have to do *anything* to the A97 database -- you need
only change the property of the linked table.
Either way will work. I would prefer to use base tables, as they are less
volative than links, but YMMV.
The fact that the property is not stored in the link does not mean
it can *not* be stored in the link.
You are right, you can append the prop to the link, and Access will respect
that just as it will a property of the base table.

Now, create a brand new Table3 in A97. Close A97, open A2002, link
to Table3. Open Table3 in design view, go to property sheet of the
table, set the SubdatasheetName property to "[None]". Save and
close the table. Reopen the table in design view again. Observe that
the SubdatasheetName property is still set to "[Auto]".

This is not what happens in A2K. The change to the subdatasheet
property in the A2K database is permanent.

As far as I can tell, in A2002 you can only create the property on the link
programatically.
Indeed, if you delete the link and recreate it, if you haven't
compacted after the delete, the recreated link inherits the
subdatasheetname property you had assigned before the deletion.

I would guess that it rather inherits it from the back end. I don't have
A2K here to test, but I wouldn't be surprised to find out that when you set
the property in the Access UI in the front end, it is actually set on the
base table in the back end, so that when you recreate the link, you are
seeing the property in the back end. But this is just idle speculation and
can be safely ignored <g>.

So, subdatasheet functionality is all in the front end in the case of
a link to A97. And that's where you can remove it.

The functionality is there because it defaults to "[Auto]" when no
property is found. As we see above in Step 1, there is no
SubdatasheetName property on a freshly created A2002 table link to a
A97 base table (even though Access UI does show "[Auto]").

One doesn't have to muck about with adding A2K properties to A97
back end tables -- one needs only to set that property to [None] for
all linked tables that participate in relationships.

Assuming you can - and in A2002 you can not, so you have to muck with
properties from code anyway.

> , and if
> you turn it off in the front end, you don't need to turn it off in
> the back end.

You can't turn it off in the front end, cause it's not there.

Have you tried it?

Well, yes, on a couple of occasions.

Have you tried it with A2K, which is the subject of this thread?

No, I don't have A2K available. I joined this thread to point out a
solution that works regardless of the front end version in use.

It's there.

It works just like it does with an A2K back end.

And you can turn it off.

And it stays off.

I suppose this could be due to differences between A2K and A2002, but
this is definitely not the way it works here. It would be interesting
to see the results of the above tests in your environment.

You simply described the way Access works with its properties --
until a value is assigned, the property is not actually created.
That does *not* mean that the lack of the property assignment does
not cause it to be used, since the default property value will be
used. And that use of the default property value (which has to be
looked up from the back end) seems to me to be the main cause of the
performance hit.

The lack of the property causes the default *behavior* to be invoked. There
is no property in the back end to look up. If I had to speculate about the
cause of the perf hit, I'd say that it is caused by looking for related
tables in the back end, and scanning the FKs of the related tables, which
is a much more expensive operation than looking up a property.

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Apr 24 2004, 01:09 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:

Some tests, using one A97 database, and one A2002 database (BE
and FE):

1. In A97, create a brand new table "Table2". Close A97, open
A2002, link to that table. From Debug window of A2002, run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: "Property not found"
First off, subDataSheet is relevant *only* when there's a
relationship available for the subDataSheet to use by default.
So, instead, what you want to do is:

1. create two tables that have RI enforced between them, and link
to *those*. The properties will still show NOT FOUND, but if you
open the linked table, there will be a + sign for the
subDataSheet, and it will be automatically populated if you click
it.

I'm not sure why the presence of RI has any relevance to the
presence of the property and the value of the property, which is
what we are discussing, I think. Sure, RI plays a role where
subdatasheets are concerned, but it's rather a separate issue.

If you link to a table that has no relationships with other tables
and open it in datasheet view, there is no + for a subdatasheet.
This would surely be a time savings in opening the datasheet because
there is nothing to look up (i.e., with the default [Auto] property,
the relationships will have to be examined to see what the automatic
link should be).

Now, obviously, opening datasheets for viewing is not the scenario
that's a performance drag, but the point here is that if a property
that means something only in datasheet view is having an effect on
performance in non-datasheet contexts, surely the lack of the
presence of that property in datasheet view suggests that the
performance will not be affected in non-datasheet contexts.

Yet, even after utilizing the subdatasheet in the UI, the
property will not be created (since it's not using a stored
property, but a default value for a property that has not yet
been created).

Yes, if there is no property, the default behavior is [Auto], but
we are talking about ways to change that behavior via the use of
the property, rather than the behavior itself.

And I'm suggesting that the ultimate cause of the slowdown is not
the property on the back end table, but the lack of the property on
the front end table link.

2. Close A2002, open A97. From Debug window, run
Currentdb.TableDefs("Table2").Properties.Append _
Currentdb.TableDefs("Table2").CreateProperty
("SubdatasheetName",dbT ext, _ "[None]")
Still in A97, from Debug window run
?Currentdb.TableDefs("Table2").Properties("Subdata sheetName")
Result: [None]

So far as I can see, this need be done only in the front end. And
*should* be done only in the front end, as if you do it in the
back end, it's not cached, and has to be looked up from the back
end. Setting it in the front end means there's no need to go to
the back end.

Until it has been clearly demonstrated that property lookup from
the back end has a marked effect on performance, I think that as a
practical matter it is usually more beneficial to set the property
on the base table once and for all and not worry about it every
time you create a link. . . .

Uh, I don't create links very often, just when I'm creating the
front end.
. . . You may need to link to the back end from
multiple places. Depending on situation, this may or may not be a
consideration.
But why would you delete and recreate the table links, instead of
just updating the CONNECT string?

Yes, there were scenarios in the early days of A2K where updating
the CONNECTt string of links created on one network would cause huge
performance problems when moved to another network and updated, but
I haven't seen that in a very, very long time. My assumption was
that SR1 fixed the problem entirely, since I haven't seen it at all
since that time.

I'm only guessing on that, of course, but it's a problem that occurs
so infrequently that I haven't seen fit to change my relinking code
to delete and recreate the links, and I just haven't had any issues
with any of the applications I'm creating for clients.

But you don't have to do *anything* to the A97 database -- you
need only change the property of the linked table.

Either way will work. I would prefer to use base tables, as they
are less volative than links, but YMMV.

Well, that will depend on your linking practices.

Another reason to do it in the front end is so that you can use the
property defined for the shared data tables if you need to.

The fact that the property is not stored in the link does not
mean it can *not* be stored in the link.

You are right, you can append the prop to the link, and Access
will respect that just as it will a property of the base table.

Now, create a brand new Table3 in A97. Close A97, open A2002,
link to Table3. Open Table3 in design view, go to property sheet
of the table, set the SubdatasheetName property to "[None]".
Save and close the table. Reopen the table in design view again.
Observe that the SubdatasheetName property is still set to
"[Auto]".

This is not what happens in A2K. The change to the subdatasheet
property in the A2K database is permanent.

As far as I can tell, in A2002 you can only create the property on
the link programatically.

It's available in the UI of A2K, but the stored property is not
created until you change it to something other than the default
[Auto].

Indeed, if you delete the link and recreate it, if you haven't
compacted after the delete, the recreated link inherits the
subdatasheetname property you had assigned before the deletion.

I would guess that it rather inherits it from the back end. . ..

There *is* no such property in the back end.

This is what I did:

1. in A2K link to an A97 table that participates in relationships to
other tables.

2. change the front end link to have a subdatasheet property of
[None].

3. delete the link.

4. recreate the link without changing the name of the link.

The subdatasheet property remains [None], even though it has not
been changed from the default value of [Auto].

If you try the same process and compact between steps 3 and 4, the
property comes out as [Auto], instead.
. . . I
don't have A2K here to test, but I wouldn't be surprised to find
out that when you set the property in the Access UI in the front
end, it is actually set on the base table in the back end, . . .
No, this does not happen. The first thing I checked was whether or
not the property had been added to the A97 back end table -- it had
not been.
. . . so that
when you recreate the link, you are seeing the property in the
back end. But this is just idle speculation and can be safely
ignored <g>.
It's also demonstrably wrong. There was no property added to the
back end table. That was my initial suspicion, so I checked it. And
if it were the case, a compact of the front end after deleting the
link would not change the value for the next-created link, since it
should be inheriting it from the back end.

So, subdatasheet functionality is all in the front end in the
case of a link to A97. And that's where you can remove it.

The functionality is there because it defaults to "[Auto]" when
no property is found. As we see above in Step 1, there is no
SubdatasheetName property on a freshly created A2002 table link
to a A97 base table (even though Access UI does show "[Auto]").

One doesn't have to muck about with adding A2K properties to A97
back end tables -- one needs only to set that property to [None]
for all linked tables that participate in relationships.

Assuming you can - and in A2002 you can not, so you have to muck
with properties from code anyway.

Hold on. Are you saying that in A2K2 you can't set the property for
the link to an A97 table through code, either? Or are you just
saying that it can't be done through the UI?

>> , and if
>> you turn it off in the front end, you don't need to turn it
>> off in the back end.
>
> You can't turn it off in the front end, cause it's not there.

Have you tried it?

Well, yes, on a couple of occasions.

Have you tried it with A2K, which is the subject of this thread?

No, I don't have A2K available. I joined this thread to point out
a solution that works regardless of the front end version in use.

Surely it works in code in all versions, and it would seem to me
that you'd want to do it in code, anyway, instead of manually doing
it via the UI?

It's there.

It works just like it does with an A2K back end.

And you can turn it off.

And it stays off.

I suppose this could be due to differences between A2K and
A2002, but this is definitely not the way it works here. It
would be interesting to see the results of the above tests in
your environment.

You simply described the way Access works with its properties --
until a value is assigned, the property is not actually created.
That does *not* mean that the lack of the property assignment
does not cause it to be used, since the default property value
will be used. And that use of the default property value (which
has to be looked up from the back end) seems to me to be the main
cause of the performance hit.

The lack of the property causes the default *behavior* to be
invoked. There is no property in the back end to look up. . .

But there is *something* to be looked up, the information about
whether or not the table participates in any relationships. And my
surmise is that the lookup in the relationships collection is what
is causing the slowdown.
. . . If I had
to speculate about the cause of the perf hit, I'd say that it is
caused by looking for related tables in the back end, and scanning
the FKs of the related tables, which is a much more expensive
operation than looking up a property.

Yes, and it is happening because there is no property set in the
front end. If you set the property in the front end, it doesn't have
to be looked up in the back end, whether or not there is a
slowdown.

Question for you: if you set the property in the A97 back end, and
create a link to it in A2K2, does the link have the property set in
itself, or is the property looked up from the back end?

I think to really answer this you'd have to create two links, one
where you just create the link, and one where you set the property
on the link in code, then check the property value 100K times and
see if one or the other is faster. If there's no difference, I think
we could conclude that creating the link copies the property from
the back end table to the front end, in which case your method of
adding the custom properties in the back end has no disadvantage.

If, on other hand, the link without the explicitly set property is
slower than the other one, then it indicates that your method of
depending on setting the property in the back end does not insulate
you from the entire performance hit.

I suggested for the performance test checking the property. To
really answer the question, you'd probably want to open a recordset
on the table instead, using the link, though that won't really tell
you what happens with loading recordsets in forms.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Apr 25 2004, 02:38 pm, "David W. Fenton" <dX********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.86:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

Yes, if there is no property, the default behavior is [Auto], but
we are talking about ways to change that behavior via the use of the
property, rather than the behavior itself.
And I'm suggesting that the ultimate cause of the slowdown is not
the property on the back end table, but the lack of the property on
the front end table link.

Well, the *ultimate* cause is probably the RI lookups and FK scans, but the
lack of the property either on the front end or the back end is what's
triggering it.

Until it has been clearly demonstrated that property lookup from
the back end has a marked effect on performance, I think that as a
practical matter it is usually more beneficial to set the property
on the base table once and for all and not worry about it every time
you create a link. . . .

Uh, I don't create links very often, just when I'm creating the
front end.

That's usually the case in a "build an app, deliver to client, and
occasionally support" scenario, but when you have a maze of A97 backends
all over the network that you have to link to from A2002 on the ad hoc
basic, it's different.

. . . You may need to link to the back end from
multiple places. Depending on situation, this may or may not be a
consideration.

But why would you delete and recreate the table links, instead of
just updating the CONNECT string?

Sometimes you never know that you will need to link to a table, or when you
will need to link to it. Knowing that you won't be hit by the issue adds
some comfort, and the way to achieve it is to add the prop in the back end.

I would guess that it rather inherits it from the back end. . ..

There *is* no such property in the back end.

Oh well, it was just an idle speculation that kind of made sense...
This is what I did:

1. in A2K link to an A97 table that participates in relationships to
other tables.

2. change the front end link to have a subdatasheet property of
[None].

3. delete the link.

4. recreate the link without changing the name of the link.

The subdatasheet property remains [None], even though it has not
been changed from the default value of [Auto].

If you try the same process and compact between steps 3 and 4, the
property comes out as [Auto], instead.
Looks like Access gets somewhat creative here with reusing deleted things.
I'm not sure I like it. It's good to know that it may happen though.

One doesn't have to muck about with adding A2K properties to A97
back end tables -- one needs only to set that property to [None]
for all linked tables that participate in relationships.

Assuming you can - and in A2002 you can not, so you have to muck with
properties from code anyway.

Hold on. Are you saying that in A2K2 you can't set the property for
the link to an A97 table through code, either? Or are you just
saying that it can't be done through the UI?

It cannot be done through UI. Works fine from code. Since I have to do it
from code, I might as well do it in the back end, since that will usually
make it easier for me in the long run. But we've covered that already.
Surely it works in code in all versions, and it would seem to me
that you'd want to do it in code, anyway, instead of manually doing
it via the UI?
Again, depends on the circumstances. If you have a front end with a lot
of links that you need to deliver, sure. If it's a couple of tables for an
ad hoc report, UI is quicker.

The lack of the property causes the default *behavior* to be invoked.
There is no property in the back end to look up. . .

But there is *something* to be looked up, the information about
whether or not the table participates in any relationships. And my
surmise is that the lookup in the relationships collection is what
is causing the slowdown.

Either that or actually looking up rows in related tables.

. . . If I had
to speculate about the cause of the perf hit, I'd say that it is
caused by looking for related tables in the back end, and scanning
the FKs of the related tables, which is a much more expensive
operation than looking up a property.

Yes, and it is happening because there is no property set in the
front end. If you set the property in the front end, it doesn't have
to be looked up in the back end, whether or not there is a
slowdown.

The RI and FK lookups are happening because there is no property set
anywhere. I see your point though: if it's on the front end, you save a
trip to the back end. Generally, that's a good thing, but in my experience
with using backend properties (and I use them a lot for various things),
the perf hit is so negligible, that the convenience of putting things in
one place (the back end) far outweighs it.
Question for you: if you set the property in the A97 back end, and
create a link to it in A2K2, does the link have the property set in
itself, or is the property looked up from the back end?
From the back end. Unless you also explicitly set it on the link.
I think to really answer this you'd have to create two links, one
where you just create the link, and one where you set the property
on the link in code, then check the property value 100K times and
see if one or the other is faster. If there's no difference, I think
we could conclude that creating the link copies the property from
the back end table to the front end, in which case your method of
adding the custom properties in the back end has no disadvantage.
No, it doesn't copy the property, I checked that - it rather looks in the
backend tabledef Properties collection.
If, on other hand, the link without the explicitly set property is
slower than the other one, then it indicates that your method of
depending on setting the property in the back end does not insulate
you from the entire performance hit.

That's possible, although I really doubt that the hit will be substantial.
Testing it is the only way to find out for sure - if I find the time to do
it, I'll post the results.

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:

On Apr 25 2004, 02:38 pm, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn**********************************@24.168.1 28.86:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
[]

Until it has been clearly demonstrated that property lookup from
the back end has a marked effect on performance, I think that as
a practical matter it is usually more beneficial to set the
property on the base table once and for all and not worry about
it every time you create a link. . . .

Uh, I don't create links very often, just when I'm creating the
front end.

That's usually the case in a "build an app, deliver to client, and
occasionally support" scenario, but when you have a maze of A97
backends all over the network that you have to link to from A2002
on the ad hoc basic, it's different.

But that only matters if you delete/recreate the links, rather than
updating the CONNECT string.

I have seen no reason do anything other than changing an existing
link since the early days of A2K.

. . . You may need to link to the back end from
multiple places. Depending on situation, this may or may not be
a consideration.

But why would you delete and recreate the table links, instead of
just updating the CONNECT string?

Sometimes you never know that you will need to link to a table, or
when you will need to link to it. Knowing that you won't be hit by
the issue adds some comfort, and the way to achieve it is to add
the prop in the back end.

I would guess that it rather inherits it from the back end. . ..

There *is* no such property in the back end.

Oh well, it was just an idle speculation that kind of made
sense...

This is what I did:

1. in A2K link to an A97 table that participates in relationships
to other tables.

2. change the front end link to have a subdatasheet property of
[None].

3. delete the link.

4. recreate the link without changing the name of the link.

The subdatasheet property remains [None], even though it has not
been changed from the default value of [Auto].

If you try the same process and compact between steps 3 and 4,
the property comes out as [Auto], instead.

Looks like Access gets somewhat creative here with reusing deleted
things. I'm not sure I like it. It's good to know that it may
happen though.

I certainly don't like it! I would surely never depend on it!

One doesn't have to muck about with adding A2K properties to
A97 back end tables -- one needs only to set that property to
[None] for all linked tables that participate in relationships.

Assuming you can - and in A2002 you can not, so you have to muck
with properties from code anyway.

Hold on. Are you saying that in A2K2 you can't set the property
for the link to an A97 table through code, either? Or are you
just saying that it can't be done through the UI?

It cannot be done through UI. Works fine from code. Since I have
to do it from code, I might as well do it in the back end, since
that will usually make it easier for me in the long run. But we've
covered that already.

Well, your philosophy here is different from mine. I would set the
subdatasheet property in the front ends where I didn't want to use
it, rather than setting it in the back end.

Of course, I'm assuming that a [None] setting would prohibit
subdatasheets in the front end, which is probably an erroneous
assumption.

I'm just bothered by the idea of adding A2K+ properties to an A97
back end.

Of course, I also don't have any cases where I'd be recommending
this in the first place, as I don't have any mixed environments to
support!

Surely it works in code in all versions, and it would seem to me
that you'd want to do it in code, anyway, instead of manually
doing it via the UI?

Again, depends on the circumstances. If you have a front end with
a lot of links that you need to deliver, sure. If it's a couple of
tables for an ad hoc report, UI is quicker.

I see an inconsistency here, in that you're recommending creating a
subdatasheet property in the A97 back end tables (which cannot be
done through the front end) and saying that it's a disadvantage to
be unable to set the property on a link to an A97 table in an A2K2
front end.

You see the inconsistency here?

If you've got code, you can use it anywhere. One would assume that
code used to set the property in the A97 back end would be smart
enough to check to see if it exists before creating it, so the same
code would be able to work just fine in an A2K+ front end.

Since what you want to do (i.e., setting the property in the A97
back end) can't be done in any UI, I don't see why setting the
property in the front end through code through code should be a
hurdle.

The lack of the property causes the default *behavior* to be
invoked. There is no property in the back end to look up. . .

But there is *something* to be looked up, the information about
whether or not the table participates in any relationships. And
my surmise is that the lookup in the relationships collection is
what is causing the slowdown.

Either that or actually looking up rows in related tables.

. . . If I had
to speculate about the cause of the perf hit, I'd say that it is
caused by looking for related tables in the back end, and
scanning the FKs of the related tables, which is a much more
expensive operation than looking up a property.

Yes, and it is happening because there is no property set in the
front end. If you set the property in the front end, it doesn't
have
to be looked up in the back end, whether or not there is a
slowdown.

The RI and FK lookups are happening because there is no property
set anywhere. I see your point though: if it's on the front end,
you save a trip to the back end. Generally, that's a good thing,
but in my experience with using backend properties (and I use them
a lot for various things), the perf hit is so negligible, that the
convenience of putting things in one place (the back end) far
outweighs it.

Hmm. I did some testing on this, checking the Properties collection
of a table link by index (going from 0 to 13), and certain
properties where clearly stored in the front end (returned
immediately) and certain others were looked up from the back end,
and caused a noticeable delay. I was guessing that subdatasheet was
one of these, but didn't actually test it.

Given that setting subdatasheet to [None] can make a huge
performance difference in non-datasheet contexts, my guess is that
the problem is not limited to walking relationships collection of
the back end (aren't the relationships copied to the front end when
you link, or am I dreaming?).

Question for you: if you set the property in the A97 back end,
and create a link to it in A2K2, does the link have the property
set in itself, or is the property looked up from the back end?

From the back end. Unless you also explicitly set it on the link.

Well, I'd certainly want to avoid that trip to the back end, which,
for the first open table, could be a significant hit.

I think to really answer this you'd have to create two links, one
where you just create the link, and one where you set the
property on the link in code, then check the property value 100K
times and see if one or the other is faster. If there's no
difference, I think we could conclude that creating the link
copies the property from the back end table to the front end, in
which case your method of adding the custom properties in the
back end has no disadvantage.

No, it doesn't copy the property, I checked that - it rather looks
in the backend tabledef Properties collection.

If, on other hand, the link without the explicitly set property
is slower than the other one, then it indicates that your method
of depending on setting the property in the back end does not
insulate you from the entire performance hit.

That's possible, although I really doubt that the hit will be
substantial. Testing it is the only way to find out for sure - if
I find the time to do it, I'll post the results.

Well, as I said above, I found that some linked table properties
took a long, long time to retrieve, which caused me to assume that
they were being looked up from the back end. I didn't test with
subdatasheet as I didn't want to add the custom property to the back
end, but I think that you would want to know whether or not my
assumption about it being a significant slowdown would be warranted.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Apr 27 2004, 12:12 am, "David W. Fenton" <dX********@bway.net.invalid>
wrote in news:Xn*********************************@24.168.12 8.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:

Looks like Access gets somewhat creative here with reusing deleted
things. I'm not sure I like it. It's good to know that it may happen
though.
I certainly don't like it! I would surely never depend on it!

Neither would I - I meant that it's good to know about it to avoid it.

It cannot be done through UI. Works fine from code. Since I have
to do it from code, I might as well do it in the back end, since
that will usually make it easier for me in the long run. But we've
covered that already.

Well, your philosophy here is different from mine. I would set the
subdatasheet property in the front ends where I didn't want to use
it, rather than setting it in the back end.

Of course, I'm assuming that a [None] setting would prohibit
subdatasheets in the front end, which is probably an erroneous
assumption.

This actually brings up an interesting question: If you add the property to
both back end and front end, and set it to contradicting values, which one
will be honored?
I'm just bothered by the idea of adding A2K+ properties to an A97
back end.
I don't think there is any inherent difference - a property is a property.
You can add any kind of custom property to pretty much any persistent db
object in any database, and unless the property name has some special
meaning to Access, it's just ignored.

Surely it works in code in all versions, and it would seem to me
that you'd want to do it in code, anyway, instead of manually doing
it via the UI?

Again, depends on the circumstances. If you have a front end with
a lot of links that you need to deliver, sure. If it's a couple of
tables for an ad hoc report, UI is quicker.

I see an inconsistency here, in that you're recommending creating a
subdatasheet property in the A97 back end tables (which cannot be
done through the front end) and saying that it's a disadvantage to
be unable to set the property on a link to an A97 table in an A2K2
front end.

You see the inconsistency here?

Not really. I was talking about two distinct things: one, where to set the
property, and two, how to set it.
If you've got code, you can use it anywhere. One would assume that
code used to set the property in the A97 back end would be smart
enough to check to see if it exists before creating it, so the same
code would be able to work just fine in an A2K+ front end.

Since what you want to do (i.e., setting the property in the A97
back end) can't be done in any UI, I don't see why setting the
property in the front end through code through code should be a
hurdle.
Simply because in many scenarios that I'm involved with this would have to
be done multiple times for the same tabledef - that's all. You have a A97
database with a table in it, and you need to link to that table from
multiple A2002 databases (I can't really call them "front ends" in the
traditional meaning). You never know when or who will link to that table in
the future. Better set the prop once on the tabledef in the backend and not
worry about it ever again.
Given that setting subdatasheet to [None] can make a huge
performance difference in non-datasheet contexts, my guess is that
the problem is not limited to walking relationships collection of
the back end (aren't the relationships copied to the front end when
you link, or am I dreaming?).
I don't think they are copied per se - they are rather looked up from the
back end in much the same way as the SubdatasheetName property <g>. My
feeling is that the major part of the slowdown is scanning the related
tables as opposed to looking in the Relationships collection. Just a gut
feeling.
Well, I'd certainly want to avoid that trip to the back end, which,
for the first open table, could be a significant hit.

Which you are taking anyway, but here we are crossing threads <g>.

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Apr 27 2004, 12:12 am, "David W. Fenton"
<dX********@bway.net.invalid> wrote in
news:Xn*********************************@24.168.12 8.90:

Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn***************************@127.0.0.1:
[]

Surely it works in code in all versions, and it would seem to
me that you'd want to do it in code, anyway, instead of
manually doing it via the UI?

Again, depends on the circumstances. If you have a front end
with a lot of links that you need to deliver, sure. If it's a
couple of tables for an ad hoc report, UI is quicker.

I see an inconsistency here, in that you're recommending creating
a subdatasheet property in the A97 back end tables (which cannot
be done through the front end) and saying that it's a
disadvantage to be unable to set the property on a link to an A97
table in an A2K2 front end.

You see the inconsistency here?

Not really. I was talking about two distinct things: one, where to
set the property, and two, how to set it.

I understood you to be saying that one reason to not set in the
front end was because you couldn't do it with the UI, which is, you
certainly realize, completely irrelevant when there is no UI for it
in the back end.

If you've got code, you can use it anywhere. One would assume
that code used to set the property in the A97 back end would be
smart enough to check to see if it exists before creating it, so
the same code would be able to work just fine in an A2K+ front
end.

Since what you want to do (i.e., setting the property in the A97
back end) can't be done in any UI, I don't see why setting the
property in the front end through code through code should be a
hurdle.

Simply because in many scenarios that I'm involved with this would
have to be done multiple times for the same tabledef - that's all.
You have a A97 database with a table in it, and you need to link
to that table from multiple A2002 databases (I can't really call
them "front ends" in the traditional meaning). You never know when
or who will link to that table in the future. Better set the prop
once on the tabledef in the backend and not worry about it ever
again.

Aha. I was assuming relinking in code, not manually. I don't have
any applications where that is the case.

Of course, I don't know that I'd worry about the whole set of
problems until someone complained about performance.

Given that setting subdatasheet to [None] can make a huge
performance difference in non-datasheet contexts, my guess is
that the problem is not limited to walking relationships
collection of the back end (aren't the relationships copied to
the front end when you link, or am I dreaming?).

I don't think they are copied per se - they are rather looked up
from the back end in much the same way as the SubdatasheetName
property <g>. My feeling is that the major part of the slowdown is
scanning the related tables as opposed to looking in the
Relationships collection. Just a gut feeling.

I don't know why that would be terribly slow. If you've got RI
enforced, it would be an index join, which ought to be extremely
fast. And it should be just as fast as populating a linked subform,
which is pretty darned fast already.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc