An alternative to a pass through query

Why not delete the local copy & link to the remote copy. Then name the linked table in Access with the origimal name - this will work if the field names remain the same.

Sorry Kelvin i forgot to add that the Students1 table is also written back to by a few forms. I guess i could split it and let it write back to the students1 table as it does and have another table to pull info thru. The trouble being that some forms and a report in particular have this one table as a record source and uses most fields from it, so splitting it would obv cause problems there.

Not sure that I understand - what is the local table you use? Is there a remote copy that is the same and do you have functionality off the two tables? If that is the case, then delete the local copy and create a second link to the same remote table & name it the same as the old. You can link more than once to the same remote table & use different aliases (local names).

Perhaps if you give me the actual table names and where the data actually is it will help me.

Hi Kelvin, see attached for a slightly better explanation...
explanation.doc

OK, I get from this that you have a local table (name plase) that has data that is updated via a passthrough query from Unite. Is that a table or a view (query based on a number of tables in Unite)?

You want to have dynamic live data. I assume you followed the earlier thread and have linked a table to this data (name of the linked table in oracle and Access please)

Is anything based on this newly linked table?

Also, the local data table - is it part of any relationships?

The local table is called Students1. The info in there which is updates is via code (to assign a learning mentor) and via a form to add comments.

I have created a pass thru query that captures the Unite report (that created the original spreadsheet/students1table just to see if it was possible, its not linked to anything yet)

If i click the 'relationships' button in the main window then the Students1 table is not in there. The Students1 table is in at least 1 query though.

When you created a linked table from the last thread, could you link to the same data as returned in the pass through query?

Does students1 exist as a table in the remote data and is the field comments part of it?

No mate i could not return the same data as the pass thru query.

Students1 only exists locally.Comments only exists locally.

Cheers

So what does the pass through query get it's data from? I don't know Oracle - is it
a view (a combination of tables and selected fields or a stored procedure (may have another name in Oracle) where parameters are passed to it.
Another thought is to use the pass through query and a form timer to periodically refresh the data in the background (not recommended but it works!) - no need to manually run it.

To create the passthu query i run the report in unite (the software that sits on the oracle database) and copied the SQL and created the pass thru query with that. SQl code attached....

select t2.s_studentreference "PSN",t1.p_surname "Surname",t1.p_forenames "Forenames",t1.p_dob "Date of birth",count(distinct decode(substr(t3.e_reference,1,3),'7AS',t3.e_id,'7A2',t3.e_id,null)) "AS/A2",max(decode(to_char(t4.a_start,'DY'),'MON',t3.e_reference,null)) "Monday",max(decode(to_char(t4.a_start,'DY'),'TUE',t3.e_reference,null)) "Tuesday",max(decode(to_char(t4.a_start,'DY'),'WED',t3.e_reference,null)) "Wednesday",max(decode(to_char(t4.a_start,'DY'),'THU',t3.e_reference,null)) "Thursday",max(decode(to_char(t4.a_start,'DY'),'FRI',t3.e_reference,null)) "Friday",decode(sum(decode(t5.s_reference,'S6ELS',1,0)),0,'No','Yes') "In Humanities" from capd_student t2,caps_valid_codes t7,capd_section t5,capd_module t6,capd_moduleactivity t8,capd_person t1,capd_activity t4,capd_moduleenrolment t3 where t2.s_id(+)=t1.p_id and t7.vc_code(+)=t3.e_status and t7.vc_domain(+)='enrolmentstatus' and (t3.e_student=t1.p_id) and (t3.e_module=t6.m_id) and (t6.m_modulesection=t5.s_id) and (t3.e_type='CLS') and (t7.vc_ref='L') and (t8.ma_activitymodule=t6.m_id) and (t8.ma_activity=t4.a_id) and (unite.getMark(t4.a_start,t4.a_end,(sysdate-14),t4.a_weekpattern,t4.a_weekpattern)=1) and (t3.e_name not like '%%TUTORIAL%') group by t2.s_studentreference,t1.p_surname,t1.p_forenames,t1.p_dob,t1.p_surname,t1.p_forenames having (count(distinct decode(substr(t3.e_reference,1,3),'7AS',t3.e_id,'7A2',t3.e_id,null))>0) order by t1.p_surname,t1.p_forenames

You also need to consider business rules here. You're mixing & matching. Can the comments field be created in Oracle? Local stored data associated with remote data is sounding decidedly dicey to me. Are you picking up new students each upload or also getting updates to existing data?

Looking at that LOVELY bit of code, I suspect it is a view (or query). Do you know it's name? When you tried linking tables, did you get a couple of tabs - one being tables and the other being views/reports/queries or similar? If so, was the name of this listed? If it was you can link to it and it will behave like a linked table (although possibly (probably) read only)

I very much doubt the comments field will be created in oracle, in fact im sure it wont.

Part of my database is updating itself with new students and their course codes (the risk2 table) obv the students1 table is static, this is causing major problems.

I know the whole situation is not ideal and dicey.

'Another thought is to use the pass through query and a form timer to periodically refresh the data in the background (not recommended but it works!) - no need to manually run it. ' sounds like an interesting route!

Ok, we may have to. How many copies of this access database will be running - just yours - or multiple users? The suggestion is to delete & reload the data periodically - does it need to be once a day, hourly, every minute or some other frequency?

Suggestion is to delete existing data & reload (using code) and have a separate table holding just two columns - PSN (I assume that is athe student identifier) and comments. You'll have to view the data on a student by student basis - no data grid style, so will need a mechaism to select them - not too difficult tho'

OK, is now champagne time (and not yet for this project). Leave any questions here Lee & I'll answer later or in the morning

i read ur last post, had a mini break down then at the end you said 'not too difficult tho' and life seemed worth living again lol.

I would say the data needs to be updated on an hourly basis, actually maybe a bit less often than that. The database will be split into 1 back end and multiple front ends.

Just digesting the second paragraph of your last post

Thanks.

Ok its going to take me a long time but im going to try to inport all tables and reduce the size of the db to a reasonable size and attach it just so as you can see how the forms/reports are layed out and linked to the Students1 table.

Actually ive uploaded it without the tables imported as i think you will be able to get the gist of it...

http://www.deans-place.co.uk/Copy%20of%20A%20level%20database%2012-6-08%202002%20format.mdb

Cheers

OK, ta, I'll play with it in the morning (now about 11.30 p.m. here) and upload some suggestions

Brilliant thanks. Hope the champagne went down well ;o)

Does the students1_passtyhru give you live data? i.e. can you see the data?

make that students1_passthru

To make things quicker in the morning, I have found the form mainform where you appear to do the work based on students1. Where else do you use students1?

Yes the students1_passthru does provide live data.

The mainform is the main form that uses the students1table. It is also used in the reports i.e report split

Cheers

Hi Dean and Kelvin,

I have just tried to catch up on this one and I have a couple of probably obvious questions

why do you need to update the table hourly?  is it being added to/ amended that often?

Also looking back through the posts it looks like you are as Kelvin said linking back to the Oracle equivalent to an SQL view (though I have no experience with oracle).  Sooooo could you just re-query the data? or am I miles off the mark.

Mark.

Mark....drop me an email deans_general at hotmail.com