Database Help

**Menessis** · 03-07-2010, 06:22 PM

OK I'm trying to learn some DB as it applies to Excel. But I'm having trouble connecting all the dots. I tested my "select" string in the SQLServerManager and that works so I cut & paste that for my string.

So I think the problem is in opening the db. I get a "data-source name not found". I'm using VB module in Excel 97. I have the active x 2.8 selected.

Code:

Sub RecordsetOpenSELECT()

    Dim rs As Recordset
    Dim strSELECT As String
    '
    'Create the Recordset object
    '
    Set rs = CreateObject("ADODB.Recordset")
    '
    ' Open it
    '
    strSELECT = " select * from  dbo.Individual1 where LastName = 'auld';"
    'select * from  dbo.Individual1 where LastName = 'auld'
    rs.Open strSELECT, "MyDataBase", adOpenKeyset
    '
    'Close it
    '
    rs.Close
    Set rs = Nothing
  
End Sub

So any help would be great!
Thanks

Menessis

**bhill** · 03-19-2010, 01:34 PM

You need to define "MyDataBase". This is the switch where your connection string would go. Once defined, you'll need to open it too. Something like:

Dim MyDataBase as New ADODB Connection
Set MyDataBase = New ADODB.Connection
MyDataBase.ConnectionString = "Provider=SQLOLEDB;Data Source=MYSQLSRVR\MYINSTANCE;Initial Catalog=MyDbName;Integrated Security=SSPI;"
MyDataBase.open

Your connection string will be different than mine. Try this site for help on connecting to many different types of data sources. You should be able to find one that will work for you.
http://www.connectionstrings.com/

**royUK** · 03-19-2010, 03:52 PM

MenessisYour post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**royUK** · 03-19-2010, 03:52 PM

bhillYour post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Menessis** · 03-19-2010, 08:38 PM

Hey thanks for trying to help me BHill! I have done a lot of reading about this since posting my original question.

So I played around with this for a minute

Code:

Sub TestThisString()
    
    Dim MyDataBase As New ADODB.Connection
    Set MyDataBase = New ADODB.Connection
    'MyDataBase.ConnectionString = "Provider=SQLOLEDB;Data Source=MYSQLSRVR\MYINSTANCE;Initial Catalog=MyDbName;Integrated Security=SSPI;"
    
    'MyDataBase.ConnectionString = "Data Source=MyMachine\SQLEXPRESS ;Initial Catalog=MyDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;"
    MyDataBase.ConnectionString = "Server=MyMachine\SQLEXPRESS ;Database=MyDataBase;Trusted_Connection=True;"
    MyDataBase.Open
    
    MyDataBase.Close
    Set MyDataBase = Nothing
    
End Sub

I get an error that states: Compile error: User-defined type not definded with the line "Dim MyDataBase As New ADODB.Connection".

I don't get it

Menessis