VBA to connect to SQL Server

[VidyaKruthi]

Hi All Good Evening,

Please help me out how to connect to SQL Server and get data using VBA.

 

[Sektor]

Tools -> References -> Microsoft ActiveX Data Objects 2.8 Library

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

 

[VidyaKruthi]

Hi Thank you so much,


Very Quick reply..It really saved my time Thank you.

 

[DJB99]

Tools -> References -> Microsoft ActiveX Data Objects 2.8 Library

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

It has taken me ages to find a straightforward example like this to work from.

Thanks very much for helping me clear this hurdle.

 

[JochenSUI]

Hello
This looks exactly like what I need.

I'm very new to all these database stuff and many things which are logical to you are difficult to understand for me.

But hopefully you can help me.

1.
Is the Provider allways the same?

2.
How can I find out my Data Source?

3.
How can I find out my Initial Catalog?

4.
Is there no User Name and Password necessary?


Thanks for your help!!!

 

[bennymc]

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

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!

 

[Sleeking]

Thanks Sektor (and BennyMC) for such helpful contributions, great stuff.

 

[Galley]

Sektor is my new hero! I have found numerous, convoluted methods to accomplish this task, but this one is simple and it WORKS!

 

[SvetB]

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

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

 

[Derangeddm]

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

FOLDER is only important if you have a named instance of SQL Server other than the default on your server. For example, if your local computer name is COMP1, the default instance of SQL Server that installs would be COMP1, and you would not use anything else.
For your setup, it sounds like all you need to use is the server name.

On the other hand, if you have a named instance (for example, multiple instances of SQL Server on one PC), the FOLDER might be an instance name such as COMP1\SERVER2.