Доступ к базе данных SQL в Excel-VBA

Question

Я копирую фрагмент кода VBA из MSDN, который показывает, как получить результаты запроса SQL в таблицу Excel (Excel 2007):

Sub GetDataFromADO()

    'Declare variables'
        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
        objMyCmd.Execute

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open objMyCmd

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

Я уже добавил библиотеку Microsoft ActiveX Data Objects 2.1 в качестве ссылки. И эта база данных доступна.

Теперь, когда я запускаю эту подпрограмму, она имеет ошибку:

Ошибка выполнения 3704: операция не разрешена, когда объект закрыт.

На заявлении:

ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

Есть идеи почему?

Благодарю.

Answer 1

Я добавил начальный каталог в строку подключения. Я также отказался от синтаксиса ADODB.Command в пользу простого создания своего собственного оператора SQL и открытия набора записей для этой переменной.

Надеюсь это поможет.

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

Answer 2

Предлагаемые изменения:

• Не вызывайте метод Execute объекта Command;
• Установите свойство Source объекта Recordset в качестве объекта Command;
• Вызвать метод Open объекта Recordset без параметров;
• Удалите скобки вокруг объекта Recordset в вызове CopyFromRecordset;
• Собственно объявите свои переменные:)

Пересмотренный код:

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 objMyRecordset

End Sub

Answer 3

Это правильная строка подключения?
Где находится экземпляр SQL Server?

Вам необходимо убедиться, что вы можете подключиться к SQL Server, используя строку подключения, указанную выше.

РЕДАКТИРОВАТЬ: Посмотрите на свойство State набора записей, чтобы увидеть, если он открыт?
Кроме того, перед открытием набора записей измените свойство CursorLocation на adUseClient.

Answer 4

Я сижу за компьютером без какого-либо соответствующего программного обеспечения, но из памяти этот код выглядит неправильно. Вы выполняете команду, но отбрасываете RecordSet тот objMyCommand.Execute возвращается.

Я бы сделал:

Set objMyRecordset = objMyCommand.Execute

... а затем потерять часть "открытый набор записей".

Answer 5

Добавлять set nocount on в начало сохраненного процесса (если вы находитесь на SQL Server). Я просто решил эту проблему в моей собственной работе, и это было вызвано промежуточными результатами, такими как "1203 Rows Affected"загружается в Recordset Я пытался использовать.

Answer 6

@firedrawndagger: чтобы вывести список имен полей / заголовков столбцов, выполнить итерацию в коллекции полей набора записей и вставить имя:

Dim myRS as ADODB.Recordset
Dim fld as Field
Dim strFieldName as String 

For Each fld in myRS.Fields
    Activesheet.Selection = fld.Name
    [Some code that moves to next column]
Next