Open Data Access (ODA) and Pass-Through Queries (SQL)

[kayodeok]

I need to construct a Pass-Through Query using ODA to pull data from JDE into Access.

I have the ODBC Connection String as:

ODBC;CONVERTCURRENCY=Y;CONVERTJULIANDATES=;CONVERTUDC=Y;ConvertFun=1;DISPLAYOPTIONS=0;DSN=ODA-Pass-Thru;DateTimeFun=1fffff;ENVIRONMENT=PD7334;LONGCOLUMNNAMES=N;LONGTABLENAMES=N;NumericFun=12ff69;SHIFTDECIMALS=;StringFun=57e7d;SystemFun=0;;

This connection string was derived from Table F0911 (by looking at the Properties) but when I try to create a Pass-Through Query based on this Table, the content of the date fields all turn out blank, in addition, some other fields such as GLU, GLLNID, GLOPSQ all have weird characters in their field names.

I have the Pass-Through Query as:

SELECT * FROM F0911

(this was meant to be a simple test before I write more queries)

I have configured my ODA Connection to "Convert User Defined Codes" and "Convert Currency Values" but I note that the ODBC Connection String above shows:

CONVERTJULIANDATES=;

I changed this to:

CONVERTJULIANDATES=Y;

and re-run the query but there was no difference to the outcome.

Could someone help?

Regards

Kayode Okeyode

 

[Larry_Jones]

The definition of a Pass-Through query (For Access) is:

"An SQL-specific query you use to send commands directly to an ODBC database server (such as Microsoft SQL Server). By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data."

Given that definition, do you really need to use a Pass-Through query as opposed to regular SQL queries?

1. I doubt that the ODA ODBC driver supports pass-through
2. If you really need pass-through capability, then you want to use the ODBC/OLEDB driver for your actual database - not ODA
3. Please always include your system configuration info on your posts - otherwise we are ignorant of possibly relevant factors.

Cheers,

 

[kayodeok]

Thanks for responding; I know what a Pass-Through Query is, but I thought it would work in this situation (I wasn't aware it will only work with SQL Server), however, here is the background information you requested:

1. JDE is stored in an Oracle Database (backend) not SQL-Server
2. My access to ODBC has been blocked by our IT Department because it has read/write capabilities and our IT Dept doesn't know how to amend it to read-only.
3. I investigated ODA and tabled my findings to our IT Department, they confirmed it was read-only and have allowed me to use it to produce ad-hoc queries. At the time, the number of records in Table F0911 was around 270,000
4. At the moment, the number of records in table F0911 is growing at the rate of 80,000 records per month, therefore, Access is creaking under the load (currently at 536,000 but growing at around 3000 records per day).
5. I am unable to use SQL Server to manage the load from Access because the company policy forbids it.
6. I got together with someone who has extensive experience with Access and he recommended a Pass-Through Query though in retrospect, I am sure he wasn't aware I was using ODA to pull the data into Access and not ODBC.
7. We are currently using the JDE Tool to create reports but we have just recently gone live and the demand for reports is greater than what I can produce (I am the only Report Writer) so I use Access to produce stuff in a hurry.
8. Our configuration is as follows (I thought it was part of my sig?)

Configuration
System Configuration:
JDE OneWorld
Platform: UNIX
Platform Type: FAT Client
OS: Solaris
Database: Oracle
Release: ERP 8.0
Service Pack: SP21

9. Our users use the Web Client but I use the FAT Client because I write the reports

I hope this helps

Regards

Kayode Okeyode

 

[Sebastien Leclerc]

The use of pass-through will help you run queries that make a lot of aggregation (summarization) faster.

This is due to the fact that, depending on how your query is built, Access could fetch all the records and apply finters locally and summarize locally. Fetching all records is an operation that is limited by, among other things, the time it takes to send packets across the network.

When you log into OneWorld, the user id and password you are using are not the same as the one used to access the Oracle Database if you are setup like most other sites.

Knowing that, your IT department could probably create a special login in the Oracle environment that would not let you perform update / add / delete queries.

Good luck!

 

[Larry_Jones]

Kayodeck,

Sorry - your sys config was in your signature. I forgot that signature information is not show when replying to a post.

SQL Server is not the only DB Pass Through can be used on.

Still not sure what your problem is. Sounds like you've been importing data into Access (and reporting off the imported data) but now you just want to use Access as a database reporting tool against the data stored in Oracle instead of importing first into Access?

If the above is the case have you tried just linking the tables/views you want to Access?

You have to recognize that ODA has some query limitations on the number of table joins it can handle though.

By-the-way accessing the Oracle database directly via ODBC is dangerous only if your I.T. group hasn't done their job and secured the database.

 

[kayodeok]

This is what I currently do at the moment:

1. I am currently developing JDE Reports via the JDE ERW Tool but demand for reports is greater than what I can supply at the moment as I am the only Report Writer and we have just gone live.

2. Therefore, I have created an ODA Datasource to link (for example) Table F0911 in Access to create adhoc querys for the accountants until I can develop the reports they need via the JDE ERW Tool; since I am the only one with FAT Client, I created a make table query based on the linked table from JDE because they cannot access the linked table on a web client.

3. This was all fine, but the make table query now returns 536,000 records as at today and the database file (consisting of only one table) is now 1.3 gigabytes and is sluggish to query. At the time when I was asked to link to F0911 in Access, I was under the impression that it was a one-off job and we will not be creating adhoc queries via Access but it that was 3 months ago and now everyone links to my database so I cannot really restructure it as I see fit since so many files which I am unaware of will break.

4. I got together with with a colleague who has been in this position before (but with a different application - not JDE) and he advised using a Pass-Through Query to pull my data based on certain criteria (for instance, I could pull the data based on Companys since we have around 8 companies with an accountant for each one). Then I can create a Make Table Query based on each data pulled by the Pass Through Query for the Accountants to analyse and do as they see fit.

5. I am able to create the Pass-Through Query , it works fine, however, the date fields are all blank and I have no idea how to correct this. (when I link to table F0911 in the normal way and create a make-table query, the dates are all there).

It is 1905 hours here so I am now at home and have no access to JDE for specific examples and specific dialog boxes, but my main query is why is my Pass-Through Query returning blank fields for all the dates (from memory there are around seven dates: GLDVJ, etc - I can't recall the other dates from memory) and if possible, what do I enable in the datasource for the dates to show up (I have enabled everything to do with dates by clicking the "advanced" button when creating the datasource name).

6. Regarding the danger of accessing my records via ODBC, I thought ODA was read-only? The IT Department has already blocked access to ODBC for my Logon ID because of its read/write capabilities but we thought ODA doesn't write to the database? We have disaster recovery plans in place should the unthinkable happen we also take nightly backups of our Database.

Regards

Kayode Okeyode

 

[mpatel]

You could use jdeDirect ODBC drivers, they are read only and provide all the functionality of ODA and more (www.jdedirect.com).
In essence, you could then create linked tables in an Access database, and then have reports running over the linked tables – pretty much as you are currently doing.
They support standard JDE security, and will also operate without the JDE fat client being installed. Or you could simply give your users a copy of Crystal Reports and they can write their own reports making use of the jdeDirect ODBC driver..

Manoj

 

[wainwrr]

Reading this read, I'm really concerned that you're dealing with the wrong problem here. That seems to be that your employer needs lots of reports written, has gone live without them and is not employing enough resources to get themselves out of a hole.

You seem to have identified taht an earlier trip down the path you're on led to people latching onto what you created as a temporary fix and adopting it to teh point where it's now unalterable by you because there are too many dependencies on what you had done before.

You're in the position of cretaing fat client workarounds in a thin client company and that can't be good long term. You'll get good technical suggestions here about how to help with that but, and I don't expect it's what you want to hear when you're under pressure, you need to talk with your bosses sensibly about getting more resource on board temporarily. If they don't want to listen then you need to consider your options because it looks like you're making a big ugly rod and you'll be the only back in town.

This isn't a criticism of you in any way. We've probably all found ourselves in similar binds from time to time. It's meant as constructive advice to step back a bit and look at the real issue.

 

[kayodeok]

Thanks but jdeDirect ODBC drivers is not an option at the moment because I do not think I will be able to convince my employers of this approach.

We did look into Crystal Reports at some stage but my employers settled on the JDE ERW Reporting Tool; this decision was made at the board level.

I have temporarily abandoned the Pass-Through Query approach and am focusing on getting my reports deployed to the Production Server (after testing) so this should relieve some of the pressures I am under.

 

[kayodeok]

Actually, a list of reports were compiled prior to go-live, I worked on them and got them promoted in time. But this was a big project that my Employers have not undertaken before so it turned out that users opinions/needs were not canvassed and the big brass made a decision on what reports would be needed based on the reports that were used in the last accounting software we used.

I was sent on the JDE ERW + Advanced ERW class and expected to deliver these reports but we both know that a newbie with JDE cannot really deliver this stuff and it took a lot of convincing to get a consultant to help me out.

In the meantime, I had a responsibility to deliver these reports and had to resort to ODA and Access to fill in the gaps but I did not anticipate the demand and I had no idea at the time that users opinions were not canvassed on the reports I was working on (this has now been rectified).

It is a steep learning curve for me and I appreciate that you understand the pressure I am under and I do appreciate your view point that I am creating FAT Client Workarounds in a Thin Client Environment because I hadn't realised this before.

Unfortunately, my options are rather limited; I have been sent on a JDE ERW + Advanced Course, I am the only one who can create these stuff at the moment so I am morally obliged to see it through. However, it has been agreed at board level to send someone else on the course as a backup.

I have temporarily abandoned the ODA/Access approach and am focussing on delivering the reports via JDE ERW Tools.