Solved

An alternative to a pass through query

Posted on 2008-06-26
29
260 Views
Last Modified: 2013-11-29
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
Comment
Question by:deanlee17
  • 15
  • 13
29 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21872613
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
 

Author Comment

by:deanlee17
ID: 21872644
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21872660
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
 

Author Comment

by:deanlee17
ID: 21872827
Hi Kelvin, see attached for a slightly better explanation...
explanation.doc
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21872845
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21872852
Also, the local data table - is it part of any relationships?
0
 

Author Comment

by:deanlee17
ID: 21872916
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
 

Author Comment

by:deanlee17
ID: 21872926
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21872949
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
 

Author Comment

by:deanlee17
ID: 21873008
No mate i could not return the same data as the pass thru query.

Students1 only exists locally.Comments only exists locally.

Cheers
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873027
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
 

Author Comment

by:deanlee17
ID: 21873047
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873050
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873074
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:deanlee17
ID: 21873142
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873186
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873226
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
 

Author Comment

by:deanlee17
ID: 21873242
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
 

Author Comment

by:deanlee17
ID: 21873272
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
 

Author Comment

by:deanlee17
ID: 21873443
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873641
OK, ta, I'll play with it in the morning (now about 11.30 p.m. here) and upload some suggestions
0
 

Author Comment

by:deanlee17
ID: 21873664
Brilliant thanks. Hope the champagne went down well ;o)
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873707
Does the students1_passtyhru give you live data? i.e. can you see the data?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873720
make that students1_passthru
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21873729
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
 

Author Comment

by:deanlee17
ID: 21873820
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 21905953
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
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 21917795
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
 

Author Comment

by:deanlee17
ID: 21923846
Mark....drop me an email deans_general at hotmail.com
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now