IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Another MS Access Question
The query output (column order) has suddenly changed. The original order was Member, DSN, STEP, DISP, LRECL. Now the order is Member, STEP, DSN, DISP, LRECL.

Here is the SQL (MS Generated) being executed.

SELECT DISTINCT JCLPRC.Member, JCLDSN.DSN, JCLPRC.Step, JCLDSN.DISP, JCLDSN.LRECL
FROM QueryParameters, JCLPRC INNER JOIN JCLDSN ON JCLPRC.Key = JCLDSN.JCLPRCKey
WHERE (((JCLPRC.Member) Like [qProc]) AND ((QueryParameters.qUser)=[Forms]![Master]![fldUser]))
ORDER BY JCLPRC.Member, JCLDSN.DSN, JCLPRC.Step, JCLDSN.DISP;

What happened? How can I get the correct order back, other that creating a report?

TIA
Joe
New Why does the column order matter?
In any case, you can edit the SQL yourself.

Wade.

Q:Is it proper to eat cheeseburgers with your fingers?
A:No, the fingers should be eaten separately.
New Output is used for other documents
Via Cut-n-paste.

If you recognize the field names, you will recognize that the data involves mainframe programs. The mainframe programmers using this are not the most PC literate users. So having the output in the order needed is important. If they need to do multiple cut-n-pastes to get the results, they end up complaining, and I get the request to "fix" it.

As I told Peter, the SQL Select statement (SELECT DISTINCT JCLPRC.Member, JCLDSN.DSN, JCLPRC.Step, JCLDSN.DISP, JCLDSN.LRECL) IS in the order I want the output. However the output is as if the select statement was (SELECT DISTINCT JCLPRC.Member, JCLPRC.Step, JCLDSN.DSN, JCLDSN.DISP, JCLDSN.LRECL )

Again, it WAS working correctly. The users do NOT have direct access to the queries, they are executed via button on a form.
New Ouch.
And I missed the difference in output vs the SQL. Mainframe programmers with no PC experience are more of a liability than an asset, IME.

Facing that, my solution would be "ditch Access", but it sounds like that is not an option. :-/ (Doncha hate it when MS software second guesses you...)

Wade.

Q:Is it proper to eat cheeseburgers with your fingers?
A:No, the fingers should be eaten separately.
New Wish I could ditch Access
But it is the only "tool" I have available. Desktops are locked down, can't even change your screen saver...

fwiw, PC users without Mainframe knowledge are equally inept. The project I am working on consists of analyzing mainframe programs and data. Corp hired both MF and PC users. I can do MF, UNIX, and PC, so I get stuck creating tools for each to use, and helping each learn/use the other "world". Still haven't decided if MF learning PC is harder or PC learning MF is harder.
New Harder?
Hmm.

You assume they are smart. They've survived the worst of the worst. In my company, these poor bastards had to write about 3 pages of JCL to copy a damn file. It was insane.

You figure that if you show them a way to make their life easier it will be good.

So you show them some regular PC apps, throw in a bit of development system, and it is incredible. Their productivity skyrockets by a factor of 10, at least from their point of view of what they were able to do before. Too bad that 10 to 1 isn't good enough when you are trying to escape old mainframe apps. It's like gravity, you have to reach escape velocity or you get sucked back in.

And they love using Excel for interactive data production, stuff they used to do in the MF in COBOL. They get trapped in end user applications for production all the time. Of course, this is a management problem, but these apps end up in production with no oversight since they are no longer using the expensive MF resources.

They take either the hardest path (let's just run that data over the 20 years old programs in the MF, since we know how), or the esiest (to them), let's run the data over this new GUI whizbang interactive program we just wrote.

Boss says use the new system.

Ooops, let's run it again, it is a daily task.

Ooops, this fun pretty environment sucks for automation (at least from their point of view), so guess what? The expensive highly skilled MF programmers just got a daily production task assigned to him.
And every time they take a task off the MF, they end up creating just one more interactive task that isn't designed to be automated.

These tasks usually depend on a single person's PC for producton since it needs the local application stack. All of a sudden, the entire groups needs all the apps installed in case they need to run it. But their PCs are development PCs, with a variety of mutually exclusive stuff installed. No one every trusts anything other than the original PC.

So a couple of years later, 1/2 the MF staff is dragged down in Windows style production, and then they call me in to rewrite what I can using Unix environment.

While attempting to train the MF/PC people.

But they ALREADY went from MF to PC, they HATE the concept of lots of typing, they just escaped that, they have no foundation for toolbox programming, and they believe that there is simply no better way for any particular task, since they already moved it.

They consider a better method of cut&pate to be the ulitmate productivity enhancer. They lecture proudly on how fast they wrote 10,000 lines since they just had to copy a bunch of subroutines from a few other systems.

And GUI code generators? Love'm. For a while, they were paid by line.

They hear enough about Unix to think it is far more complex than anything they've ever done, it does it wrong, and they don't like it.

You get tired of fighting with them, and spend more time writing scripts to automate them out of existence than pretending to train them.

So most of them are done. They've been converted into one-off production monkeys rather than real programmers. Maybe I was able to salvage 3 people out of 20 in my career of doing this.

The other direction?

PC means way too much. Focus. Do you mean MS centric desktop programmers with SQL back end experience? Or do you mean webby gui people? Or back end transaction processing with a variety of systems, just not MF?

Or do you mean Unixy people? Which is way different from the above? Or even Linux server / windows desktop people, which is yet a different group.

Of the various groups, I'd say old style Unix people pickup the JCL and concepts faster than most, but they will end up writing scripts on the Unix side to control the MF via tn3270. Whining the whole time how much it sucks. Been there, done that.

After that, you might get a tiny tiny subset. If you have a well defined highly constricted world, then MS centric programmers will probably be the best starting point. The MS way is like the MF way. Do it our way or suffer. You'll be suffering anyway, but not as much. They are probably better at adapting to the MF contraints. Sheep can be guided.

Anyone else with a variety of tools under their belt will be driven crazy by the restrictions of dealing with the MF. They might initially do fine (if you held a gun to their head), but a few weeks later they'll be looking of the next gig.


New Well...
Anyone else with a variety of tools under their belt will be driven crazy by the restrictions of dealing with the MF. They might initially do fine (if you held a gun to their head), but a few weeks later they'll be looking of the next gig.


Me, I'd just pull all the data off the MF and fix it on the *NIX machine. The figure out a way to pipeline it to the people that want it.
New Re: Another MS Access Question
Can't you just drag the columns around in the query editor?
New I have dragged to columns around
SELECT DISTINCT JCLPRC.Member, JCLDSN.DSN, JCLPRC.Step, JCLDSN.DISP, JCLDSN.LRECL

The SELECT statement shows the order that the query columns are in. Member, DSN, Step, DISP, and then LRECL.

The output is being sequenced by the table, then column. Member, Step, DSN, DISP, LRECL.

I've tried leaving the table unsorted, various output orders, even dropping the member column. Then the output is Step, DSN, DISP, LRECL.

The query WAS working correctly. I have no control over the version of Windows, nor Office, nor Access. Corporate does remote upgrades.
New How peculiar.
Has anything changed? Version of Access? Has anyone compacted/repaired the database?
New I am the "DBA"
Since I am working with mainframers, they have no idea of how Access works. I have not compacted the database since the last time I refreshed the tables (Nov 1st). Version of Access is something that I have no control over. Updates and "patches" are applied remotely overnight, whenever corporate deems necessary.

If you think it would help, I can always try to "repair" and "compress" the database. I usually get in before anyone starts using the app.
New I'm clutching at straws, TBH
But has the version changed? This may explain your situation, even if it does not offer a solution.
New I understand...
the clutching at straws. To make matters "worse", it happened before. I don't remember if it was this query or another query. Anyway, I deleted the query and recreated it, and the problem "went away", until now.

Since I don't have any control of the versions, I don't know what I was running, nor what I am running. iow, I have no way of knowing if/when I get new upgrades.

New Time to put a report in the way, I think
At least that way you'll have control over the column order.
New Ok, now I REALLY cornfuzed
Ran the compact/repair database utility. No change.

So I deleted the query and recreated it.

Works like it should. SQL select statement is EXACTLY like the original.

What a PIA.

Thanks Peter and Wade for suggestions.

If anyone else knows what might be causing this, please let me know. I hate having to recreate the query on a regular basis.
New Possible theory...
the SQL gets boiled down to a binary-verion-meta data thingy. I'm guessing Access then stores this query. If the query is semi-used often and it doesn't change, it'll pull the binary version rather than reboil the text down.

Now...the question is why the boiled down version was somehow modified. No idea there, but if Access thought the query was still the same, it wouldn't recalculate it.

That would explain why 'recreating' the sql fixed the problem.
New Are the tables linked from elsewhere?
It is possible that the column order was swapped in the source table. Access copies things like table structure when the link is first defined. Unless the links are refreshed, all kinds of strange things can happen when the source tables are modified later on.
     Another MS Access Question - (jbrabeck) - (16)
         Why does the column order matter? - (static) - (5)
             Output is used for other documents - (jbrabeck) - (4)
                 Ouch. - (static) - (3)
                     Wish I could ditch Access - (jbrabeck) - (2)
                         Harder? - (crazy) - (1)
                             Well... - (folkert)
         Re: Another MS Access Question - (pwhysall) - (6)
             I have dragged to columns around - (jbrabeck) - (5)
                 How peculiar. - (pwhysall) - (4)
                     I am the "DBA" - (jbrabeck) - (3)
                         I'm clutching at straws, TBH - (pwhysall) - (2)
                             I understand... - (jbrabeck) - (1)
                                 Time to put a report in the way, I think - (pwhysall)
         Ok, now I REALLY cornfuzed - (jbrabeck) - (1)
             Possible theory... - (Mycroft_Holmes_Iv)
         Are the tables linked from elsewhere? - (scoenye)

Is that a Dune reference?
63 ms