Asp Forum - All Tables Accessible - microsoft.public.vb.general.discussion

Privado

11/1/2011 10:54:00 AM

Hi there,
This might be a stupid question, but is it ok to make all tables in an MDB
database accessible through RecordSet, or should I only make accessible the
one I will be using at a specific moment in the program?

In other words,

I open the database, at program start, then make all tables accessible
through RecordSet, after opening the database.

Or should

Open the database, and at the moment I will be using a specific table, make
it accessible through RecordSet?

Any Clues?

Yours Sincere,
N.C

Please Post Reply here.

Thanks

7 Answers

Dee Earley

11/1/2011 11:13:00 AM

On 01/11/2011 10:54, NC wrote:
> Hi there,
> This might be a stupid question, but is it ok to make all tables in an MDB
> database accessible through RecordSet, or should I only make accessible the
> one I will be using at a specific moment in the program?
>
> In other words,
>
> I open the database, at program start, then make all tables accessible
> through RecordSet, after opening the database.
>
> Or should
>
> Open the database, and at the moment I will be using a specific table, make
> it accessible through RecordSet?

How do you mean by making a table accessible?

You can access all tables through the database libraries automatically,
and just need to use standard SQL to query from them.

--
Dee Earley (dee.earley@icode.co.uk)
i-Catcher Development Team
http://www.icode.co.uk...

iCode Systems

(Replies direct to my email address will be ignored.
Please reply to the group.)

Wolfgang Enzinger

11/1/2011 11:41:00 AM

"NC" <oceano.web.pt@netmadeira.com> wrote:

>Hi there,
>This might be a stupid question, but is it ok to make all tables in an MDB
>database accessible through RecordSet, or should I only make accessible the
>one I will be using at a specific moment in the program?
>
>In other words,
>
>I open the database, at program start, then make all tables accessible
>through RecordSet, after opening the database.
>
>Or should
>
>Open the database, and at the moment I will be using a specific table, make
>it accessible through RecordSet?
>
>Any Clues?

Depends. Opening a recordset takes "some" time, so it would be wise to keep it
open, but only if it's frequently used, because any open recordset takes up
"some" memory. So it makes no sense to keep tables open that are only accessed
every now and then.

It also depends on the kind of recordset you are opening. Assuming you are
using DAO, if you open a recordset of dbOpenTable kind and query it by the
..Seek method, it's worth keeping it open if you access it frequently. OTOH, it
makes no sense with a, say, dbOpenForwardOnly type including a varying WHERE
clause, because you have to reopen it anyway as soon as the WHERE criteria
change - so it would be better to close the Recordset as soon as you don't
need it anymore.

Last not least it also depends on whether there is only one user working on
your MDB, or whether there are many users. In the latter case, a more
"defensive" way of keeping recordsets opened is recommended, generally.

Wolfgang

Jeff Johnson [MVP: VB]

11/1/2011 2:38:00 PM

"NC" <oceano.web.pt@netmadeira.com> wrote in message
news:4eafd72e$0$16589$a729d347@news.telepac.pt...

> I open the database, at program start, then make all tables accessible
> through RecordSet, after opening the database.

I have absolutely no idea what you mean by "make all tables accessible
through RecordSet."

mikeb

11/1/2011 4:23:00 PM

"Jeff Johnson" <i.get@enough.spam> wrote in message
news:j8p07f$ab$1@dont-email.me...
> "NC" <oceano.web.pt@netmadeira.com> wrote in message
> news:4eafd72e$0$16589$a729d347@news.telepac.pt...
>
>> I open the database, at program start, then make all tables accessible
>> through RecordSet, after opening the database.
>
> I have absolutely no idea what you mean by "make all tables accessible
> through RecordSet."

Well if the RecordSet Object reference is public, you can set the reference
once (for each distinct recordset) in the first form load or SubMain, then
open / close it thoroughout the app.

Not to indicate that it would be best practice by any stretch.

>

Tony Toews

11/1/2011 7:29:00 PM

On Tue, 01 Nov 2011 12:41:21 +0100, Wolfgang Enzinger
<we_usenet@nurfuerspam.de> wrote:

>Last not least it also depends on whether there is only one user working on
>your MDB, or whether there are many users. In the latter case, a more
>"defensive" way of keeping recordsets opened is recommended, generally.

The only time an MDB can become corrupted is when the records are
being inserted/updated which, of course, in turn updates indexes..
If you just have a recordset variable open I don't see that as being a
problem.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/ac...
Tony's Microsoft Access Blog - http://msmvps.com/blo...
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeup...

ralph

11/1/2011 10:14:00 PM

On Tue, 01 Nov 2011 13:28:47 -0600, Tony Toews
<ttoews@telusplanet.net> wrote:

>On Tue, 01 Nov 2011 12:41:21 +0100, Wolfgang Enzinger
><we_usenet@nurfuerspam.de> wrote:
>
>>Last not least it also depends on whether there is only one user working on
>>your MDB, or whether there are many users. In the latter case, a more
>>"defensive" way of keeping recordsets opened is recommended, generally.
>
>The only time an MDB can become corrupted is when the records are
>being inserted/updated which, of course, in turn updates indexes..
>If you just have a recordset variable open I don't see that as being a
>problem.
>

I'm not so sure. I once had a MDB file that got corrupted if I so much
as peeked at it. But perhaps I was a bit paranoid at the time. <bg>

My guess Wolfgang was referring to possible problems with concurrency
and data integrity, which along with the wire, frequency and number of
users, and cursors, can be acerbated with long Recordset lifecycles -
particularly one that included multiple or all the tables.

-ralph

Clive Lumb

11/3/2011 8:08:00 AM

"NC" <oceano.web.pt@netmadeira.com> a écrit dans le message de groupe de
discussion : 4eafd72e$0$16589$a729d347@news.telepac.pt...
> Hi there,
> This might be a stupid question, but is it ok to make all tables in an MDB
> database accessible through RecordSet, or should I only make accessible
> the
> one I will be using at a specific moment in the program?
>
> In other words,
>
> I open the database, at program start, then make all tables accessible
> through RecordSet, after opening the database.
>
> Or should
>
> Open the database, and at the moment I will be using a specific table,
> make
> it accessible through RecordSet?
>
> Any Clues?
>
> Yours Sincere,
> N.C

I suspect that the OP is wondering if he can simplify his life by simply
opening a recordest for each of the tables he needs when his app starts. He
would then close them at shutdown, no need to worry about
creating/destroying recordsets in his functions/procedures.

I would not recommend this, notably with an Access .mdb which is far less
robust than SQL Server - if your app fails and exits without closing all the
recordsets it can leave the mdb in a "delicate state" and if it is on a
networked drive it can have phantom locks on it.

It is best (IMO) to be very careful and open/destroy recordsets as and when
they are needed. Include error trapping to make sure that however each
function might fail the recordset is disposed of properly.

Believe me I know, I've been there.

microsoft.public.vb.general.discussion

All Tables Accessible