Accessing SQL Database in Excel-VBA

I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

Sub GetDataFromADO()    'Declare variables'        Set objMyConn = New ADODB.Connection        Set objMyRecordset = New ADODB.Recordset        Dim strSQL As String    'Open Connection'        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"        objMyConn.Open    'Set and Excecute SQL Command'        strSQL = "select * from myTable"    'Open Recordset'        Set objMyRecordset.ActiveConnection = objMyConn        objMyRecordset.Open strSQL                'Copy Data to Excel'        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)End Sub

Suggested changes:

• Do not invoke the Command object's Execute method;
• Set the Recordset object's Source property to be your Command object;
• Invoke the Recordset object's Open method with no parameters;
• Remove the parentheses from around the Recordset object in the call to CopyFromRecordset;
• Actually declare your variables :)

Revised code:

Sub GetDataFromADO()    'Declare variables'        Dim objMyConn As ADODB.Connection        Dim objMyCmd As ADODB.Command        Dim objMyRecordset As ADODB.Recordset        Set objMyConn = New ADODB.Connection        Set objMyCmd = New ADODB.Command        Set objMyRecordset = New ADODB.Recordset    'Open Connection'        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"            objMyConn.Open    'Set and Excecute SQL Command'        Set objMyCmd.ActiveConnection = objMyConn        objMyCmd.CommandText = "select * from mytable"        objMyCmd.CommandType = adCmdText    'Open Recordset'        Set objMyRecordset.Source = objMyCmd        objMyRecordset.Open    'Copy Data to Excel'        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordsetEnd Sub

Is that a proper connection string?
Where is the SQL Server instance located?

You will need to verify that you are able to conenct to SQL Server using the connection string, you specified above.

EDIT: Look at the State property of the recordset to see if it is Open?
Also, change the CursorLocation property to adUseClient before opening the recordset.