Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

An alternative to a pass through query

Hi All,

I currently have a table in my database that was originally an excel spreadsheet and contained person data. This was fine originally and my database was built around it so it is the record course for a few forms/reports.
This table is now constantly out of data, the 'live' version of this table is held as a report in an external database (unite). I need to keep this table live and i do not have the option of calling it thru a pass thru query (from the unite database) as these queries cannot be record sources for forms/queries. Any ideas?

Many Thanks.
0
deanlee17
Asked:
deanlee17
  • 15
  • 13
1 Solution
 
Kelvin SparksCommented:
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.
0
 
deanlee17Author Commented:
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.
0
 
Kelvin SparksCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
deanlee17Author Commented:
Hi Kelvin, see attached for a slightly better explanation...
explanation.doc
0
 
Kelvin SparksCommented:
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?
0
 
Kelvin SparksCommented:
Also, the local data table - is it part of any relationships?
0
 
deanlee17Author Commented:
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)
0
 
deanlee17Author Commented:
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.
0
 
Kelvin SparksCommented:
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?
0
 
deanlee17Author Commented:
No mate i could not return the same data as the pass thru query.

Students1 only exists locally.Comments only exists locally.

Cheers
0
 
Kelvin SparksCommented:
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.
0
 
deanlee17Author Commented:
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
0
 
Kelvin SparksCommented:
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?
0
 
Kelvin SparksCommented:
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)
0
 
deanlee17Author Commented:
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!
0
 
Kelvin SparksCommented:
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'
0
 
Kelvin SparksCommented:
OK, is now champagne time (and not yet for this project). Leave any questions here Lee & I'll answer later or in the morning
0
 
deanlee17Author Commented:
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.
0
 
deanlee17Author Commented:
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.

0
 
deanlee17Author Commented:
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
0
 
Kelvin SparksCommented:
OK, ta, I'll play with it in the morning (now about 11.30 p.m. here) and upload some suggestions
0
 
deanlee17Author Commented:
Brilliant thanks. Hope the champagne went down well ;o)
0
 
Kelvin SparksCommented:
Does the students1_passtyhru give you live data? i.e. can you see the data?
0
 
Kelvin SparksCommented:
make that students1_passthru
0
 
Kelvin SparksCommented:
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?
0
 
deanlee17Author Commented:
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
0
 
Kelvin SparksCommented:
OK, here's the script.

Create a copy of table students1 (say students1Old) for safe keeping. Edit the design of students1 and delete all fields other than PSN. Save & rename as studentsSub.
Rename the passthru query as students1
Main form make a copy. call it Mainformsub. delete all data and functionality other than the stuff that is based on the local data. Drag this to the top and resize the form to suit. Save

On main form, delete the controls that you saved in mainformsub. Now add the mainformsub as a subform (parent child on PSN) - do not enable navigation buttons etc.

Create a new query using an inner join for students1 inner join studentsSub. Return all columns.

Bind the reports using students1 as recordsource to this new query.

You now be close to being in business.

Kelvin
0
 
OnALearningCurveCommented:
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.
0
 
deanlee17Author Commented:
Mark....drop me an email deans_general at hotmail.com
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 15
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now