by Michael S. Kaplan, published on 2012/06/25 07:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2012/06/25/10323279.aspx
The other day, Question Boy asked me via email:
Mr. Kaplan, I normally would never contact you directly in this unsolicited manner, and I completely understand if you do not have the time or inclination to assist me since this is outside of the standard forum premise, but I have been trying to get some help on a specific issue relating to creating a progress meter for MS Access. I basically had resigned myself to believe it was not possible when Doug Steele mentioned that he thought he remembered you might have a technique to do so. If you are interested in helping out, please take a look at the thread or reply to this e-mail and I will post back any solution you provide so others may benefit as well. I thank you most sincerely for your time and apologize once more for disturbing your in this manner. Sincerely, QuestionBoy
Let me start by saying that I probably don't have a good answer. But with that said....
This one takes me back.
The year was 1998.
I was going through a phase in my life where I was mostly writing C and C++ code.
But I still knew Visual Basic (VB 6.0 at the time) was pretty powerful, and I would get annoyed by people who would talk down to it.
I had just put the TSI Synchronizer out there, mainly because I was naive enough to think Jet Red replication was not going to be left for dead a few years later (they decided to invest in Data Access Pages and ADPs instead; I wonder how that worked out? <grin>).
Anyway, I had just hooked up the event support for replica creation in the TSI Synchronizer, and I was bored so I added some hidden methods in there:
to go along with the non-hidden MakeReplica method.
They each have Begin, Progress, End, and Fail events.
Perfect for setting up one's own progress meters, or allowing graceful cancels, or whatever.
I got permission to do this from someone the Access team (as with the rest of the TSI Synchronizer, though they did ask what exactly these other methods had to do with replication. I admitted that they had nothing to do with replication, and agreed to mark them hidden.
I think 12 people noticed them over the years.
The CompactDatabase and RepairDatabase methods made their way into other tools, leaving just ExecuteDdlSql as the odd man out.
It is very badly named since it can be used by any query that returns no rows, which does apply to some DML queries and doesn't apply to all DDL queries.
It's just that I didn't have time to build other data objects, and my brief experiments with reusing DAO and ADO objects convinced me that was never gonna happen.
If you look at Question Boy's ask, this won't help him since we wants something that directly integrates with an Access form's behavior -- this solution is much more for a heavy duty DoCmd.RunSQL call.
Breaking into what is going on internally what Access is doing with it's Jet session is a much bigger deal, it involves code somewhat like the TSI ImpExp Spec Tool (2000/2002) and the way it can redirect what Access is looking at, combined with the TSI Synchronizer's events.
I've done it myself (once), for a client that Microsoft referred me to and gave me permission to give them the source files (which have a lot of internal Jet/Access knowledge in them). It was a fairly lucrative consulting job at the time, probably the biggest Access contract I'd had in a while. They were under NDAs too about the internal stuff, but it worked out well.
Anyway, the utilities were able to handle anything other than Jet 3.5 and Jet 4.0 (which is all I had permission to do this for, so even though it would be easy to create a new version for the new Jet replacement that Access uses, it could also get me in a lot of trouble....
So, if you find yourself wanting to have this type of functionality and were peeved that Access could do it but you couldn't and you are using Jet 3.5 or Jet 4.0, then take a look. :-)
I know it won't help Question Boy's specific issue (one probably better managed by
though each of those can be quite complicated....
Doug Steele on 25 Jun 2012 7:47 AM:
Quite a shock seeing my name associated with this question! However, I checked the thread in question, and I see I'm not actually the one who sicced him on you!
Joshua on 25 Jun 2012 10:04 AM:
I've had this come up over and over again for SQL batches. Seems to be a generally unsolved problem and progress bars are not worth 10x performance cost.
Michael S. Kaplan on 25 Jun 2012 1:42 PM:
No worries Doug, it was a fun trip down memory lane! :-)
Michael S. Kaplan on 25 Jun 2012 1:42 PM:
Well Joshua, my ExecuteDdlSql method is quite fast, FWIW. :-)
Albert D. Kallal on 26 Jun 2012 2:06 PM:
A marvelous story. And I appreciate you sharing this with us. Now that the Access team owns that JET code then perhaps a few well placed beers and pizza's to the right person on the Access team would result in this ability being exposed!
On the other hand – perhaps we send the beer and pizza to you? I sure you would love a few days back in your old war trenches in those Access Halls at Redmond!
Makes we wonder if some interesting un-documented call backs exist for JET that we don't know about either.
I am going to have to do some digging on this one!
Once again, great story – thanks for sharing. A
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal@msn.com
Question Boy on 26 Jun 2012 6:14 PM:
Sorry Doug, I miss spoke as it was in fact Marshall that mentioned Michael's name.
Thank you once again for the answer!
Michael S. Kaplan on 26 Jun 2012 8:13 PM:
For them, this would in fact be easy -- they are in on that loop and raising the events would be easy, and interesting.
Maybe I should mention it to them (probably too late for v.Next, but maybe some day).
aaron kempf on 12 Nov 2012 11:50 AM:
you're crazy.. ADP / DAP are two of the best products to ever come out of Microsoft. I built solutions that leveraged Office Web Components, and these reporting solutions are sooooo incredibly popular, they are STILL IN USE at Microsoft, Expedia and Safeco. DAP was fantastic. ADP was even better.