Facing 'run-time error -2147217871 (80040e31) Automation error' in excel VBA

Hi guys,

I am working on Excel 2007 macros. My requirement is to access MS SQL server DB from excel, using VBA. I am just trying to pull some data by connecting to DB, but facing this issue: 'Facing 'run-time error -2147217871 (80040e31) Automation error' in excel VBA'

Below is the chunk of code I am using:

Dim objMyConn
Set objMyConn = New ADODB.Connection
Dim objMyRecordset As New Recordset
Set objMyRecordset = New ADODB.Recordset
Dim strConnectionString As String
Dim strSQL As String

strConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={abcd};Data Source={abcd};Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID={abcd};Use Encryption for Data=False;Tag with column collation when possible=False;ConnectionTimeout = 300;CommandTimeout = 0;"

strConnectionString = "Driver=SQL Server;Server={abcd};UID=;PASSWORD=;Database={abcd};Trusted_Connection=Yes;"

strSQL = "select top 10 * from {table name} where server = '{columnName}' order by 1 desc"

objMyConn.Open strConnectionString

objMyRecordset.Open strSQL, objMyConn

ActiveSheet.Range("A4").CopyFromRecordset objMyRecordset

For reference, I have pasted here both the connection strings which I tried. With both I am facing the same issue.

Any help is appreciated. Thanks.

Moderator Note:

Pls use code tags around your code next time as per forum rules.

Where are you getting the error?

I am getting this error when I run the macro.