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