ADO Data Control Missing / VBA Sub cannot connect to database / User defined type not defined
Posted on 2009-03-29
Hello. I have just installed MS SQL 2005, the .NET Framework 3.5 with SP1, created "Database1" on server named OCM\SQLEXPRESS and copied the attached code below "Sub ConnectionExample4()" and saved it into my Excel file's Module1.
My objective here is to use a Function I have made in Excel to retrieve a single value into an Excel cell . As I understand it, the Function must first Connect my Excel to my SQL Server database called OCM\SQLEXPRESS.
When I run the SubConnectionExample4() I get the error "User defined type not defined" and Dim conn As ADODB.Connection is highlighted. Seems like a bunch of other folks have had the same issue. I looked at my list of components (Ctrl + T) in the project and found the ADO Data Control component is not there.
Looking this up I find that it is part of .Net Framework 3.5, which I have just installed again. The specific missing OSX file that drives the ADO appears to be MSADODC.OSX which I also downloaded, and copied/pasted into C:\Windows|System32 folder alongside all the other OSX files. Do I now have to "register" it somehow ?
If I run the Sub ConnectionExample4() again, I still have the same error.
So I am at a loss as to where / what I am doing wrong. I am running XP + Service Packs,and the SQL Server Express 2005, Server Management Studio Express 2005, Visual C# 2005 Studio Express and .Net Framework 3.5 + Service Pack have all been downloaded in the last few days.
A couple of hints / pointers I have seen are :
1. ADO Data Control / MSADODC.OSX has to be "registered" - but I am not familiar with how to do this
2. ADO Data Control may/may not be an "official" part of the Express versions of these products.
3. My server name cannot be OCM\SQLEXPRESS even though that is what the server name shows when I connect to the database (which works fine, and I can run my queries in C# Express and Server Management Studio with no problems)
Experts - please have a look at the above and hopefully help me and some others out there - many thanks.