Looking for Advice on best way to proceed

[GBalcom]

I wanted to make this a short, to the point thread, but I don't really see a way to. So...here is my conundrum. We have an ERP system that is overall really good, but I have the power to read (Read only connection) data from it and display as I want, making it better.

We are a manufacturer, and I've recently deployed tablets connecting with wifi on the shop floor for the employees to enter their time through it. That interface is a module of our ERP and works well for it's intended purpose. But the overall scope of the module is lacking. I've talked with the developers, and they have taken my suggestions into account, and at some point in the future may employ some of my suggestions, but I'd rather not wait....

One of the things I want to be able to do is look up which jobs are next for a given department. This involves a form with two combo boxes; one for department, and one for number of returned records (1,3,5, etc.)

This query is more complex than I originally thought. There are 3 primary tables to consider; tblDept, tblOpCode, and tblOperation. Operation Codes (tblOpCode) are a child of the Department. tblOperation lists all of the Operations for any work order in our system. So, tblOperation will first have to be filtered to only the nearest work orders (based on a target date field). So, when a user selects a department from the combo box, we need to look up all the cooresponding OpCodes for that department, then query that filtered tblOperations table to find all instances of those OpCodes that aren't set to 100% complete (another field).

How would one go about doing this? I'm less concerned about it being easy on me the programmer, and more concerned about speed, as it will be over a wifi connection. Unfortunately, the ERP database is not MS SQL (sybase), so no stored procedures for me. It is ODBC compliant.

Possibly several queries tiered off each other? Maybe an initial Pass-thru query to limit the data brought over? I'd love to hear the experts chime in.

I feel like alot of times I go the hard way with VBA and variables when there may be a simpler solution. As I'm not an everyday user, I tend to forget things!

Thanks!
Gary

 

[The_Doc_Man]

I would worry less about stored procedures and determine whether your back end can do real queries. I.e. how active is the back end? Anything that does SQL in any flavor whatsoever is active. ODBC compliance is merely a way of saying that you can send a data request and get data back in a way that conforms to a particular protocol.

Is there a database underneath the ERP system and can you get to THAT database? That is the real question of how to proceed.

 

[jdraw]

Gary your access to the current database is ReadONLY right? I asked because I've heard of many issues with Access FE to Access BE using wifi for RWED. Much corruption and lost data.

I haven't worked with Sybase (and haven't heard much of it recently -just googled and see it's now part of SAP)

As long as it's all read I see no issues. If you are doing updates, then hopefully SAP/Sybase is using transaction processing (BT/ET logic).

I hope I haven't misjudged the database design -- since it's ERP I am assuming a tested and well structure design.

 

[spikepl]

Show structure, taBles, forms ... anything graphic really. That would make it so much easier for outsiders to envision your data

 

[GBalcom]

Everyone; thanks for the responses thus far...

Doc_Man:
Yes, the ERP is Real...We've been using it for over a decade. It is a strong, well supported platform. We are on support with the company who made it, and update at least bi-annually. I have dug into this back end several times, and it is easy to access all of the tables and underlying data as necessary to pull this together. The 3 tables I described above are part of the underlying schema, not something I'm creating as an add on.

J_Draw:
You are correct; this will be a READ ONLY connection. The other modules on the table do write back to the database, and they work fine through the wifi. It's actually a pretty cool system, they "pool" the data and send it up to a controller on the server, which looks for the data in 15 second intervals, then pushes it up to the service server. Last week I thought I lost a bunch of transactions. It turns out we had a power outage, and the server hadn't restarted the controller. when I started it, all the transactions for 4 days shot through to the server db. It made me pretty happy.

Spike:
Not sure what I can post graphically. I've tried to spell it out, but I know pictures are worth 1,000 words. Let me see what I can put together tomorrow.

 

[The_Doc_Man]

If you are worried about speed, there is one and only one real way to handle that in the case you described. See if you can get permission to build a few queries/views/whatever they call them in the backend to conform to your requirements. If you have to pull the raw tables over the net to your FE system, that is nowhere near as efficient as pulling the output of a query-equivalent across the net. The returned data set is guaranteed to be smaller if it does any kind of filtration at all, and with network transfers, smaller almost always means faster.

 

[HiTechCoach]

Since you are running this on a wireless network you really should not use Access as the front end. Access just for not play well over a WiFi connection.

If this were my project is would use .NET. ASP.net would be my first choice.

If you must us Access then I would urge you to use unbound forms.

FYI - History time:
Microsoft SQL Server is started out as a a variant of Sybase SQL Server

Until 1994, Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.


Microsoft SQL Server 6.0 was the first version designed for NT, and did not include any direction from Sybase.

from Wikipedia

In 1988 Microsoft joined Ashton-Tate and Sybase to create a variant of Sybase SQL Server for IBM OS/2 (then developed jointly with Microsoft),

About the time Windows NT was released in July 1993, Sybase and Microsoft parted ways and each pursued its own design and marketing schemes.

Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems.

(In 1996 Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server.)


From: https://en.wikipedia.org/wiki/Microsoft_SQL_Server

 

[GBalcom]

HiTechCoach:
Thank you for the information. I will definitely be using unbound forms for this project.

 

[GBalcom]

Doc_Man:
I don't have any way of storing a query inside the SyBase db. Will Pass thru queries help?

 

[HiTechCoach]

You can use regular queries but Pass-through queries will probably be faster.

ALERT: Pass-through are are not process by JET/ACE so you can not use any Access SQL specific features like VBA functions.

If you convert an existing Access query into a pass-through query, it will use the proprietary Access SQL language, not the SQL for teh back end. This is an issue, since we are "passing" the query back to the SQL Server for parsing and processing. This means that the query must be written in the SQL language for the back end SQL Server.