parametros - macros excel sql server

Acceso a la base de datos SQL en Excel-VBA

Question

Estoy copiando un fragmento de código de VBA de MSDN que me muestra cómo tomar los resultados de una consulta SQL en una hoja de 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

Ya he agregado la Biblioteca Microsoft ActiveX Data Objects 2.1 como referencia. Y esta base de datos es accesible.

Ahora, cuando ejecuto esta subrutina, tiene un error:

Error de tiempo de ejecución 3704: no se permite el funcionamiento cuando el objeto está cerrado.

En la declaración:

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

¿Alguna idea de por qué?

Gracias.

Answer 1

¿Es esa una cadena de conexión adecuada?
¿Dónde se encuentra la instancia de SQL Server?

Deberá verificar que puede conectarse a SQL Server utilizando la cadena de conexión que especificó anteriormente.

EDITAR: Mire la propiedad del estado del conjunto de registros para ver si está abierto.
Además, cambie la propiedad CursorLocation a adUseClient antes de abrir el conjunto de registros.

Answer 2

@firedrawndagger: para enumerar los nombres de campo / encabezados de columna iterar a través de la colección de Campos de conjunto de registros e insertar el nombre:

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

Answer 3

Agregue set nocount on el comienzo del proceso almacenado (si está en SQL Server). Acabo de resolver este problema en mi propio trabajo y fue causado por resultados intermedios, como "1203 Rows Affected" , que se cargaron en el Recordset que estaba tratando de usar.

Answer 4

Cambios sugeridos:

• No invoque el método Execute del objeto Comando;
• Establezca la propiedad Origen del objeto Recordset para que sea su objeto Command;
• Invoque el método Open del objeto Recordset sin parámetros;
• Quite los paréntesis de todo el objeto Recordset en la llamada a CopyFromRecordset ;
• En realidad, declara tus variables :)

Código revisado:

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 5

Estoy sentado frente a una computadora sin ninguno de los bits de software pertinentes, pero de memoria ese código parece estar mal. Estás ejecutando el comando pero descartando el RecordSet que devuelve objMyCommand.Execute .

Lo haría:

Set objMyRecordset = objMyCommand.Execute

... y luego pierde la parte "conjunto de registros abierto".

Answer 6

He agregado el Catálogo Inicial a su cadena de conexión. También abandoné la sintaxis ADODB.Command a favor de simplemente crear mi propia declaración SQL y abrir el conjunto de registros en esa variable.

Espero que esto ayude.

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