Loading Tables

[Vel]

Hello,

I have a couple of questions:

1. Is there a way to "hide" a table after it's been
opened?
2. Is there anything inherently wrong with loading a
particularly large table when a database is first opened
and then hiding it so queries based off of that table run
faster? I realize this would cause a slower start, but
it would be more than worth it in the long run.

Vel.

 

[Vel.]

Some more information, and questions regarding this topic:

This database application has a server backend. There
may be multiple users (probably no more than 5-8 at any
one time) accessing the database. Using the following
code, I add time to startup, but reduce time when loading
reports, combo boxes, etc:

Private Sub frmMain_Open()
'...

DoCmd.OpenTable "tblClient"
DoCmd.GotoRecord, , acLast 'to be sure entire table is
loaded
DoCmd.RunCommand acCmdWindowHide

'repeat for each table in database
'...

End Sub

Am I going to run into some as of yet unforseen problems
with this? I realize that when my activity table starts
to grow to thousands, or even tens of thousands of
records long that the load time will become quite
substantial. This is an acceptable sacrifice, though, as
this database will be used primarily for clerical staff,
opened in the morning, and not shut down until the staff
person goes home at night.

Thank You for any help,

Vel.

 

[John Vinson]

Hello,

I have a couple of questions:

1. Is there a way to "hide" a table after it's been
opened?

Tables need not be "opened" to run queries, and users should never see
a table datasheet in any case.

2. Is there anything inherently wrong with loading a
particularly large table when a database is first opened
and then hiding it so queries based off of that table run
faster? I realize this would cause a slower start, but
it would be more than worth it in the long run.

It's often useful to open *a* table - any table - in a linked backend
database, when opening a database; this keeps a live connection open
and speeds later queries. But I'm not aware that opening a table will
affect later queries against that table in any predictable way.

 

[Vel]

John,

Just as an example. I have a combo box which is filtered
based on some criteria which is entered by the user. It
is necessary to use either a combo box, or a subform, or
something, either way it takes a long time to load
because this particular combo is based on a query based
on the largest table in my database. No matter
what "trick" I used to populate that combo box it still
took from 5-30 seconds to load, and this would just get
worse as the table grows in size. Conversely, when I
loaded the table at startup, i.e. I used a bit of code to
open the table, move to the last record (to ensure the
entire table was loaded), and hide the window in which
the table was open, the same combo box took no noticeable
time to populate on the form whenever I opened it. I
haven't tried opening that table on the backend to see if
it yields the same result, if so, that would certainly be
preferable. Do you see me running into other problems
using this method to speed things up?

Vel.

 

[Vel]

I tried a few different things, and here's what I've
discovered:

1. Having table(s) open (i.e. docmd.OpenTable) in the
frontend greatly improves the speed at which queries,
Dlookup functions, and combo boxes are executed on the
frontend machine.
2. The more machines that currently have the table
already open, the longer it takes to load initially.
3. By hiding the windows and limiting the menu bar, all
tables can be loaded at startup without the user ever
knowing about it.

What I would like to know:
1. Am I likely to run into conflicts by doing this?
i.e. generate unexpected errors, etc.
2. Besides being a rather unorthodox approach, is there
anything inherently wrong with this setup that I'm not
seeing?
3. If I seem to be 'asking for trouble' by doing this,
could you please explain why it seems that way?

Thank you all for your help over time. This is my
biggest project so far, and I just want to be sure I'm
not dooming myself.

Vel.

 

[John Vinson]

I tried a few different things, and here's what I've
discovered:

1. Having table(s) open (i.e. docmd.OpenTable) in the
frontend greatly improves the speed at which queries,
Dlookup functions, and combo boxes are executed on the
frontend machine.

Interesting. I haven't experimented with this, obviously, but I will!

Would opening a Recordset do the same thing? Doing so would still pull
the table into your frontend, but without the annoyance of having a
table datasheet pop into view.

What I would like to know:
1. Am I likely to run into conflicts by doing this?
i.e. generate unexpected errors, etc.

It shouldn't (unless you keep the table datasheet open, in which case
you might get Access confused about which window is editing the data).

2. Besides being a rather unorthodox approach, is there
anything inherently wrong with this setup that I'm not
seeing?

I'd try the Recordset rather than the table, but just because it seems
a simpler way of doing the same thing.

3. If I seem to be 'asking for trouble' by doing this,
could you please explain why it seems that way?

If it works - it works!

 

[Vel]

Would opening a Recordset do the same thing? Doing so
would still pull the table into your frontend, but
without the annoyance of having a table datasheet pop
into view.

I'm not familiar with this type of coding, and have, in
fact don't know how to open a recordset without opening
the table. On another note, see the next comment which
might make this point moot.

It shouldn't (unless you keep the table datasheet open,
in which case you might get Access confused about which
window is editing the data).

That's the thing. Technically, the datasheet is open,
albeit hidden from view. Without the datasheet open
(i.e. if I open, then close the table) the speed increase
doesn't occur. There is no way to access the datasheet
from the user's end (unless they bypass my startup
settings) so they wouldn't even know it existed. I guess
I'll have to fiddle with it a bit to see if any unforseen
errors come up since it sounds like I'm doing something
quite out of the ordinary.

I'd try the Recordset rather than the table, but just
because it seems a simpler way of doing the same thing.

Once again, I'm unfamiliar with exactly HOW to do this.
If it would work in the same way as having the table
open, I'm all for it if it's simpler and avoids
the "popup" problem.


....maybe I should come in for a chat some tuesday or
thursday...

 

[John Vinson]

I'm not familiar with this type of coding, and have, in
fact don't know how to open a recordset without opening
the table. On another note, see the next comment which
might make this point moot.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset, dbReadOnly)
rs.MoveLast ' to populate the entire table

should do the trick... opening the recordset readonly should prevent
it from interfering with other uses.

 

[Vel]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset, dbReadOnly)
rs.MoveLast ' to populate the entire table

When I try to compile that bit of code I get the error:

"User defined type not defined"

I am using Access 2K on windows XP if that makes any
difference.

Thanks for your help,

Vel

kalNOSPAMis@korNOSPAMinth dot com

 

[John Vinson]

When I try to compile that bit of code I get the error:

"User defined type not defined"

ah! Yes, AccessXP defaults to the ADO data model instead of DAO.
Sorry!

Either use the online help for Recordset to open an ADO recordset; or
open the VBA editor and select Tools... References, scroll down to
Microsoft DAO x.xx Object Library, and check it.

 

[Vel]

-----Original Message-----

ah! Yes, AccessXP defaults to the ADO data model instead of DAO.
Sorry!

Either use the online help for Recordset to open an ADO recordset; or
open the VBA editor and select Tools... References, scroll down to
Microsoft DAO x.xx Object Library, and check it.


.

It appears that the increased speed is a direct result of
having the table open in datasheet mode. I tried using
your code to open the recordset, but it didn't have the
same effect...