Question

Locked

Access query: inserts dynamically reflected on input?

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

This conversation is currently closed to new comments.

19 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Use a while loop

by Tony Hopkinson In reply to Access query: inserts dyn ...

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

Collapse -

it's about Access, not SQLServer...

by GruntingDwarf In reply to Use a while loop

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.

Collapse -

Well I'm a bit rusty on access

by Tony Hopkinson In reply to it's about Access, not SQ ...

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

Collapse -

trying to make clear myself...

by GruntingDwarf In reply to Access query: inserts dyn ...

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.

Collapse -

So, you want an infinite loop?

by ThumbsUp2 In reply to trying to make clear myse ...

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?

Collapse -

Not only I get the picture...

by GruntingDwarf In reply to So, you want an infinite ...

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

Collapse -

Hey Thumbs :-)

by GruntingDwarf In reply to Not only I get the pictur ...

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

Collapse -

re: Helping a Dwarf.

by ThumbsUp2 In reply to Not only I get the pictur ...

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?

Collapse -

"Other" is...

by GruntingDwarf In reply to re: Helping a Dwarf.

...short for "Otherland."

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

Collapse -

Hey Thumbs,

by GruntingDwarf In reply to So, you want an infinite ...

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

Back to Web Development Forum
19 total posts (Page 1 of 2)   01 | 02   Next

Software Forums