ADO Data Control Missing / VBA Sub cannot connect to database / User defined type not defined

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.

RW

OCM123Asked:
Who is Participating?
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.

 
Wayne Taylor (webtubbs)Commented:
You need to add a reference (Tools > References) to "Microsoft AxtiveX DataObjects".

Wayne
0
 
OCM123Author Commented:
Thanks for responding Wayne. Having done some learning, I now understand that adding a reference involves using the Solution Explorer (right click, Add Reference). However, there is one last piece to the puzzle.

I am looking at the Visual C# Express Edition screen. On the left I have Database1.mdf where I have my data. Under Database1.mdf I have the usual folders (Database Diagrams,Tables, Stored Procedures,Functions,Synonyms,Types, Assemblies). In Stored Procedures I have my procedure called spValue. This is the Stored procedure I want to "run" from Excel, using my excel  macro.

 My Database1.mdf Assemblies folder is empty (I have not created any, I don't yet know what they do or if I need one).

I have opened the Solution Explorer on the right side of the Visual C# window. Following the instructions here http://support.microsoft.com/kb/315974 to "Use COM components from Visual Studio .NET" I created ComDemo (which I now cannot delete !) and then saw how to Add a Reference.

What I don't know is :

1. How to Add a Reference in the case of my project
2. Delete the ComDemo I created (!)
3. I need to know more about Assemblies - which I am happy to read up on - but based on what i am trying to do, do you think I need one/some assemblies ?

Many thanks again

RW

0
 
Wayne Taylor (webtubbs)Commented:
Sorry, I really don't understand the problem.

When you said "use a Function I have made in Excel", do you mean in VBA?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OCM123Author Commented:
Hi, thanks for responding.

yes, I mean in VBA. So the first part of the Sub will connect to the SQL Server. With the code (or similar code) to the code I attached in the first post.

When I run the code to connect to SQL Server 2005, i get the "user defined type not defined" error.

And although I want to follow your instructions to Add  a Reference, I can't see how to add a reference to my project. As I don;t have any Assemblies, there's nothing in the Solution Explorer to right click on.

Sorry if I didn't make that clear.

Thanks, RW

0
 
OCM123Author Commented:
Sub ConnectionExample6()
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Initial Catalog=Database1;Data Source=.\SQLEXPRESS"
   cnn.Open

i ended up with this and I managed to get the Add Reference done in VBA.

So thanks to you for your help - I will gladly add the 500 points ! However I think it will end this string of messages (so i will definitely do it at the end).

I'm still getting an error and not connecting to the Database1.

I've also tried this connection string

 cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Database1;Data Source=.\SQLEXPRESS"

and both give me "Cannot open the database "Database1" requested by the login. The login failed"

any final views, now that you see where I am, are much appreciated !

Thanks again, RW

0
 
OCM123Author Commented:
In the end, I've got these 4 possible connectionstrings, all with the same result : "Cannot open the database "Database1" requested by the login. The login failed"

So it must be something to do with the login :)



Sub ConnectionExample6()
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.

'   cnn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Initial Catalog=Database1;Data Source=.\SQLEXPRESS"

'   cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Database1;Data Source=.\SQLEXPRESS"

'   cnn.ConnectionString = "Driver=SQL Native Client;SERVER=.\SQLEXPRESS;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=.\sqlexpress;DATABASE=Database1"
   
'   cnn.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;"
   
    cnn.Open

Lost now...now ideas. Any help much appreciated !

Thanks, RW
0
 
Wayne Taylor (webtubbs)Commented:
Have you tried specifying the username and password in the connection string?

    Data Source=.\SQLEXPRESS;Initial Catalog=Database1;User Id=myUsername;Password=myPassword;
0

Experts Exchange Solution brought to you by ConnectWise

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
 
Anthony PerkinsCommented:
>>I looked at my list of components (Ctrl + T) in the project and found the ADO Data Control component is not there. <<
You are using Excel VBA therefore there is no ADO Data Control supported (only VB6)

>>Looking this up I find that it is part of .Net Framework 3.5, which I have just installed again. <<
Nope again.  As pointed out previously be webtubbs you need a reference to ADO (not the Data Control) and it is part of MDAC not the .NET Framework.

>>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 ?<<
The file is MSADODC.OCX, but again that is the Data Control and will not help you in Excel VBA.
0
 
OCM123Author Commented:
Hi Wayne, thanks again for your suggestion. For the Server I used Windows Authentication, so no need for a username or password i believe. The for the database, I do not believe I have a username or password either. If I open Visual C# or Management Studio applications, and connect to Database1, neither of them ask me for a login username or password. I can run queries and look at the date without logging into Database1.

Is there another level of username & password that I might be able to use / be required to enter ?

Thanks, RW

0
 
OCM123Author Commented:
@ acperkins

many thanks. I was not clear on Adding a Reference, but I've got those boxes checked now. It's all come down to this Login question. Once again I am stumped. My response to Wayne above sets out the issue.

If you have any thoughts they are much appreciated.

regards, RW
0
 
OCM123Author Commented:
Sorry for the delay Wayne, much appreciated your advice. Did some re-installations, and lined up every possible combo of connection strings, and in the end, it work nicely. You showed the way - many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.