INSERT does, and doesn't.

  • Thread starter Thread starter .
  • Start date Start date
?

.

Works on dev computer.

Table hst contains some fields common to table tab1.
Whenever a value changes in tab1, code adds rec to hst,
values coming from tab1's updated rec:

sel = "select <common fields> from tab1 WHERE ID ='" & sID & "'"
CurrentDb.Execute "INSERT INTO hst " & sel

Works on dev computer.

Not on user's.
All code identical (copied).
Debugging proves a record selected.
And debugger happily runs the Execute line;
without appending anything.

Hst has no key field.
I find no differences in designs of respective tables.
Access 97.
 
Hi,
Try this and see what the error is:
CurrentDb.Execute "INSERT INTO hst " & sel,dbFailOnError
 
I find no differences in designs of respective tables.

I found a difference: some fields' prop Required set to "yes".
That, it seems to me, should not prevent appending of
record, given that required fields would be geting their data.

But setting prop Required to "no" was the fix.
Unsatisfying fix, until I make sense of it.
 
fields would be geting their data.

The explanation:
Some time ago, but after the fields list was composed,
a field was added to the tables. So, there was one field
not getting its data.
 
Try this and see what the error is:
Thanks, I will try it, maybe tonight.
Click to expand...

Dan, it did the trick, named a field missing from the list.

Do you know why a trick was necessary in that instance,
whereas Access seems to ignore nothing else in that way.
 
I found a difference: some fields' prop Required set to "yes".
That, it seems to me, should not prevent appending of
record, given that required fields would be geting their data.
Click to expand...

But that is _precisely_ the meaning of the Required property -- the field
cannot be null ever ever ever. Setting a field to Required normally
requires a DefaultValue so that this situation does not arise.
But setting prop Required to "no" was the fix.
Unsatisfying fix, until I make sense of it.
Click to expand...
Well, either make sure there is a value passed in each INSERT command, or
put a DefaultValue property on the affected fields.

HTH


Tim F
 
. .net> said:
Dan, it did the trick, named a field missing from the list.

Do you know why a trick was necessary in that instance,
whereas Access seems to ignore nothing else in that way.
Click to expand...

When you use CurrentDb.Execute, it's not really Access that is handling
the interaction with the database engine. Instead, you are, by way of
the DAO objects you're manipulating. When *Access* does it (say, via
the RunSQL or OpenQuery methods), then Access takes care of checking for
any errors that are raised by the database engine. When you do it
yourself, it's your responsibility to do that. If you look at the help
entry for the DAO Execute method, it specifically tells you that no
error will be raised in your program if the method fails, unless you
specify the dbFailOnError options. That's at least partly because you
might conceivably want to ignore any errors from the database engine.
 
Back
Top