Dostęp do bazy danych SQL w Excelu-VBA

Kopiuję fragment kodu VBA z MSDN, który pokazuje mi, jak pobrać wyniki z zapytania SQL do arkusza 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

Dodałem już bibliotekę Microsoft ActiveX Data Objects 2.1 pod jako odniesienie. I ta baza danych jest dostępna.

Teraz, kiedy uruchamiam ten podprogram, ma błąd:

Run-time error 3704: operacja nie jest dozwolona, gdy obiekt jest zamknięty.

Na wypowiedzi: 

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

Dowolny pomysł dlaczego?

Dzięki.
Author: lfrandom, 2009-07-13
Source

6 answers

Dodałem Katalog początkowy do łańcucha połączeń. Porzuciłem też ADODB.Składnia poleceń na rzecz po prostu tworzenia własnego polecenia SQL i otworzyć zestaw rekordów na tej zmiennej.

Mam nadzieję, że to pomoże. 
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
 21
Author: David Walker,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-09-14 09:07:42

Sugerowane zmiany:

  • nie wywołuje metody Execute obiektu polecenia;
  • ustaw właściwość źródłową obiektu Recordset jako obiekt polecenia;
  • wywoła metodę otwartą obiektu Recordset bez parametrów;
  • Usuń nawiasy wokół obiektu Recordset w wywołaniu do CopyFromRecordset;
  • faktycznie zadeklaruj swoje zmienne:)

Poprawiony kod: 

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
 15
Author: onedaywhen,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-09-14 09:08:27

Siedzę przy komputerze bez żadnego odpowiedniego oprogramowania, ale z pamięci ten kod wygląda źle. Wykonujesz polecenie, ale odrzucasz RecordSet, Które objMyCommand.Execute zwraca.

Zrobiłbym: 

Set objMyRecordset = objMyCommand.Execute

...a następnie stracić część "open recordset".

 1
Author: Gary McGill,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-13 21:25:35

Czy to prawidłowy łańcuch połączeń?
Gdzie znajduje się instancja SQL Server?

Będziesz musiał sprawdzić, czy jesteś w stanie połączyć się z SQL Server za pomocą łańcucha połączenia, określonego powyżej.

EDIT: spójrz na Właściwość State zestawu rekordów, aby zobaczyć, czy jest otwarty?
Przed otwarciem zestawu rekordów należy zmienić właściwość CursorLocation na adUseClient.

 0
Author: shahkalpesh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-13 16:54:14

Dodaj set nocount on do początku przechowywanego proc (jeśli korzystasz z serwera SQL). Właśnie rozwiązałem ten problem we własnej pracy i był on spowodowany przez pośrednie wyniki, takie jak "1203 Rows Affected", ładowanie do Recordset, którego próbowałem użyć.

 0
Author: ecounysis,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-09 23:03:16

@firedrawndagger: aby wyświetlić listę nazw pól / nagłówków kolumn, należy przejść przez kolekcję pól recordset i wpisać nazwę: 

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
 0
Author: Jock,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-11 07:38:33