[AccessD] Access 2007 - The story so far.

Dan Waters df.waters at comcast.net
Fri Aug 19 10:12:41 CDT 2011


The vast majority are still using Windows XP.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, August 19, 2011 4:08 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Access 2007 - The story so far.

Hi Dan

Couldn't you just ask clients to install SP1? Why shouldn't they?

/gustav


>>> df.waters at comcast.net 19-08-2011 05:17 >>>
I should have added that this only happened at one of my four customers, and
they all have earlier OS's.  So 3 of 4 apparently did something to get
around the problem, or 1 of 4 did something to cause the problem.  It wasn't
me!  ;-)

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, August 18, 2011 6:46 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

Yep, Correct.  It will perform without issue on the W7 SP1 PC it was
compiled on, but it will fail on any other PC's that are running an earlier
version of the OS.  This is way beyond an Access issue.  It impacts .NET,
VB6, all VBA etc - Basically anything that uses ADO in the code.  It kind of
sneaks up on you though as it run flawlessly on the PC it was compiled on,
but sent it out to your client who is on anything but W7 SP1 and it will
fail immediately with the usual variety of useless messages from Microsoft.

Makes you look like a complete idiot, especially as the you have changed
nothing in the code at all, just went from compiling pre SP1 to post SP1.
MS are meant to be working on a VBA patch, but I have seen nothing to date
and I have been following this since April 2011.

Here is the exact error you will get on any ADO line of code, just so you
know what to look out for.

"Class does not support Automation or does not support expected interface"

Yep, real helpful given a google search initially throws up a whole stack of
other issues that can cause the same error response.

Anyway, just be aware that is likely to bite a few of you sooner or later.

Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Thursday, 18 August 2011 11:16 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

I didn't have a problem with compiling code on my W7 SP1 machine.  The
problem came when I had compiled code on my machine, then copied the Access
mdb to another PC using and earlier OS.  If I didn't open the code and then
recompile it, the code would fail.

Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Wednesday, August 17, 2011 10:53 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

This issue is EVERYTHING to do with compiling code with ADO on W7 SP1
machines. See the links in the original email earlier today.

Cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
(VBACreations.Com)
Sent: Thursday, 18 August 2011 1:14 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

Hey y'all could we be a little clear (or those who have used both pls pipe
up) are these Ac2007 issues which are no longer issues in 2010 or what? I
understand if Darryl only has Ac2007 it would be a little hard for him to
comment, but maybe someone who has 2010 could please let us know if the
issues are still there or not? Thanks a lot. It's not that I have run into
this, I use W7 and 2010 and have not run into anything like them but I don't
want to say they don't exist.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Wednesday, August 17, 2011 10:49 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

I was able to resolve the W7/ADO issue with late binding to ADO:

Example:
    
    Dim cnnWorkgroup As Object
    Dim rstWorkgroup As Object

    Set cnnWorkgroup = New ADODB.Connection
    cnnWorkgroup.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
DBEngine.SystemDB & ";"

    Set rstWorkgroup = cnnWorkgroup.OpenSchema(adSchemaProviderSpecific, ,
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
    ... code using rstWorkgroup

This was the only place I needed it, so this solution may not work in your
situation.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Wednesday, August 17, 2011 8:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

Hi Everyone,

Ok... This turns out to be more interested than I thought.  What the root
cause of this issue was the W7 SP1 ADO SNAFU that MS are *still* trying to
fix for VBA.  My work PC is on W7 SP1, as a few of the other here, but many
are not (either W7 or Vista or XP).  Turns out the only solution is to roll
back SP1 completely off the development machine and block it from updating.
Hardly a long term fix.

So the script used to update the database on the other PC's was failing with
the front end tried to talk to the back end using ADO.  If I manually ran it
then Access seemed to fix the reference, but using script to open the accde
file it would always fail as per the links below.

<<http://support.microsoft.com/kb/2517589/en-us?sd=rss&spid=14481>>
<http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/508fa044-93f0-4
8be-b5c0-82b83fcdde0e/>

So, the (only) fix was to roll back SP1 (that took about 2 hours of the
computer faffing around) and then recompile the FE.  Now the script and FE
works as expected (I have used this method for years and it has never failed
until yesterday so I was suspicious).

Of course, there is still a whole notebook full of other A2007 oddness that
is yet to be resolved.  On the case :)

Cheers
Darryl.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, 17 August 2011 11:27 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2007 - The story so far.

And the real answer is (probably) -

Access modules consist of what you see and what you don't see.  Kind of like
Word documents except that there is no "show codes" for access editor.  I
have seen exactly what you are discussing, and it has nothing to do with the
error handler per se, it can occur on any line of code.  I was tracing a
page fault one time, stepping through code until the offending line caused
the page fault. 
Perfectly valid code.  I ended up cutting the line out and pasting it back
in and the problem was gone.

So there is some "invisible stuff" going on with the editor.  cutting the
offending "whatever" out causes that offending stuff to be corrected and you
are back in business.

BTW this is not 2007 specific, I was getting this back in 2K.  I doubt it
has ever been fixed.  The dev team had too many pretty tool bars to work on
to fix real bugs.

John W. Colby
www.ColbyConsulting.com 

On 8/17/2011 12:16 AM, Darryl Collins wrote:
> Hehehehe, WTF indeed - I will keep that lill answer as my corporate 
> back up response in case someone get offended. I like it.
>
> Yeah, I was really surprised when that happened.  I mean, I really was 
> like "What??".  I don't think I have ever had code that runs to the 
> error handler (and I repeated the launch several times to check it 
> happened consistently) then run without ANY error once the "On Error Goto
x" were commented out.
> It has always just stopped at the offending line of code - And I mean 
> always!!
>
> My next thought was, hell, the error handler must be causing the 
> issue, but no - putting it back in had no impact at all. It is 
> behaving nicely now which is a good result I guess, but even so, really
rather weird...
>
> Probably just blah blah to some of you, but I needed to tell someone 
> and the wife does care that much about the strangeness of code
performance.
>
> Cheers
> Darryl.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William 
> Benson
> (VBACreations.Com)
> Sent: Wednesday, 17 August 2011 12:41 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Access 2007 - The story so far.
>
> WTF = "Why'd That Fail?" ... we all know our acronyms.
>
>
> Could this be vindication of my strategy of using no error handling?
>
>
> (Apologies to Emilia and others who have tried to reform me for such a 
> paltry joke).
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl 
> Collins
> Sent: Tuesday, August 16, 2011 10:28 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Access 2007 - The story so far.
>
> Well I have been trying to find something nice to say about A2007, and 
> I guess one thing is in form design view when you have all the 
> controls grouped and you expand or contract one of them, the rest move 
> along in sync, that is nice and can save a fair bit of time tweaking the
form layout etc.
>
> Sadly, it doesn't make up for the rest of the bugs and nonsense I have 
> come across, but hey, it is something at least :)
>
> Latest weirdness was I had a file whose ADO connection string to the 
> back-end was failing.  No idea why as it was working great on other 
> PC's, so I took the error handlers off the all the code so I could see 
> exactly which line was failing. Ran the code and it worked perfectly, 
> WTF??  I mean, I didn't put in an "On error resume next" I took out 
> ALL the error handling functions so it would go splat at the first 
> problem, but instead it work flawlessly first time.
>
> So I put back in the error handlers to see what might happen - maybe 
> they were causing the error somehow (although it was compiling just 
> fine), and it worked flawlessly again.
>
> Go figure. I fixed the problem, but no idea what happened or why that 
> would fix the issue....
>
> Many instances of this sort of weirdness.  If you are just mashing  
> data
> A2007 works mostly ok, but to develop an app in A2003 is far more  
> stable and easy to use.
>
> Approach with some caution if you can.
>
> Just me thoughts
> Cheers
> Darryl.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list