VBA code cannot connect to SQL Server 2008

问题:

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

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

:)