refresh the front enq queries from the back end?

[Locopete99]

Hi Guys,

I need some help, or pointing in the right direction.

I have a database that 30+ people are using over 4 departments.

Department 1 raise an order improvement and assign it to department 2.
Department 2's interface (a subform running off an on timer update query) refreshes and displays new work to do.
They complete the improvement and assign it back to department 1.
Department 1's interface (a subform running off an on timer update query) updates and shows the reply and then they complete this and update the customer.

With 30 people (at least 6 per department) using the forms, this is meaning that the back end refresh (this deletes a tables info and then re-runs a query and repopulates the table) is running at varying intervals between the 4 minute refresh rate. This is leaving most users showing a #deleted message for most of their experience, until their version updates.

I need to find a way around this and to this end I have 2 questions.

1) is there a way to get the refresh to occur on the back end? Theortically then this should update everyone at the same time and get rid of this message.
How do I get this to operate from a back end that isn't open?

2) If I swap to an update query so the information isn't deleted, will this remove any information that isn't relevant, I.e when the closed tick box is ticked or when it is assigned to another department?

Any thoughts or ideas would be greatly appreciated.

Thanks guys!!

 

[Peter Reid]

Using an update query instead of delete/insert will certainly get rid of the #DELETED.

If you want 'irrelevant' information 'removed', you can use the update query to reset it

Of course, sharing backend rather than trying to maintain many local copies would probably be better all round

 

[Locopete99]

Thanks

Do you have any tips for getting the back end to handle the refresh?

I know how I want to do it but I have the following problems,

1) The backend isn't open, so how do i get it to do an on timer refresh?
2) how do I get the backend to refresh the queries in the front end without moving them all to the backend?

Thanks

 

[Minty]

It's not normal to need to delete tables every time something is updated. This would indicate a problem with either how your data is stored, or how you are processing / reporting on the data being entered.

Can you post up the table design / relationships?

 

[Locopete99]

Hi Minth,

Thinking about this I might be doing it the wrong way.

I have sub tables to collect information from my main tables (by my queries) to run my sub forms.

Can I run my sub forms straight from the queries?

 

[Minty]

Yes - a sub form recordsource can be a table or query.

 

[Locopete99]

Thanks, How can I take the record selectors off of the Query subform?

They look a bit horrible on the subform

 

[Minty]

In form design , properties window, on the format tab there are options for Record Selectors. Turn them off.