Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-03-29
11
Medium Priority
?
1,068 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
11 Comments
 
LVL 48

Expert Comment

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

Wayne
0
 

Author Comment

by:OCM123
ID: 24018237
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 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24018278
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:OCM123
ID: 24020373
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
ID: 24023428
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
 

Author Comment

by:OCM123
ID: 24024173
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 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 24024863
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
ID: 24025460
>>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
ID: 24025495
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
ID: 24025511
@ 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
ID: 31564180
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

636 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