Mr. Kallal & Mr. Fenton: What did I do wrong?

[Vladimír Cvajniga]

Thx, Tony. I'll bear it in mind when I'll test it again. Not going to test
anything tonight... at 3:45 am...
Vlado

P.S. Happy Easter to everybody!

 

[John W. Vinson]

Yes, the Switchboard Manager wizard does create a local table. However, this
table is moved to the back-end file as soon as you use the database splitter
wizard.

In my experience, the Switchboard then immediately stops working (because the
switchboard wizard code uses Seek and expects the table to be local). Only if
you re-import the Switchboard table into the frontend can you use the
switchboard!

One reason I tend to avoid the wizard generated switchboard.

John W. Vinson [MVP]

 

[Larry Linson]

I have downloaded query_timing_results.zip. TYVM
for letting me see the results. All I can say is: I'm really
surprised! I think I will try persistent connection once
again on my home P2P network. And I'll try David
Fenton's technique, too, ie. use db variable instead
of a recordset. Has anyone tried persistent connection
on a P2P?

The difference in the limit of number of connections between machines on a
P2P versus a server may make any findings in the former not useful in
predicting performance in the latter.

Yes, you can use linked tables between machines in a P2P. But, most of the
performance information and suggestions you see here and in references
applies to a server on a LAN.

Larry Linson
Microsoft Access MVP

 

[Tom Wickerath]

Hi John,

I just finished testing three new switchboards, created using Access 2000,
2002 and 2003. In each case, the Switchboard Items table was a linked table.
It works fine in all three versions.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Albert D. Kallal]

I think you making this more complex then you need.

As the others said if the ldb locking file remains, then you are ok...

The example posted in that thread was:

Public Function fncIsOpenMDB(ByVal strPath As String) As Boolean
Dim db As DAO.Database

On Error Resume Next
fncIsOpenMDB = False
Set db = DBEngine.OpenDatabase(strPath, True)
fncIsOpenMDB = (Err <> 0)
db.Close
Set db = Nothing


End Function


The above is NOT good, because the "db" variable goes out of scope right
away. So, the only real issue to be careful here is to make sure the
database variable (or record set variable) you use is a global define
variable in a standard code module.

You don't repeat call over and over the code to open the back end...

You simply ensure that the connection persists.

That is it.....

There is nothing more, nothing less on this subject. As to which setups this
improves performance the most? I don't believe that information is known.

On some systems, creating and deleting the file seems to take a LONG time
(virus software, large path names....etc can all effect this).

The ONLY thing you can be sure of is that you reduce the time it takes to
create the ldb file...you want to eliminate the long delay.

The only thing you need to be sure of is that the variable used to open the
recordset (or database var) is global..and keeps the connection alive.....

As to if this works better on a wan, or lan, or peer-to-peer, it really not
an issue. It usually helps in all cases. the only thing you can do is try
it!!!

If you find a case that were using the persistent connection slows things
down, then sure..share this information with us..it always appreciated when
we can learn something new....

 

[Albert D. Kallal]

Has anyone tested the difference between opening a recordset on a
local linked table and simply initializing a db variable pointing to
the back-end database? Both create the MDB, but it seems to me that
the latter is more efficient.

I certainly keeping my fingers crossed there's not a difference. However,
someone likely should check!!!

I always opened a table, but your suggestion to use a db variable is a
*much* nicer solution (as it don't care about a particular table name). So,
I actually changed my approach based on your suggestion.......

I will test the two when I have a chance. (I would bet 96% that there no
difference, but you never know...I seen stranger things!).

 

[Arvin Meyer [MVP]]

To see if keeping a persistent recordset open might have the desired affect,
you can simply open a small linked table and keep it open while you run the
problematic part of your application. It should speed up the application
considerably. If it doesn't, that's not your problem.

 

[David W. Fenton]

In my experience, the Switchboard then immediately stops working
(because the switchboard wizard code uses Seek and expects the
table to be local). Only if you re-import the Switchboard table
into the frontend can you use the switchboard!

From A2K on, the Switchboard wizard writes ADO code, so it can't
possibly be using SEEK.

One reason I tend to avoid the wizard generated switchboard.

I do believe it breaks for some reason when you move it to the back
end.

But the switchboard table doesn't *belong* in the back end. Take
this scenario:

1. you create a new report in your development copy.

2. you add it to the switchboard.

If the switchboard table is in the back end, users will see the
choice to print the report, but won't be able to do so, as they
don't have the updated copy of the front end.

For that reason, the switchboard belongs in the front end because it
is connected to the version of the front end you are using, not to
the data. It is a user interface object and the data store for it
belongs with the UI.

 

[David W. Fenton]

I certainly keeping my fingers crossed there's not a difference.
However, someone likely should check!!!

I always opened a table, but your suggestion to use a db variable
is a *much* nicer solution (as it don't care about a particular
table name). So, I actually changed my approach based on your
suggestion.......

I will test the two when I have a chance. (I would bet 96% that
there no difference, but you never know...I seen stranger
things!).

If the difference truly is the creation of the LDB file, then there
oughtn't be a difference. If there's something else involved (e.g.,
some data manipulation internal to the LDB file), then there might
be a difference.

 

[David W. Fenton]

In the front end. Which is where it belongs (as a switchboard
form is likely to be specific to a particular front-end version).

I know that there are people who write different versions of a
Front-end application targeted for different groups of users (for
example, managers vs. regular grunts). I do not follow this
practice. Instead, I create one FE for all users, and use the
appropriate VBA code to make certain functionality visible to
selected users. Therefore, if I were to use the Switchboard
Manager [which I absolutely abhor--my switchboard forms are
unbound], I would keep the Switchboard Items table in the BE
database. In that case, I might need to add a field or two to the
Switchboard Items table for controlling when a particular item was
visible to a given user--not really sure, since I haven't
travelled down that road.

What about a new version? How do you coordinate adding a new menu
choice to the switchboard with the creation of new features in the
front end? For instance, you create a report in your development
front end and add a menu choice to the switchboard. If the
switchboard table is in the back end, then all your users will now
see the new menu choice but won't have the report in the front ends
yet.

The switchboard is part of the user interface. Its data stroe
belongs in the front end because it applies to user interface
functionality in the front end.

 

[David W. Fenton]

The difference in the limit of number of connections between
machines on a P2P versus a server may make any findings in the
former not useful in predicting performance in the latter.

Yes, you can use linked tables between machines in a P2P. But,
most of the performance information and suggestions you see here
and in references applies to a server on a LAN.

Exactly what differences do you adduce between a Windows workstation
acting as a server and a Windows server? What parameters are
different between the two that would cause the issues we are
attempting to resolve with the persistent connection?

Remember, the server and workstation versions of any version of
Windows are binary identical and the only difference is
configuration settings.

 

[Tom Wickerath]

If the switchboard table is in the back end, users will see the

choice to print the report, but won't be able to do so, as they
don't have the updated copy of the front end.

My users *would* have immediate access to the new report, because I use Tony
Toews AutoFE updater. As soon as they double-clicked on their desktop icon to
start the application, a new copy would be downloaded. So, one can have the
Switchboard Items table in the BE database, and it does not have to be a
problem. Whether or not it *belongs* in the FE is certainly debateable, but
that's an academic debate for me, which I'm really not interested in
pursuing, since I only use unbound switchboard forms.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Answered here
http://www.microsoft.com/office/com...cess&mid=5a0d5039-5664-4fcc-a288-03e4c228be96

For instance, you create a report in your development
front end and add a menu choice to the switchboard.

If I used a bound switchboard (which I don't), I would modify the
Switchboard Items table first in a dev. copy of the BE. Once I was satisfied
all was well, then I would roll out the new FE, with an updated copy of the
Switchboard Items table in the production BE. Or, perhaps I would do as you
suggest, and just leave the Switchboard Items table in the FE. I use unbound
switchboard forms exclusively, so I've never really pondered this question in
depth. But, it certainly is possible to keep the Switchboard Items table in
the BE, and if you do so, it will serve nicely to maintain the persistent
connection without any more fuss. Sure, there may be slight obstacles that
one needs to consider (such as not updating the production copy of the
Switchboard Items table without forcing an update of the FE), but those
issues are easy to work around. Even if one maintains the Switchboard Items
table in the FE, there certainly is no guarantee of continued functionality
working if the developer makes changes to the BE database. In my mind, it's
"six-one-half-a-dozen-the-other".

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi John,

I have just finished testing one more variation: this time using Access 97.
The bound switchboard form works fine with a linked Switchboard Items table
in this version too. In addition, I searched the code module behind the
Switchboard form for the word "Seek" in all four tests, and I have found no
occurances.

Which version did you experience this problem in? Was it Access 95 or earlier?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[David W. Fenton]

My users *would* have immediate access to the new report, because
I use Tony Toews AutoFE updater. As soon as they double-clicked on
their desktop icon to start the application, a new copy would be
downloaded.

But what if someone has the front end open, you add the menu item to
the Switchboard, and they go to the reports menu on it. They'll see
the report before they've got it in their front end.

So, one can have the
Switchboard Items table in the BE database, and it does not have
to be a problem. Whether or not it *belongs* in the FE is
certainly debateable, but that's an academic debate for me, which
I'm really not interested in pursuing, since I only use unbound
switchboard forms.

I think it's obvious that the UI to give access to the application's
functions is by definition a UI object and all its components should
be kept in synch with the front end. What do you gain by putting the
table in the back end? Nothing -- if it's in the front end, they're
getting the data when they need it. If you're using Tony's updater,
then they would be getting it at exactly the same time as they'd
have access to report otherwise, and you don't have any interval
whatsoever when a user could be offered the opportunity to run
something that's not actually there yet.

 

[David W. Fenton]

If I used a bound switchboard (which I don't), I would modify the
Switchboard Items table first in a dev. copy of the BE. Once I was
satisfied all was well, then I would roll out the new FE, with an
updated copy of the Switchboard Items table in the production BE.
Or, perhaps I would do as you suggest, and just leave the
Switchboard Items table in the FE.

What can possibly be gained in terms of Switchboard functionality by
putting the Switchboard table in the back end?

I use unbound
switchboard forms exclusively, so I've never really pondered this
question in depth.

Well, I use bound ones and I *have* pondered the question.

But, it certainly is possible to keep the Switchboard Items table
in the BE, and if you do so, it will serve nicely to maintain the
persistent connection without any more fuss.

But it also introduces problems because of a period of latency
between adding the items to the table and rolling out the front end.
That latency can never be zero when you store the Switchboard table
in the back end. It will *always* be zero when the table is in the
front end.

Other tables are always available for the persistent link.

(I don't use persistent links, BTW. I do use global db variables,
but those point to the front end, so they don't server the purpose
of the persistent connection. I've never seen a performance
improvement when I implemented a persistent connection on an app
that had performance issues)

Sure, there may be slight obstacles that
one needs to consider (such as not updating the production copy of
the Switchboard Items table without forcing an update of the FE),
but those issues are easy to work around.

And if you keep the SWitchboard in the front end, then there is
nothing to work around.

Even if one maintains the Switchboard Items
table in the FE, there certainly is no guarantee of continued
functionality working if the developer makes changes to the BE
database. In my mind, it's "six-one-half-a-dozen-the-other".

Huh? What changes to the BE? The Switchboard table is not changed
structural -- only the data changes. If your app fails with a change
in the *data* then you have far worse problems than choosing where
to put the Switchboard table!

 

[Vladimír Cvajniga]

In English, is Switchboard the same as the Dashboard (in terms of MS
Access)?

Vlado

 

[Tom Wickerath]

But what if someone has the front end open, you add the menu item to

the Switchboard, and they go to the reports menu on it. They'll see
the report before they've got it in their front end.

You'd trap for the error and present a customized message, such as "This
report will be available shortly."

I think it's obvious that the UI to give access to the application's
functions is by definition a UI object and all its components should
be kept in synch with the front end.

I'm inclined to agree with you here, given that the VBA code behind my
unbound switchboard forms is in the front-end too (which is the only place it
really could be).

What do you gain by putting the table in the back end? Nothing -- if it's ...

I disagree. You do gain an automatic persistent connection, without having
to do anything else, so there *is* something to be gained. Sure, you may have
to work around some issues, like trapping for an error in the event a report
is not yet available, but that's certainly not insurmountable.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

What can possibly be gained in terms of Switchboard functionality by

putting the Switchboard table in the back end?

What I've already mentioned a few times: a persistent connection without the
need to do anything else.

But it also introduces problems because of a period of latency
between adding the items to the table and rolling out the front end.

In my case, probalby not. I tend to be a late night owl, so in my case, by
the time I'm making that new FE available (and updating a linked SI table [if
I used one]) all other employees have long since gone home. In any case, just
trap for any errors in the FE, and present a customized error message, such
as "This report will be available shortly". No big deal really.

Other tables are always available for the persistent link.

True....I won't argue that.

Huh? What changes to the BE?

I'm talking mostly about the very early stages of development, where a
customer want to be able to see the progress, but the requirements are still
being decided. So, for example, you might be told all of a sudden "we thought
we'd only need to record one comment per record, but we really need many".
So, you yank your comments field out of a table and put it into a child
table, related 1:M. So, the situation I had in mind is where the requirements
are still quite fluid.

The Switchboard table is not changed structural -- only the data changes.

I understand that.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Vladimír,

Yes, I think so.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________