Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Trouble shooting SQL Manager 2008

    OK here we go. After installing the SQL Server on my XP Pro pc I was able to go in and create a db. OK great. Then I try to connect to it from MS Excel 97. No luck.

    So I'm trying to figure out where I am going wrong. I have looked around for a tutorial that will explain but I'm not having much luck.

    I only have one account on this pc which I did add a password to after installing SQL Server. I don't think I need to have a seperate admin account but could be wrong ??

    What services should be running ???

    In my Excel, I did go in and turn on the Active x 2.8.

    I can't think of anything else to check or ask.

    So what do you think? Where should I be looking.

    Menessis

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Are you using SQL Express? I would check the connection string you are using. When using SQL Express the default Instance Name will be MACHINENAME\SQLEXPRESS.

    What error message are you getting.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Mar 2010
    Posts
    7

    Trouble shooting SQL Manager 2008

    Hey thanks for taking a shot at this. Yes it is SQL Express. The error for this one is " user defined type not defined". It's like the Excel 97 does't know what ADODB is. Here is one of the bits I tried.

    *************************************************
    Code:
    '
    'Excel 97 module
    '
    Sub TestThis()
        
        Dim MyDataBaseConnection As ADODB.Connection
        Set MyDataBaseConnection = New ADODB.Connection
        
        'MyDataBase.ConnectionString = "Provider=SQLOLEDB;Data Source=MYSQLSRVR\MYINSTANCE;Initial Catalog=MyDbName;Integrated Security=SSPI;"
        
        MyDataBaseConnection.ConnectionString = "Server=MyMachine\SQLEXPRESS ;Database=MyDataBase;Trusted_Connection=True;"
        MyDataBaseConnection.Open
        '
        'Should have some sort of code to test for connection
        '
          
        
        MyDataBaseConnection.Close
        Set MyDataBaseConnection = Nothing
        
    End Sub
    ************************************************** ****

    Thanks

    Menessis

  4. #4
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Have you referenced the ADODB object under Tools > References... in the VBA window?

    Failing that you can try and create the ADODB connection with late binding.

    Dim MyDataBaseConnection As Object
    Set MyDataBaseConnection = CreateObject("ADODB.Connection")

    Also check your connection string. for examples check the following link
    SQL Server 2000, 7.0 Connection String Samples - ConnectionStrings.com
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    OK now we are getting somewhere! No I don't have any reference to ADODB checked. And I can't seem to find it in the list.

    I tried your idea and now get this error:

    Run-time error '-2147 bla bla.......
    Data source name not found and no default driver specified"

    Does this mean I need to go into the XP "Data Sources(ODBC) and set up a "data source" and driver ???

    Thanks again.
    Menessis

  6. #6
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    The object to reference is Microsoft ActiveX Data Objects 2.8 Library

    If you use late binding you don't need to reference it. The error message is due to your connection string not being correct. You need to specify that you are using sqloledb so your connection string should look something like this:

    Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

    Hope that sorts it.
    Last edited by machado; 03-24-10 at 19:38.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  7. #7
    Join Date
    Mar 2010
    Posts
    7

    Success

    Well I think you did it! Thanks MachADO!

    I did have the ActiveX Data Objects 2.8 Library checked.
    I say I think it works. No errors. No output either LOL. So now I can start learning how to work with SQL.

    BTW there must be some sort of "if" statement to test if the connection is open right?

    If connection then
    MsgBox "The connection is open"
    End If

    OK so what did you mean by "If you use late binding" ??

    Thanks again.

    Menessis

  8. #8
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    This is early binding where you define the object explicitly

    Dim MyDataBaseConnection As ADODB.Connection
    Set MyDataBaseConnection = New ADODB.Connection

    This is late binding where the object is created at run time

    Dim MyDataBaseConnection As Object
    Set MyDataBaseConnection = CreateObject("ADODB.Connection")

    Check out this link for more info:
    Early vs. Late Binding

    connection.state indicates the status of a connection. The values are below:

    0 Indicates that the object is closed.
    1 Indicates that the object is open.
    2 Indicates that the object is connecting.
    4 Indicates that the object is executing a command.
    8 Indicates that the rows of the object are being retrieved.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  9. #9
    Join Date
    Mar 2010
    Posts
    7

    Cool

    OK I'm off to figure out how to get into my little database and display a message box with a name in it or something! LOL

    I use Excel97 to do all my books. ie make up my bills, keep track of then, a sheet with all the parts I use, a sheet with all the customer names and so on. So I think it's time to step up to a real data base.

    MachADO you have been a big help!

    Menessis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •