INSERT INTO not dependable

[jking482]

I am using Access 2000. I am using INSERT INTO syntax that works most
of the time, but sometimes it doesn't insert the record. The data base
has been heavily used for five or more years now. There have been many
deletions of records and additions in that time. The AutoNumber field
is past 6 digits. It is long integer data type. My theory is that
Access is trying to assign invalid numbers into the AutoNumber field
when it creates new records, and therefore it just quietly rejects the
record. If I try several times, it finally takes. Could I be right
about that? If so, how do I fix it?

John King
Boise, Idaho

[Tom van Stiphout]

On 25 Nov 2006 09:38:16 -0800, jk******@cableone.net wrote:

You're probably wrong about the Autonumbers. If you insert 1 record
per second, 24/7, it has enough numbers for 140 years.

You don't mention any error message. That's probably because you've
turned them off using:
DoCmd SetWarnings False
Make sure you have warnings on, so Access will tell you WHY it
couldn't insert the record.
An alternative is:
CurrentDb.Execute sqlstatement, dbFailOnError
You'll likely find a violation of referential integrity, a unique
index, or a required field.

-Tom.

>I am using Access 2000. I am using INSERT INTO syntax that works most
of the time, but sometimes it doesn't insert the record. The data base
has been heavily used for five or more years now. There have been many
deletions of records and additions in that time. The AutoNumber field
is past 6 digits. It is long integer data type. My theory is that
Access is trying to assign invalid numbers into the AutoNumber field
when it creates new records, and therefore it just quietly rejects the
record. If I try several times, it finally takes. Could I be right
about that? If so, how do I fix it?

John King
Boise, Idaho

[David F Cox]

Is it possible that the record is being inserted, but not where you think it
should be?
I have read that Access witll reuse old deleted autonumber generated keys.
have you checked the record count before and after?

<jk******@cableone.netwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...

>I am using Access 2000. I am using INSERT INTO syntax that works most
of the time, but sometimes it doesn't insert the record. The data base
has been heavily used for five or more years now. There have been many
deletions of records and additions in that time. The AutoNumber field
is past 6 digits. It is long integer data type. My theory is that
Access is trying to assign invalid numbers into the AutoNumber field
when it creates new records, and therefore it just quietly rejects the
record. If I try several times, it finally takes. Could I be right
about that? If so, how do I fix it?

John King
Boise, Idaho

[Tim Marshall]

Tom van Stiphout wrote:

You don't mention any error message. That's probably because you've
turned them off using:
DoCmd SetWarnings False
Make sure you have warnings on, so Access will tell you WHY it
couldn't insert the record.
An alternative is:
CurrentDb.Execute sqlstatement, dbFailOnError

Yup. I see so many people advising newbies to use DoCmd SetWarnings
False and in my opinion, it's the worst possible advice.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Nick 'The Database Guy']

I have to concur with both Tom and Tim, do not set the warmings to
false for general running of code, but I will say that it is
understandable that while running an insert query via code you may want
to do this so that the users do not see and unfriendly 'Access is about
to insert 123 rows of data' message, it also gives them the option to
cancel, which may undesirable, however after you have run your query
make sure you turn them back on straight away and be aware of what you
have done. You mention that many records have been deleted, it could
be that inserting the records would create orphaned records, or break
the rules that have been created for that table. If you turn the
warning back on, all be it temporarily, access will tell you why it is
not inserting certain row, and then will allow you to carry on with
inserting the records that you can or cancel the whole insert. I would
recommend that you cancel the whole insert until you have found out why
certain records did not make it.

Good luck

Nick

Tim Marshall wrote:

Tom van Stiphout wrote:

You don't mention any error message. That's probably because you've
turned them off using:
DoCmd SetWarnings False
Make sure you have warnings on, so Access will tell you WHY it
couldn't insert the record.
An alternative is:
CurrentDb.Execute sqlstatement, dbFailOnError

Yup. I see so many people advising newbies to use DoCmd SetWarnings
False and in my opinion, it's the worst possible advice.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tom Becker]

On Sun, 26 Nov 2006 00:16:34 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertheriumwrote:

>Tom van Stiphout wrote:

>You don't mention any error message. That's probably because you've
turned them off using:
DoCmd SetWarnings False
Make sure you have warnings on, so Access will tell you WHY it
couldn't insert the record.
An alternative is:
CurrentDb.Execute sqlstatement, dbFailOnError

Yup. I see so many people advising newbies to use DoCmd SetWarnings
False and in my opinion, it's the worst possible advice.

You must mean without SetWarnings True afterward. You may not
want... "Are you sure you want to delete one record" etc.

[Tim Marshall]

Tom Becker wrote:

>>Yup. I see so many people advising newbies to use DoCmd SetWarnings
False and in my opinion, it's the worst possible advice.

You must mean without SetWarnings True afterward. You may not
want... "Are you sure you want to delete one record" etc.

Nope even with that. It's a careless practice. As is happening with
the OP, you don't get any warning that errors are occuring. And you can
be sure that no matter how certain you are of your code, that you will
eventually have errors.

In fact, in a properly structured database with primary key constraints,
you WANT to throw and trap error 3022 to let the user know they are
trying to store a duplicate primary key.

The DAO execute method:

db.execute strSql, dbFailOnError

Where db is a DAO database object such as currentdb, is a far, far
better way of doing it and superior in every way possible to the
setWarnings bandaid.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Nick 'The Database Guy' wrote:

I have to concur with both Tom and Tim, do not set the warmings to
false for general running of code, but I will say that it is
understandable that while running an insert query via code you may want
to do this so that the users do not see and unfriendly 'Access is about
to insert 123 rows of data' message,

The DAO execute method:

db.execute strSql, dbFailOnError

Where db is a DAO database object such as currentdb, does not produce
such messages.

USing setwarnings is, bluntly, careless programming. In my opinion, of
course.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Nick 'The Database Guy']

Hi Tim,

I would agree with you that DoCmd.SetWarnings is certainly not the most
professional approach that one could adopt.

It is been many years since I used this statement myself however I was
just answering a question, not embarking on a voyage of VBA discovery.

Good luck

Nick

Tim Marshall wrote:

Nick 'The Database Guy' wrote:

I have to concur with both Tom and Tim, do not set the warmings to
false for general running of code, but I will say that it is
understandable that while running an insert query via code you may want
to do this so that the users do not see and unfriendly 'Access is about
to insert 123 rows of data' message,

The DAO execute method:

db.execute strSql, dbFailOnError

Where db is a DAO database object such as currentdb, does not produce
such messages.

USing setwarnings is, bluntly, careless programming. In my opinion, of
course.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me