VidyaKruthi
New Member
- Joined
- Feb 17, 2012
- Messages
- 18
Hi All Good Evening,
Please help me out how to connect to SQL Server and get data using VBA.
Please help me out how to connect to SQL Server and get data using VBA.
Sub ConnectSqlServer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
"Initial Catalog=MyDatabaseName;" & _
"Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("SELECT * FROM Table1;")
' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(1).Range("A1").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
It has taken me ages to find a straightforward example like this to work from.Tools -> References -> Microsoft ActiveX Data Objects 2.8 Library
Code:Sub ConnectSqlServer() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sConnString As String ' Create the connection string. sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _ "Initial Catalog=MyDatabaseName;" & _ "Integrated Security=SSPI;" ' Create the Connection and Recordset objects. Set conn = New ADODB.Connection Set rs = New ADODB.Recordset ' Open the connection and execute. conn.Open sConnString Set rs = conn.Execute("SELECT * FROM Table1;") ' Check we have data. If Not rs.EOF Then ' Transfer result. Sheets(1).Range("A1").CopyFromRecordset rs ' Close the recordset rs.Close Else MsgBox "Error: No records returned.", vbCritical End If ' Clean up If CBool(conn.State And adStateOpen) Then conn.Close Set conn = Nothing Set rs = Nothing End Sub
For more info about connection strings: http://www.connectionstrings.com
sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
"Initial Catalog=MyDatabaseName;" & _
"Integrated Security=SSPI;"
I feel a quick Acknowledgement and thanks is in order
This thread helped me a lot, thank you for that Sektor
For those slightly baffled at first like I was, here is how it's broken down:
Remember to go Tools > References and tick Microsoft ActiveX Data Objects 2.8 Library (or higher)
In the part that says
Code:sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _ "Initial Catalog=MyDatabaseName;" & _ "Integrated Security=SSPI;"
Data Source is where your stuff is on whatever Server you're using. Ie, Data Source=SERVER1\FOLDER (Don't go rooting around in Program Files for the .exe file for SQL Server 2008 like I did *facepalm*)
Initial Catalogue is whatever your database is called. Just change "MyDatabaseName" to your own.
Sounds obvious I know but I'm totally new to this, as are many who view it!
Cheers!
Can anybody tell me how exactly you determine the replacement for SERVER1\FOLDER ? I mean SERVER1 is obviously the server name BUT what is FOLDER ? My db location is this: \Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA. What should I use instead of FOLDER ??? It seems this one is not that obvious...