Web Development

Ask something
Question Locked

Access query: inserts dynamically reflected on input?

0 helpful
19 replies

by gruntingdwarf 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

19 replies

by 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

by 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.

by 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....

by 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.

by 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?

by 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..?)

by gruntingdwarf

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

by 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.

by 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...

by 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...

by 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.

by 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.

by tony hopkinson

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

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.

by 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! ;)

by 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?

by gruntingdwarf

...short for "Otherland."

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

This discussion is closed to new replies.