Questions

Access query: inserts dynamically reflected on input?

+
0 Votes
Locked

Access query: inserts dynamically reflected on input?

GruntingDwarf
Hi,

I have an Access 2002 query that inserts rows into a table.
As an input (FROM) for the query there is another query based upon the same table that receives the inserts.

Is there a way of having the main query read the rows just inserted? IOW, can these inserts immediately reflect on the input and so be processed on the same run?
From my experience it seems not possible, without ending and reprocessing the query for the newly added rows. I want the process to automatically get exhausted over a single run rather than manually iterate it several times.

Note#1. The rows inserted have values that would place them behind the existing ones in the sequence defined for the input sub-query, so they should still get into the process.

Note#2. The query has a WHERE clause that would stop the process once the sequencing column exceeds certain value, thus avoiding an infinite loop.

Thanks for any suggestion.
  • +
    0 Votes
    Tony Hopkinson

    Put an processed column in the table.
    reset it for all newly inserted records.
    set as you do it

    Declare @Processed int
    select @Processed = Count(*)from MyTable Where Processed = 0
    While @Unprocessed > 0
    Begin
    ...
    ...
    select @Processed = Count(*)from MyTable Where Processed = 0
    End

    Personally as a just in case, I'd put a passcount in. Increment and check during the loop. That way if you make a wee error would put you in an infinite loop, the code will break and report a useful exception.

    Worth a go, you might not need the flag column if there's some other combination of column values that will achieve the same thing.

    HtHs

    +
    0 Votes
    GruntingDwarf

    Hi Tony,
    thanks for your reply.

    However, your code looks like a sqlserver stored procedure, while my question regarded to an Access query.
    AFAIK, Access isn't capable of directly execute sqlserver procs - I understand the equivalent feature would be a VisualBasic function.

    The actual (straightforward) logic of my Access query as it is would fulfil my requirement without the need to build any additional function - Provided there exists a way to specify Access to open the query in a mode like, say, ADO's OpenDynamic, which reflects in the recordset, on a real-time basis, changes made to the database.

    Shouldn't such a thing exist as a native feature in Access, I guess I'll have to go with some additional control-logic in VB.

    Kind regards.

    +
    0 Votes
    Tony Hopkinson

    but it will sort of manage with most T_SQL.

    Just go into SQL View and type it in. All an SP is, is a compiled and named sql snippet.

    You want to display this while it's happening?
    Why? You know you've done it, just refresh the query.

    The technique by the way is one I've used in code. VBA should do it, not ideal but then if ideal was required, Access wouldn't be the tool of choice....

    +
    0 Votes
    GruntingDwarf

    OK, I'll try to explain my case in a simpler way.

    My query (Access 2002) inserts rows into a table, wich is the same table that it reads from.

    The newly inserted records will have to be processed in the same way as the existing ones. However, I cannot manage to get the new records automatically fed into the input. The query runs and terminates normally, but the new records were not taken into process - I have to relaunch the query for these records to be processed.

    I'd like this iterative process run automatically over a single run, as would do if each inserted record could dynamically be reflected on the input.

    Is this possible to do in some way?
    Does Access have some kind of option that enables a query to "catch" on the input those newly inserted records?

    Thank you.

    +
    0 Votes
    ThumbsUp2

    You said your query inserts records back into the same table it reads from and you want the query to run again automatically after the records are inserted which would insert more records and run again.... insert, run, insert, run, insert, run....

    Get the picture?

    +
    0 Votes
    GruntingDwarf

    ...it IS the picture I want to get!

    As for now, you tell me whether it's possible to do in Access, then I'll take care of make the loop finite ;-)

    (Hummm... Will an inhabitant of LandOfGiantTumbleweeds be able to help a darwf from OakForest..?)

    +
    0 Votes
    GruntingDwarf

    See you're pretty busy here, but pls don't forget this little dwarfie.
    Already got a skeptical verdict from Tony.
    Still hoping to hear from you though.
    From your comment I see you caught the idea as well.
    Perhaps you could come up with something, or should I give up...

    +
    0 Votes
    ThumbsUp2

    I have nothing against helping a Dwarf. Dwarfs are some of my favorite people. However, I'm having trouble finding a country named 'Other' on the world globe!

    Where are ya?

    +
    0 Votes
    GruntingDwarf

    ...short for "Otherland."

    I guess you couldn't find it even in the oldest Middle Earth maps...!

    +
    0 Votes
    GruntingDwarf

    Did you read my reply to you?
    Should I give up on this matter..? :-(

    +
    0 Votes
    ThumbsUp2

    He's much better at Access than I ever thought of being. I've avoided creating infinite loops for so many years now, I don't think I could create one if I tried!

    +
    0 Votes
    GruntingDwarf

    be nice to that aek sweetie... (hhhmmmm)

    +
    0 Votes
    Tony Hopkinson

    That can be done in some DBMS (not access), though I personally wouldn't recomend it at all.

    If I'd gotten to the point in a design where this was necessary, I'd be having a look at where I went wrong. Massive violation of the KISS principle.

    +
    0 Votes
    GruntingDwarf

    In my original post I mentioned "something like ADO's adOpenDynamic option".
    Didn't want to use the word "cursor" in order not to provoke reflex responses.
    But so far, I'd be agreeing with you in that Access isn't the tool; though might still try some VB.
    Let's see what Thumbs has to say...

    +
    0 Votes
    Tony Hopkinson

    You could even refresh the display to give an indication of progress, not to mention, a suitable pause to get a Ctrl-Alt_Del in if you happen to go infinite.

    Cursors are a fun topic, Avoid them if you can is the rule, and I've seen several exceptions that proved it. In fact I wrote yet another last Friday. When you need, em, you need 'em.

    +
    0 Votes
    GruntingDwarf

    So I'll go VBA, been thinking some logic that pretty matches that of your first reply - so I marked it.
    Here starts another story :-)
    Thank you both guys for your time.

    +
    0 Votes
    Tony Hopkinson

    on friday. Fixed that and then it blew up with an AV.

    Recursive process four differet exit conditions, called from two different starting points, the result used to mean three different things.

    My highest recommendation on recursion, is don't use it if there's any other solution. It's more trouble than it's worth.

  • +
    0 Votes
    Tony Hopkinson

    Put an processed column in the table.
    reset it for all newly inserted records.
    set as you do it

    Declare @Processed int
    select @Processed = Count(*)from MyTable Where Processed = 0
    While @Unprocessed > 0
    Begin
    ...
    ...
    select @Processed = Count(*)from MyTable Where Processed = 0
    End

    Personally as a just in case, I'd put a passcount in. Increment and check during the loop. That way if you make a wee error would put you in an infinite loop, the code will break and report a useful exception.

    Worth a go, you might not need the flag column if there's some other combination of column values that will achieve the same thing.

    HtHs

    +
    0 Votes
    GruntingDwarf

    Hi Tony,
    thanks for your reply.

    However, your code looks like a sqlserver stored procedure, while my question regarded to an Access query.
    AFAIK, Access isn't capable of directly execute sqlserver procs - I understand the equivalent feature would be a VisualBasic function.

    The actual (straightforward) logic of my Access query as it is would fulfil my requirement without the need to build any additional function - Provided there exists a way to specify Access to open the query in a mode like, say, ADO's OpenDynamic, which reflects in the recordset, on a real-time basis, changes made to the database.

    Shouldn't such a thing exist as a native feature in Access, I guess I'll have to go with some additional control-logic in VB.

    Kind regards.

    +
    0 Votes
    Tony Hopkinson

    but it will sort of manage with most T_SQL.

    Just go into SQL View and type it in. All an SP is, is a compiled and named sql snippet.

    You want to display this while it's happening?
    Why? You know you've done it, just refresh the query.

    The technique by the way is one I've used in code. VBA should do it, not ideal but then if ideal was required, Access wouldn't be the tool of choice....

    +
    0 Votes
    GruntingDwarf

    OK, I'll try to explain my case in a simpler way.

    My query (Access 2002) inserts rows into a table, wich is the same table that it reads from.

    The newly inserted records will have to be processed in the same way as the existing ones. However, I cannot manage to get the new records automatically fed into the input. The query runs and terminates normally, but the new records were not taken into process - I have to relaunch the query for these records to be processed.

    I'd like this iterative process run automatically over a single run, as would do if each inserted record could dynamically be reflected on the input.

    Is this possible to do in some way?
    Does Access have some kind of option that enables a query to "catch" on the input those newly inserted records?

    Thank you.

    +
    0 Votes
    ThumbsUp2

    You said your query inserts records back into the same table it reads from and you want the query to run again automatically after the records are inserted which would insert more records and run again.... insert, run, insert, run, insert, run....

    Get the picture?

    +
    0 Votes
    GruntingDwarf

    ...it IS the picture I want to get!

    As for now, you tell me whether it's possible to do in Access, then I'll take care of make the loop finite ;-)

    (Hummm... Will an inhabitant of LandOfGiantTumbleweeds be able to help a darwf from OakForest..?)

    +
    0 Votes
    GruntingDwarf

    See you're pretty busy here, but pls don't forget this little dwarfie.
    Already got a skeptical verdict from Tony.
    Still hoping to hear from you though.
    From your comment I see you caught the idea as well.
    Perhaps you could come up with something, or should I give up...

    +
    0 Votes
    ThumbsUp2

    I have nothing against helping a Dwarf. Dwarfs are some of my favorite people. However, I'm having trouble finding a country named 'Other' on the world globe!

    Where are ya?

    +
    0 Votes
    GruntingDwarf

    ...short for "Otherland."

    I guess you couldn't find it even in the oldest Middle Earth maps...!

    +
    0 Votes
    GruntingDwarf

    Did you read my reply to you?
    Should I give up on this matter..? :-(

    +
    0 Votes
    ThumbsUp2

    He's much better at Access than I ever thought of being. I've avoided creating infinite loops for so many years now, I don't think I could create one if I tried!

    +
    0 Votes
    GruntingDwarf

    be nice to that aek sweetie... (hhhmmmm)

    +
    0 Votes
    Tony Hopkinson

    That can be done in some DBMS (not access), though I personally wouldn't recomend it at all.

    If I'd gotten to the point in a design where this was necessary, I'd be having a look at where I went wrong. Massive violation of the KISS principle.

    +
    0 Votes
    GruntingDwarf

    In my original post I mentioned "something like ADO's adOpenDynamic option".
    Didn't want to use the word "cursor" in order not to provoke reflex responses.
    But so far, I'd be agreeing with you in that Access isn't the tool; though might still try some VB.
    Let's see what Thumbs has to say...

    +
    0 Votes
    Tony Hopkinson

    You could even refresh the display to give an indication of progress, not to mention, a suitable pause to get a Ctrl-Alt_Del in if you happen to go infinite.

    Cursors are a fun topic, Avoid them if you can is the rule, and I've seen several exceptions that proved it. In fact I wrote yet another last Friday. When you need, em, you need 'em.

    +
    0 Votes
    GruntingDwarf

    So I'll go VBA, been thinking some logic that pretty matches that of your first reply - so I marked it.
    Here starts another story :-)
    Thank you both guys for your time.

    +
    0 Votes
    Tony Hopkinson

    on friday. Fixed that and then it blew up with an AV.

    Recursive process four differet exit conditions, called from two different starting points, the result used to mean three different things.

    My highest recommendation on recursion, is don't use it if there's any other solution. It's more trouble than it's worth.