Re: What record is INSERT failing on?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Jeff! Thanks for the response.

That seems like a huge workaround for something that should be simple. I
can't automatically add the missing userid's to the userid table because they
have to be checked by a human(make sure they are actual users and not typos).
Userid is not a primary key in the comments table, but is in the userid
table (also the only field there). There is an auto_incrementing key that is
primary in the comments didn't include it cause didn't think it mattered.

This would be a super easy feature to add. . . . I'm sure I'm not the only
one who would like to know which rec a query is failing on. Any way to
intercept and capture the output of a query execution?



"Jeff Boyce" wrote:

Hollie

I'm not there, so take any notions with several grains of salt...

If you can imagine a path that includes loading to a temp table, adding
missing IDs and appending to your permanent table(s), you can probably write
the queries and a macro to execute them.

By the way, if you use a unique index (No Duplicates) on that UserID, when
your query tries to append records that already exist, Access rejects them.
If you include SetWarnings Off (and back On again afterwards!), you can have
the warning message hidden from the users.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"frustrated" <frustrated@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D1B2F674-C5C8-4A30-9BB5-BDFB01DC7D21@xxxxxxxxxxxxxxxx
Hi Jeff,

Thank you for your timely response. This file is imported via the import
text file macro. I guess I could just hack it and load it into a
temporary
table then query that against the user id table, add the missing userids
or
remove the violating records then load that into the real database. *Just
not understanding why Access does not log the query errors.* I'm a grad
assistant at the university I work at and in a few weeks/months I'm going
to
be out of here. This process will have to be inherited by one of the other
staff members. I am positive they are not going to be able to do this, as
they are completely computer illiterate. That's why I implemented it in
macro
form. ..double click to happiness. So my main goal is to make this as
simple
for them as possible. Hence show error on query at which it happened(I've
also ran into other situations when I was needing a log of some sort).
Maybe
I should send the request to Microsoft. Can you think of any super user
friendly work arounds? I'm a programmer, so I can do pretty much anything
required to get this done. Any suggestions are welcome. Thanks and have
a
great day.


Sincerely,
Hollie



"Jeff Boyce" wrote:

If your incoming set of data must have a UserID to be successfully
appended,
could you run an initial query on that set of data to locate any records
that DON'T?

Regards

Jeff Boyce
Micorosft Office/Access MVP

"frustrated" <frustrated@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71E855D9-285C-4A7B-96C2-2A299F7E4DB2@xxxxxxxxxxxxxxxx
I have a database with records that have comments made by users. I
import
the comments and the associated user id. There is a referential
integrity
constraint on the comments table (comments, userID) to a table just
containing the user ids so I can make sure they are actual users. I
would
like to know what record the insert is failing on to determine if I
need
to
add the user id to the table or not. There are over 300 records
imported
at
a time. These records are imported vi a text file import macro.
Tested
and
works fine. I know the error is that the user id is not in the table.
The
record that errored *should* be logged (one of those error tables does
NOT
show up in this case). I just don't know where. Thanks for any
information.







.

Relevant Pages

  • Re: What record is INSERT failing on?
    ... Userid is not a primary key in the comments table, ... one who would like to know which rec a query is failing on. ... "Jeff Boyce" wrote: ... the queries and a macro to execute them. ...
    (microsoft.public.access.externaldata)
  • RE: Exporting to Excel
    ... GetUserId ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Exporting to Excel
    ... I did make the Criteria for the patientid field as GetUserId() not GetUserId. ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)