PDA

View Full Version : VBA Code Cannot Access Database In SQL Server



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

westconn1
07-15-2014, 02:15 PM
strConn = strConn & "DATA SOURCE=Server_Name;INITIAL CATALOG=Database_Name;"


' Login details.
strConn = strConn & " UID=UserName; PWD=Password"
i assume you have valid values for all these items

check out connectionstings.com for sql server

Bob Phillips
07-15-2014, 02:43 PM
Shouldn't it be



' Login details.
strConn = strConn & " UID='" & UserName & "'; PWD='" Password & "'"

assuming you have values for UserName and Password as westconn1 said.

freestylpola
07-15-2014, 02:43 PM
I've finally established the connection to the server but now the line: " Sheet1.Range("A1").CopyFromRecordset rsDatabase_Name" is giving me an object required error.

westconn1
07-16-2014, 01:24 AM
is this code written within the excel VBA ide?
is the recordset valid and contains records?
is table_name correct for your table?

there is 2 objects on that line, first need to determine which one is not valid, sheet1 or rsdatabase_name
look in the locals window

Bob Phillips
07-16-2014, 03:24 AM
Can you post the full code then we could knock up a quick test.

Aflatoon
07-17-2014, 01:42 AM
Do you actually have a sheet with code name Sheet1?

Bob Phillips
07-17-2014, 11:48 AM
Do you actually have a sheet with code name Sheet1?

I wondered that, but thought, nah he has to, it defaults to that :)

Aflatoon
07-17-2014, 01:54 PM
I know but I couldn't see any other way to get that error on that line. ;)

westconn1
07-17-2014, 02:24 PM
i thought i covered that in post #5

sheet1 could have been deleted for some reason