vba - sql string

[Mallesh23]

Hi Team,

I am using below code for extracting SQL Data into into excel and it worked.


Below is connection string I created using recording(extract data from external source)
Any alternate way and where we can shorten the connection string.


Const conSTRSQL As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DESKTOP-VJ0FV5Q\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DESKTOP-VJ0FV5Q;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master"

Option Explicit

Const conSTRSQL As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DESKTOP-VJ0FV5Q\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DESKTOP-VJ0FV5Q;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master"

Sub Extract_SQL_Data()

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset


cn.ConnectionString = conSTRSQL

cn.Open



With rst
    .ActiveConnection = cn
    .Source = "Persons"
    .LockType = adLockReadOnly
    .CursorType = adOpenForwardOnly
    .Open
End With


Worksheets.Add
Range("A2").CopyFromRecordset rst


cn.Close

End Sub