Solved

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

Posted on 2009-03-29
11
1,021 Views
Last Modified: 2013-12-26
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

0
Comment
Question by:OCM123
  • 7
  • 3
11 Comments
 
LVL 47

Expert Comment

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

Wayne
0
 

Author Comment

by:OCM123
Comment Utility
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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
 

Author Comment

by:OCM123
Comment Utility
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
 

Author Comment

by:OCM123
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:OCM123
Comment Utility
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
Comment Utility
Have you tried specifying the username and password in the connection string?

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

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 

Author Comment

by:OCM123
Comment Utility
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
 

Author Comment

by:OCM123
Comment Utility
@ 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
 

Author Closing Comment

by:OCM123
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now