Thread: Connect to MS SQL 2005 Database

Heya folks, I am trying now for hours, but with no satisfying result.

I have a MS SQL 05 DB running on a remote server with the IP Address A.B.C.D and i have also set up a test database with a table.

Now I want Excel to connect to this tatabase, but it always gives me following errormsg: [DBNETLIB][ConnectionOpen (Connect().]SQL Server does not exist or Access Denied

So I don't even know if I can connect to the DB or if just the Login Information is wrong... Does maybe some1 has experience with connecting to a remote SQL Server?

Cheers,
prozero

It could be a permission or something simplier like an error in your connection string. Without seeing your connection string, it's hard to troubleshoot. The below site has a huge assortment of connection strings. I'd start looking at that first.

www.connectionstrings.com

I already have been to that site but it did not really helped me. Is it possible to see the connection string somehow? Because i just enter the data into the Wizard...

I'm not sure what/where the wizard is that you're talking about. You mentioned Excel in your post, even through this is an Access forum, which is furthering my confusion. If you need to connect to a SQL server using VBA (regardless if it's Excel or Access) and get/manipulate data, you could do it the following way:

Code:

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String

    'Note: You will need a reference to "Microsoft ActiveX Data Objects Library" for this to work

    'The connection to the server
    Set cn = New ADODB.Connection

    With cn
        'Using Integrated Security (Windows Logon)
        .ConnectionString = "Provider=SQLOLEDB;Data Source=myServer\myInstance;Initial Catalog=myDB;Integrated Security=SSPI"
        'OR
        'Using User Name/Password
        .ConnectionString = "Provider=SQLOLEDB;Data Source=myServer\myInstance;Initial Catalog=myDB;User ID=SomeUser;Password=SomePass;"
        .Open
    End With

    'Getting data from a table
    Set rs = New ADODB.Recordset
    strSQL = "SELECT * FROM mytable"
    rs.Open strSQL, cn, adOpenStatic, adLockPessimistic

    If rs.BOF = True And rs.EOF = True Then
        'Indicates NO records
    Else
        'Indicates Records - do something here
    End If

    'Clean up
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

If you are talking about a linked table in Access, you'd need to set up a DSN (which would be your connection string) that you would then reference while linking a table. Once linked, if you hover over the table it will display the saved connection string or you could use the linked table manager (invoke by right clicking the table).

I accidentally choose the wrong subforum, my apologize. It is working now and thanks for the Script, I think it will be helpful at some point =)