Dimitri Furman <dfurman@cloud99.net> wrote in
news:Xns94D5AE4B041BFdfurmancloud99@127.0.0.1:
[color=blue]
> On Apr 24 2004, 01:09 pm, "David W. Fenton"
> <dXXXfenton@bway.net.invalid> wrote in
> news:Xns94D586287CE14dfentonbwaynetinvali@24.168.1 28.90:
>[color=green]
>> Dimitri Furman <dfurman@cloud99.net> wrote in
>> news:Xns94D583053A51dfurmancloud99@127.0.0.1:[color=darkred]
>>>
>>> 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"[/color]
>>
>> 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.[/color]
>
> 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.[/color]
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.
[color=blue][color=green]
>> 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).[/color]
>
> 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.[/color]
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.
[color=blue][color=green][color=darkred]
>>> 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][/color]
>>
>> 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.[/color]
>
> 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. . . .[/color]
Uh, I don't create links very often, just when I'm creating the
front end.
[color=blue]
> . . . You may need to link to the back end from
> multiple places. Depending on situation, this may or may not be a
> consideration.[/color]
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.
[color=blue][color=green]
>> But you don't have to do *anything* to the A97 database -- you
>> need only change the property of the linked table.[/color]
>
> Either way will work. I would prefer to use base tables, as they
> are less volative than links, but YMMV.[/color]
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.
[color=blue][color=green]
>> The fact that the property is not stored in the link does not
>> mean it can *not* be stored in the link.[/color]
>
> 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.
>[color=green][color=darkred]
>>> 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]".[/color]
>>
>> This is not what happens in A2K. The change to the subdatasheet
>> property in the A2K database is permanent.[/color]
>
> As far as I can tell, in A2002 you can only create the property on
> the link programatically.[/color]
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].
[color=blue][color=green]
>> 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.[/color]
>
> I would guess that it rather inherits it from the back end. . ..[/color]
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.
[color=blue]
> . . . 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, . . .[/color]
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.
[color=blue]
> . . . 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>.[/color]
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.
[color=blue][color=green][color=darkred]
>>>> 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]").[/color]
>>
>> 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.[/color]
>
> Assuming you can - and in A2002 you can not, so you have to muck
> with properties from code anyway.[/color]
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?
[color=blue][color=green][color=darkred]
>>>>>> , 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.[/color]
>>
>> Have you tried it with A2K, which is the subject of this thread?[/color]
>
> 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.[/color]
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?
[color=blue][color=green][color=darkred]
>>>> 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.[/color]
>>
>> 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.[/color]
>
> The lack of the property causes the default *behavior* to be
> invoked. There is no property in the back end to look up. . .[/color]
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.
[color=blue]
> . . . 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.[/color]
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 
