Method 'Connection' of object '_CurrentProject' failed

Method 'Connection' of object '_CurrentProject' failed on any form edit preceeded by "Automation Error The specified procedure could not be found." But im not automating anything!  I do have code that writes reads variables from the table i just edited in the "on close" code piece though which is the trigger for the error:

On Error GoTo ErrorHandler
 Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset
 Dim strSql As String
 Dim strCurrentProjectPath As String
 blnCurrentPathUsed = False 'keep track of wether attempts to use the current path were used and if they failed
 strCurrentProjectPath = CurrentProject.Path & "\"
 strSql = "SELECT * FROM tbllkupProgramOptions WHERE MachineName = """ & fOSMachineName & """"
 rs.Open strSql, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
 If rs.EOF = True Then ' No Options for this computer so Add Defaults


Open in new window

This is the problem i get in a 2010 project after several years of using this project, it started out as 2000 then 2003 then 2007 then 2010 and it quit working recently but ONLY on .accde project packages.  I can run the accdb with no issues at all.  I have read the first 3 pages of google on this an im actually at a loss.  I have created a new database, imported my forms and code over.  I have compacted, repaired.  I have removed all references and readded.  I have done everything i know how.  Now for the wierdest part, 100% of this problem i have researched happens upon code to do with recordsets usually dao vs the new access database engine reference.  Well my error happens on every form in which i try to write via ADO.  It happens on ACCDE projects on xp xp3 and win 7.

Currently my references are shown in the screenshot, I have also tried switching ado and adox 6.0 to 2.8 with no success either.  I have developed with access for over 10 years and I am actually tapping out. I repaired my dev machine, I uninstalled reinstalled my dev machine, I even installed a fresh copy of 2010 on another desktop and brought my files over and compiled a new accde and created a package.  That package when installed on the laptop thats giving me trouble has the same error!  I have also opened mssaccess with the /decompile option on my code.  I can't seem to find the common denominator  I literally have done everything i know how to.  The database i use is in a backend / frontend format and uses dao code to connect the underlying linked tables (but its worked for a long time, and I thought DAO was depreciated with the new database engine object and all code still worked) Please help experts! Current references
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The fact that accdb is OK and accde is not suggest to me that your problem is with references.
Such problems can arise if you are using on the run-time machine a different version of Access or a different version if Windows.  Either of these can lead to differences in paths between the development and runtime machines.

If have full a full copy of Access on the problem machine I suggest you try creating the accde on that machine and see if you still get the problem.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You may be bitten by the new ADO SP1 bug, which affects all applications running on that platform that use ADO:

I'd also caution you against using ActiveX controls that are NOT verified to work in the newer Access environments. They can cause odd failures as well. If you must use these controls, you might consider moving to a different platform.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShinZanAuthor Commented:
I will say this I definately agree that it is some sort of reference error with ADO and Ill tell you why:

I have another large project that is an .adp which has very similar symptoms I compile an .accde and it won't run properly giving me very odd errors when i declare ado recordsets.  This project came from 2000 format to 2010 access.

@Peter When i compile the ade of my .adp on other machines it works on all other target machines on that network.  My Dev machine is Win7/Office Pro Plus 2010 and the target machines are actually win7 office pro plus 2010 as well.  I can run the full version .accdb on the 50 target machines from my Dev machines (and thank God we bought the full pro plus 2010 versions)  
This is ok for my .adp projects in the corporate environment that i control, however this is not an acceptable solution for this project, I must distribute it in a runtime format.  On the 2nd Dev Machine (a desktop with Win7 (no sp1) pro plus office the .accde runs fine.  Also on my .adp project if i compile an .accde on the one of those target machines and distribute it to the 50 other target machines it runs fine (thats what ive been doing actually) It just stinks that i have to to another office to compile changes and then redistribute from there.


What an Illuminating article and I am certain this is my problem!!  I never found this in google :(  This is a huge MS cluster#$@!!  I am going to work around by uninstalling Win7SP1 and compiling my project on Win7 without the service pack and from the book i just read on that post that should fix my problem until Microsoft gets a real solution together, How can they say ADO is an old technolgy that should be "left to die" as on person puts it.  What the heck do they expect you to use?  I mean i didn't start coding yesterday, I've been with VBA and Access since 2000 (the year not the version!)  First I upgraded my "antiquated" DAO code to ADO and so are they now saying that MDAC is antiquated again and if so what replaces it?!!?!??

I will update if removing sp1 works (I'm optimistic)!!
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

DAO is current. As far as i know, it is the only library being updated to deal with the accdb formats.

In the Access/Jet world, ADO never replaced DAO.  I think MS thought it would replace dao when it launched ado but there were always shortcomings in dealing with Access files, even though it is necessary to use ADO for some sql commands.

ShinZanAuthor Commented:
@Peter, I definatley drank the cool aid from MS at the time because i distinctly remember all the articles about how to migrate dao code to ado which i did.  Now I'm over 75000 lines of code in on this project.  

@LSMConsulting I uninstalled sp1 from the command prompt wusa.exe /uninstall /kb:976932 but when i came back into windows i recompiled a new .accde and id DID NOT RUN on the xpsp3 machine or the other windows7 machine with full access 2010 pro plus.

Man I'm up the creak right now...Is there some way to make a manual reference to an older version of mdac 2.8 or soemthing like that?
ShinZanAuthor Commented:
@LMSConsulting:  It seems that the removal of SP1 was successful i forgot that i had included newer versions of msado15.dll and msadox.dll with my package.  I reverted those files to pre sp1 versions and then my origional Dev Machine with sp1 removed created an installable package that ran on my xpsp3,  When i get into the office I will be excited to see if this resolves my .adp issue as well which im sure it will.

Thanks for the help, Even though i had to do more research your link to the ado sp1 bug was the ticket, i need to start reading more of those access blogs as it seems this warning was out there before i even installed sp1.  Dang it microsoft!
ShinZanAuthor Commented:
It pointed me to the problem source and from there i was able to make the leap to correction
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.