{"id":12096,"date":"2011-11-25T14:53:00","date_gmt":"2011-11-25T14:53:00","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=812"},"modified":"2022-08-06T12:11:55","modified_gmt":"2022-08-06T12:11:55","slug":"sql-connection-string","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/11\/sql-connection-string\/","title":{"rendered":"SQL Connection using Excel Macros"},"content":{"rendered":"

Dear Friends,<\/p>\n

Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I am going to teach you, how you can do a connection with a SQL Server.
\nSQL databases has mainly two type of authentication :
\n1.<\/strong> Windows Authentication <\/em>
\n2.<\/strong> SQL Server authentication.<\/em>
\nHere in this article, I will share VBA code for both type of connection – Windows authentication and SQL Server Authentication.
\nBefore you start running the below code, make sure that reference for ADODB Connection is added in your Excel Workbook.
\nIf you do not know how to add references to your excel workbook, refer the below steps:<\/p>\n

How to add ADODB Connection references in Excel<\/h2>\n
\n

1. Go to VB Editor Screen (Alt+F11)<\/span>
\n 2. Tools –> References…<\/strong><\/span><\/span>
\n 3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library<\/span>” <\/strong> You can select 2.0 Version or any other higher version of this reference.<\/span>
\n 4. Click OK<\/span><\/p>\n<\/div>\n

SQL Connection String – Using SQL Server Authentication<\/a>
\nSQL Connection String – Using Windows Authentication<\/a><\/p>\n

<\/a><\/p>\n

Using SQL Server Authentication: <\/h2>\n
\r\n\r\n\r\n    Sub SQL_Connection()  \r\n      \r\n    Dim con As ADODB.Connection  \r\n    Dim rs As ADODB.Recordset  \r\n    Dim query As String  \r\n    Set con = New ADODB.Connection  \r\n    Set rs = New ADODB.Recordset  \r\n      \r\n    '---- Replace below highlighted names with the corresponding values  \r\n      \r\n    strCon = \"Provider=SQLOLEDB; \" & _\r\n            \"Data Source=\"your ServerName\"; \" & _\r\n            \"Initial Catalog=database name;\" & _\r\n            \"User ID=uid; Password=pwd; Trusted_Connection=yes\"  \r\n      \r\n    '---  Open   the above connection string.  \r\n      \r\n    con.Open (strCon)  \r\n      \r\n    '---  Now connection is open and you can use queries to execute them.\r\n    '--- It will be open till you close the connection  \r\n      \r\n    End Sub  \r\n<\/code>\r\n<\/code><\/pre>\n
<\/a><\/p>\n
Using Windows Authentication: <\/h2>\n
\r\n\r\n\r\n    Sub SQL_Connection()  \r\n      \r\n    Dim con As ADODB.Connection  \r\n    Dim rs As ADODB.Recordset  \r\n    Dim query As String  \r\n    Set con = New ADODB.Connection  \r\n    Set rs = New ADODB.Recordset  \r\n      \r\n    '---- Replace below highlighted names with the corresponding values  \r\n      \r\n    strCon = \"Provider=SQLOLEDB; \" & _\r\n            \"Data Source=\"your ServerName\"; \" & _\r\n            \"Initial Catalog=database name;\" & _\r\n            \"Integrated Security=SSPI\"   \r\n      \r\n    '---  Open   the above connection string.  \r\n      \r\n    con.Open (strCon)  \r\n      \r\n    '---  Now connection is open and you can use queries to execute them.\r\n    '---  It will be open till you close the connection  \r\n      \r\n    End Sub  \r\n<\/code>\r\n<\/code><\/pre>\n
Read this Also:<\/h2>\n