I use SQLOLEDB a lot in my code, thinking I was bypassing some of the ODBC stack. Now it turns out we can use ODBC after all in our connection strings. Here is the post from Microsoft:
http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/29/microsoft-aligning-with-odbc.aspx
It’s probably going to be a while but I would start replacing SQLOLEDB with ODBC from now on in your code.
Office SP1 damages Access?
Ben has a practice of avoiding SP1s of any kind and here’s why:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2625046
Hold off on upgrading to SP1 until Microsoft has fixed this issue!
Dear Juan,
Regarding the Office 2010 sp1 Access crash I would like to thank you for posting this link. It is a long time since I’ m facing this problem in my access frontends to MSSQL dbs. A quick resolution for me was the following.
I opened up my Access application by pressing the Shift button, so I bypassed all startup forms and logins.
Opened up a module, not a particular one!
Went on Debug>Compile and voila…problematic old code started firing up error messages.
After I removed all the unnecessary parts of the code and changed with new refs/code the needed ones, I closed and reopened.
The database opened like a charm. I compacted and repair since this process made my db frontend went on 90MB and started working again with no problem.
But….(there is always a @!$!but)
Whenever I try to make a new form or module, and I close my DB and then reopen the crash comes back again 🙁
PS. I have 12 Modules and modifications pending that I need to make in my access frontend and I cannot…It is really frustrating. Please continue to keep us informed and congratulations on way of promoting Access since it is a life and really good money saving tool!!!
Thanks for your feedback, sorry to hear you’re having problems with this issue. I will post again when I’ve heard more.
Hi Juan,
Nice post. We’ve been dealing with Microsoft Access 2010 SP1 issues since they released it back in June. For the 64 bit version of Access, it created a backward compatibility problem for ACCDEs created in the original RTM version. I blogged about it back in July: Microsoft Access 2010 Service Pack 1 VBA Project Compatibility Issues.
With all our Access add-ins and libraries, we and our customers rarely get to control what our users have installed, so we need to support both. We have our development machines and then we have our build machines to create the final deliverables for our customers using the version of Access they are running. A real pain.
Keep up the good work!
Luke
Thanks Luke!
Hi Juan, apologies for bringing up an old topic,
With the demise of OLE DB does this also spell the demise of the Access Projects, most of my development over the past 8-9 years has been .adp’s front ending SQL server via ADO. I’m presuming we all go back to linked tables and DAO ?
Thanks in advance for your views, Gary
Hi Gary,
ADPs unfortunately are on their way out and linked tables are the way to go. You can still use ADODB and in fact it’s still the most efficient way to connect with SQL Server. The only difference is the connection string will be ODBC based instead of SQLOLEDB, which is being phased out.
Kind Regards,
Juan
So does this have any bearing on the code you published recently under the title: “Easy ADODB Recordsets and Commands in Access”?
Does anything there have to be changed to ODBC?
Thanks
Not at all! SQLOLEDB is still valid and works on both Access 2007 and 2010. I would however urge you to start switching your code over to ODBC provider sooner rather then later.
Hope that helps!
Juan