to compact or not to

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

As it is, if I start a new record but do not complete nor
save it, the next time I enter the table, ID gets
autonumber + 1.

I am thinking that each time the user closes the MAINFORM,
VBCODE should compact the maintable.

1) is this wise?
2) how do you code it in VB?
3)what about other children tables with referential
integrity?
 
If you care about the value of the autonumber field, you probably should be
using an autonumber field.

Autonumbers exist for one purpose: to provide a (practically guaranteed)
unique value that can be used as a primary key. That purpose is met whether
the numbers are sequential or not. In fact, it's rare that the value of the
autonumber field is even shown to the user.
 
doug,

please reread my post. I am using an autonumber.
please answer my question or repost it so someone who
knows the answer can
 
Cool it Mark, aside from a missing word (should NOT be),
Doug did answer your question. What's a little typo among
friends anyway ;-)

A couple of problems in your question. First, you can not
Compact a database while it is running (see
http://www.mvps.org/access/general/gen0013.htm), at least
not and continue executing code in the procedure. The
database must close itself before the Compact can run (the
Compact menu item does this and then reopens the database
after the Compact completes), but your open forms, code,
etc. are all terminated so you're back to the startup form.
You can do this in code using:
http://www.mvps.org/access/general/gen0041.htm

Regardless, of the Compact procedure that may or may not be
available, you have a serious issue with exposing an
Autonumber field to users. Since an autonumber primary key
does not guarantee anything beyond a unique identifier, you
can not reliably use it to provide an unbroken number
sequence. Autonumber fields may go negative, switch to
random numbers, develop both small and large gaps in the
sequence and if you should ever want to use Replication,
autonumber primary keys become GUIDs, which are not even
numbers.

If you need a reliable sequence of numbers such as Invoice,
PO or Serial Numbers, then you have to provide the code to
do that (taking into account deleted items, multiple users
requesting a new number simultaneously, etc). Depending on
your requirements, you might be able to use a simple
procedure or you may have to implement a complex algorithm.
Try searching the web to see if any examples are available
for whatever situation you have.
 
As it is, if I start a new record but do not complete nor
save it, the next time I enter the table, ID gets
autonumber + 1.

I am thinking that each time the user closes the MAINFORM,
VBCODE should compact the maintable.
Click to expand...

Just to add to Douglas' and Marshall's suggestions:
1) is this wise?
Click to expand...

No. It would be a very cumbersome and inefficient way to do something
which probably should not be done at all (as noted elsethread).
2) how do you code it in VB?
Click to expand...

See Marshall's link (if you insist).
3)what about other children tables with referential
integrity?
Click to expand...

Since no record exists in either the parent or the related tables for
the deleted autonumber value, the problem does not arise.

If it's a multiuser database you simply will NOT be able to compact in
any case if anyone else has the database open.

John W. Vinson[MVP]
(no longer chatting for now)
 
Thanks, I didn't gather Doug answered...........

OKay, I won't compact...........how often should one
compact and how should one do it?

(curious for both single user and multi user)



-----Original Message-----
Cool it Mark, aside from a missing word (should NOT be),
Doug did answer your question. What's a little typo among
friends anyway ;-)

A couple of problems in your question. First, you can not
Compact a database while it is running (see
http://www.mvps.org/access/general/gen0013.htm), at least
not and continue executing code in the procedure. The
database must close itself before the Compact can run (the
Compact menu item does this and then reopens the database
after the Compact completes), but your open forms, code,
etc. are all terminated so you're back to the startup form.
You can do this in code using:
http://www.mvps.org/access/general/gen0041.htm

Regardless, of the Compact procedure that may or may not be
available, you have a serious issue with exposing an
Autonumber field to users. Since an autonumber primary key
does not guarantee anything beyond a unique identifier, you
can not reliably use it to provide an unbroken number
sequence. Autonumber fields may go negative, switch to
random numbers, develop both small and large gaps in the
sequence and if you should ever want to use Replication,
autonumber primary keys become GUIDs, which are not even
numbers.

If you need a reliable sequence of numbers such as Invoice,
PO or Serial Numbers, then you have to provide the code to
do that (taking into account deleted items, multiple users
requesting a new number simultaneously, etc). Depending on
your requirements, you might be able to use a simple
procedure or you may have to implement a complex algorithm.
Try searching the web to see if any examples are available
for whatever situation you have.
--
Marsh
MVP [MS Access]



mark said:
doug,

please reread my post. I am using an autonumber.
please answer my question or repost it so someone who
knows the answer can

you
probably should be
Click to expand...

.
Click to expand...
 
Compacting is a data administration (certainly not an
autonumber management) issue. Some people use the Compact On
Close option to compact when the last user closes your
application. I prefer to use a more refined(?) approach and
manually compact when the data mdb file has grown
significantly (25% - 50%) or if a major data update is
executed (deleting or inserting a lot of records).

A well designed and implemented front end mdb file should
never need to be compacted.

Depending on your application, you might want to use the
idea in the article I refererenced earlier.
--
Marsh
MVP [MS Access]



Mark said:
Thanks, I didn't gather Doug answered...........

OKay, I won't compact...........how often should one
compact and how should one do it?

(curious for both single user and multi user)



-----Original Message-----
Cool it Mark, aside from a missing word (should NOT be),
Doug did answer your question. What's a little typo among
friends anyway ;-)

A couple of problems in your question. First, you can not
Compact a database while it is running (see
http://www.mvps.org/access/general/gen0013.htm), at least
not and continue executing code in the procedure. The
database must close itself before the Compact can run (the
Compact menu item does this and then reopens the database
after the Compact completes), but your open forms, code,
etc. are all terminated so you're back to the startup form.
You can do this in code using:
http://www.mvps.org/access/general/gen0041.htm

Regardless, of the Compact procedure that may or may not be
available, you have a serious issue with exposing an
Autonumber field to users. Since an autonumber primary key
does not guarantee anything beyond a unique identifier, you
can not reliably use it to provide an unbroken number
sequence. Autonumber fields may go negative, switch to
random numbers, develop both small and large gaps in the
sequence and if you should ever want to use Replication,
autonumber primary keys become GUIDs, which are not even
numbers.

If you need a reliable sequence of numbers such as Invoice,
PO or Serial Numbers, then you have to provide the code to
do that (taking into account deleted items, multiple users
requesting a new number simultaneously, etc). Depending on
your requirements, you might be able to use a simple
procedure or you may have to implement a complex algorithm.
Try searching the web to see if any examples are available
for whatever situation you have.
--
Marsh
MVP [MS Access]



mark said:
doug,

please reread my post. I am using an autonumber.
please answer my question or repost it so someone who
knows the answer can


-----Original Message-----
If you care about the value of the autonumber field, you
probably should be
using an autonumber field.

Autonumbers exist for one purpose: to provide a
(practically guaranteed)
unique value that can be used as a primary key. That
purpose is met whether
the numbers are sequential or not. In fact, it's rare
that the value of the
autonumber field is even shown to the user.


As it is, if I start a new record but do not complete
nor
save it, the next time I enter the table, ID gets
autonumber + 1.

I am thinking that each time the user closes the
MAINFORM,
VBCODE should compact the maintable.

1) is this wise?
2) how do you code it in VB?
3)what about other children tables with referential
integrity?
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top