We help IT Professionals succeed at work.

Error in VBA (Excel) while trying to connect to SQL server: Provider cannot be found. It may not be properly installed.

1,614 Views
Last Modified: 2008-09-19
I am trying to connect to SQL server using excel, but i am getting the following error:
Provider cannot be found. It may not be properly installed.  

Public Const gszCONNECTION As String = _
    "Provider=SQLOLEDB;Data Source=localhost;" & _
    "Initial Catalog=Northwind;Integrated Security=SSPI"

Public gobjCmd As ADODB.Command
Public gobjConn As ADODB.Connection

Public Sub CreateConnection()
    ' Create the Connection object.
    Set gobjConn = New ADODB.Connection
    gobjConn.Open gszCONNECTION
End Sub

It fails when it tries to call this line: gobjConn.Open gszCONNECTION

I have added Microsoft ADO 2.5 in the refrences section in the VBA editor.
I have also run the Microsft MDAC checked tool, and it tells me that the latest version is MDAC 2.8
I do'nt know what else i can do to fix this issue.
Please help.
Comment
Watch Question

Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
sqloledb.dll does not exist in this folder - \Program Files\Common Files\System\Ole DB
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Then you can't use the SQLOLEDB provider, because you do not have it. I think that if you download the latest MDAC from Microsoft, it should install the provider for you. (to be honest, I'm a little surprised you have SQL Server running locally without the SQLOLEDB dll)
Regards,
Rory
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:

Author

Commented:
I am running XP professional - and i get the following error when i try to install the MDAC 2.8

MDAC 2.8 RTM is incompatible with this version of Windows. All of its features are currently part of Windows.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you have SP2 installed? That should have installed the sqloledb driver. Did you install the client tools when you installed SQL Server on the machine?

Author

Commented:
Yes, i have SP2 installed, and i have the client tools installed as well.

I checked one article from http://support.microsoft.com/default.aspx?scid=kb;en-us;318251
which says to install from a alternate MDAC file, but that also failed, with
Fatal Setup Error:
The setup does not support installing on this operating system.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Did you try reinstalling from the Windows .cab files per that article?

Author

Commented:
i do'nt have the windows installer cd, will try that later..
This mdac has caused me a lot of trouble.
I think the file could be removed when i uninstalled some programs....
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you have the SQL Server CD, you might try reinstalling the client tools from there and see if that helps.
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Rory, I unistalled the SQL server 2000 from my computer and then installed the SQL server 2005 express edition, but i still could not see the sqloledb.dll file being copied.

Badotz, i tried to follow your steps, but i just cannot establish connection to the SQL server.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I think you will either have to reinstall the client tools from SQL2000 or reinstall from the windows XP cab files.
Top Expert 2007

Commented:
Then something is seriously wrong - if you find out what it is, please let us know, eh?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.