VBA code cannot connect to SQL Server 2008

Question

My code cannot connect to the database within Microsoft SQL Server 2008. It tries to but then errors out with the following error: "run time error SQL Server does not exist or access denied. The Server is password protected but I think I accounted for that. Any help would be appreciated! Where the error occurs is bolded

Public Sub dataextract()

    'Create a connection object.'
     Dim cndatabase_name As ADODB.Connection
     Set cndatabase_name = New ADODB.Connection

    'Provide the connection string.'
     Dim strConn As String

    'Use the SQL Server OLE DB Provider.'
     strConn = "PROVIDER=SQLOLEDB;"

     'Connect to the  database on the  server.'
     strConn = strConn & "DATA SOURCE=Server_name;INITIAL CATALOG=database_name;"

     ' Login details.'
      strConn = strConn & " UID=username; PWD=password"

    'Now open the connection.'
    'ERROR HERE:'
     cndatabase_name.Open strConn***




    ' Create a recordset object.'
    Dim rsdatabase_name As ADODB.Recordset
    Set rsdatabase_name = New ADODB.Recordset

    With rsdatabase_name
        ' Assign the Connection object.'
        .ActiveConnection = cndatabase_name
        ' Extract the required records.'
        .Open "SELECT * FROM table_name"
        ' Copy the records into cell A1 on Sheet1.'
        Sheet1.Range("A1").CopyFromRecordset rsdatabase_name

        ' Tidy up'
        .Close
    End With

    cndatabase_name
    Set rsdatabase_name = Nothing
    Set cndatabase_name = Nothing

End Sub

Answer 1

Try changing

' Create a recordset object.'
Dim rsdatabase_name As ADODB.Recordset
Set rsdatabase_name = New ADODB.Recordset

With rsdatabase_name
    ' Assign the Connection object.'
    .ActiveConnection = cndatabase_name
    ' Extract the required records.'
    .Open "SELECT * FROM table_name"
    ' Copy the records into cell A1 on Sheet1.'
    Sheet1.Range("A1").CopyFromRecordset rsdatabase_name

    ' Tidy up'
    .Close
End With

cndatabase_name
Set rsdatabase_name = Nothing
Set cndatabase_name = Nothing

to

' Create a recordset object.'
Dim rsdatabase_name As ADODB.Recordset
Set rsdatabase_name = New ADODB.Recordset

With rsdatabase_name
    ' Assign the Connection object.'
    .ActiveConnection = cndatabase_name
    ' Extract the required records.'
    .Open "SELECT * FROM table_name"
    ' Tidy up'
    .Close
End With

Sheet1.Range("A1").CopyFromRecordset rsdatabase_name

Set rsdatabase_name = Nothing
Set cndatabase_name = Nothing

You have a random "cndatabase_name" sitting in your code.

:)