freestylpola
07-15-2014, 01:24 PM
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.
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.Close
Set rsDatabase_Name = Nothing
Set cnDatabase_Name = Nothing
End Sub
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.
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.Close
Set rsDatabase_Name = Nothing
Set cnDatabase_Name = Nothing
End Sub