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

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

Sorry, I really don't understand the problem.

When you said "use a Function I have made in Excel", do you mean in VBA?

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

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

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

>>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.

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

@ 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