Linking JDE OneWorld to MS Access

[jimmidopolus]

Hi. Hopefully a quick question.

I have joined a company running JDE OneWorld as a 'zero client' using Citrix Server. We evaluate data in a MS Access database installed on the client, so rather than exporting to Excel from JDE and importing to Access from Excel, we are hoping to link the JDE data in directly.

I have so far come accross limited information on this. I have read about using ODBC but think the Citrix aspect removes the possibility.

Any ideas???

 

[monkeylizard]

If MS Access is INSTALLED on the Client then just use ODBC. Citrix should not be involved in any way. Have the Access DB get External Data from the SQL or Oracle JDEdwards DB.

If Access is on the Citrix servers and the clients use Access as a Published App via Citrix, then setup the app on the Citrix box to use ODBC to hit the JDE Databases in SQL or Oracle. Then the clients should be simply using the configuration on the citrix servers.

Monkeylizard
-Isaiah 35-

 

[infomania]

I have a similar setup (Citrix Client). Access was installed by the ASP. They needed to do some work to grant permissions to qualified users in order for the ODBC logon to be transparent.

Queries take a little longer using linked tables in Access. One thing I have found is that if you open a table for viewing in Access, the data you see is not necessarily what is in the table. Access has a nasty habit of duplicating field values in the view when the values are different in the JDE table.

What is troublesome is doing left joins in Access. In many cases, I needed to create sub queries that could be used as a subset of the main table.

Access timeouts are also a concern. Unless you are constrained using indexed fields, your queries may time out. My way around the problem, again, was to create subquery views.

F4211 and F0101 are my best friends!

One other thing. We occasionally have problems with the lock file or ODBC conn. Somehow the connection gets corrupted blocking other users from accessing the application. To date the only way to resolve this is to have all users quit the app, killing the lock file. I'm not sure the cause of this -- perhaps a Citrix issue.

 

[monkeylizard]

Keep in mind that JDE is an ENTERPRISE scale set of applications and SQL/Oracle are ENTERPRISE scale databases. Access was originally designed (and really still is) nothing more than a DESKTOP/WORKGROUP scale application. Its functionality is limited and so are its abilities (eg. total record count). I wouldn't recommend using too much ACCESS as a pass-thru to JDE tables for custom apps. You would probably be better of (if possible) to code your custom apps (VB, VC++, etc)to do the data set selections and querying and have those apps connect to SQL/Oracle using ODBC directly.

Putting Citrix into the mix with Access is like trying to get 30 people across a raging river using a rope bridge that's patched with duct tape and dental floss. It'll work...most of the time, but don't expect a lot of reliability.

Monkeylizard
-Isaiah 35-

 

[mpatel]

you could also try

http://www.jdedirect.com

these are JDE ODBC drivers that work without the JDE client software being installed..