1

Topic: EXCEL Function returning RecordSet

Hello world!
The code f-tsii which should return a dial-up is more low presented

Function F_Exec_SQL_ToRcdSet (ConString As String, CommandText As String) As ADODB.Recordset
' Procedure fulfills SQL the request which result returns as Recordset
' ConString - a line of connection
' CommandText - the request text
Dim Cmd As ADODB.Command
Dim Con As Connection
Set Con = New ADODB.Connection
Con. CommandTimeout = 1000000
Con. ConnectionString = ConString
Con. CursorLocation = adUseClient
Con. Open
Set Cmd = CreateObject ("ADODB.Command")
Cmd. CommandTimeout = 1000000
Cmd. CommandType = adCmdText
Cmd. ActiveConnection = Con
Set Exec_SQL_ToRcdSet = Con. Execute (CommandText)
End Function

By a call of this function it returns nothing.
I assume that at an output from F_Exec_SQL_ToRcdSet the cursor is closed.
Whether truly my assumption?
How it is possible to return a dial-up?

2

Re: EXCEL Function returning RecordSet

To output F_Exec_SQL_ToRcdSet
In a dial-up there are records

3

Re: EXCEL Function returning RecordSet

Crystal full-sphere on maintenance
1) That in ConString?
2) whether Is not present On Error Resume Next in the causing code?
3) whether It is used Option Explicit?

4

Re: EXCEL Function returning RecordSet

On good

Yagrus2 wrote:

>>> Set <<<Cmd. ActiveConnection = Con

but it hardly influences. Your assumption is incorrect - should work.

5

Re: EXCEL Function returning RecordSet

Shocker. Pro wrote:

1) That in ConString?

the Line of connection to the sql-server

Shocker. Pro wrote:

2) whether Is not present On Error Resume Next in the causing code?

Is not present

Shocker. Pro wrote:

3) whether it is used Option Explicit?

is not present
It is procedure from which I cause

Sub Calc_InSql_MapingOrgName_Put_ToExcel_Forum ()
' the Line of connection
Dim ConString As String
ConString = "Driver=SQL" AND "Server; Server=N01000039; Database=TD"
' the Text of command SQL of request
Dim CommandText As String
' the Array of parameters necessary for performance SQL of request. Of it it is formed CommandText
Dim MasParams () As String
ReDim MasParams (1 To 1000, 1 To 5)
CommandText = F_CommandText ("EXEC", "[dbo]. [SP_Report__Organisation_WithoutMaping]", MasParams ())
DoEvents
Dim RcdSet As ADODB.Recordset
Set RcdSet = F_Exec_SQL_ToRcdSet (ConString, CommandText)
CountCol = Val (RcdSet. Fields. Count)
CountRow = Val (RcdSet. RecordCount)
End Sub

Function F_CommandText returns the request text "EXEC [dbo]. [SP_Report__Organisation_WithoutMaping]"
On the sql-server the given request fulfills (I Look through )
At line CountCol (in exterior procedure) RcdSet - Notthing

6

Re: EXCEL Function returning RecordSet

Yagrus2 wrote:

it is passed...
There is no

It smile))
http://www.sql.ru/faq/faq_topic.aspx?fid=2539

7

Re: EXCEL Function returning RecordSet

Shocker. Pro;
Unfortunately switching-on of the given option did not help to find an error. More low the code of procedures

Option Explicit
Sub Calc_InSql_MapingOrgName_Put_ToExcel_Forum ()
' the Line of connection
Dim ConString As String
ConString = "Driver=SQL" AND "Server; Server=N01000039; Database=TD"
' the Text of command SQL of request
Dim CommandText As String
' the Array of parameters necessary for performance SQL of request. Of it it is formed CommandText
Dim MasParams () As String
ReDim MasParams (1 To 1000, 1 To 5)
CommandText = F_CommandText ("EXEC", "[dbo]. [SP_Report__Organisation_WithoutMaping]", MasParams ())
DoEvents
' the organizations on which is not present  in SQL - the table get To this cursor.
Dim RcdSet As ADODB.Recordset
Set RcdSet = F_Exec_SQL_ToRcdSet (ConString, CommandText)
Dim CountCol, CountRow As Integer
CountCol = Val (RcdSet. Fields. Count)
CountRow = Val (RcdSet. RecordCount)
End Sub
Function F_Exec_SQL_ToRcdSet (ConString As String, CommandText As String) As ADODB.Recordset
' Procedure fulfills SQL the request which result returns as Recordset
' ConString - a line of connection
' CommandText - the request text
Dim Cmd As ADODB.Command
Dim Con As Connection
Set Con = New ADODB.Connection
Con. CommandTimeout = 1000000
Con. ConnectionString = ConString
Con. CursorLocation = adUseClient
Con. Open
Set Cmd = CreateObject ("ADODB.Command")
Cmd. CommandTimeout = 1000000
Cmd. CommandType = adCmdText
Cmd. ActiveConnection = Con
Dim RcdSet As New ADODB.Recordset
Set RcdSet = Con. Execute (CommandText)
End Function
Function F_CommandText (TypeComand As String, SqlObject As String, MasParams () As String)
' F-tsija returning a SQL-command
' TypeComand - Type SQL of a command: INSERT; EXEC
' SqlObject - Object SQL which participates in SQL - request
' StrParams - Myssiv of parameters containing values of parameters  for SQL - request
Dim CommandText As String ' Text SQL of a command
Dim StrParams As String ' Skonkatenirovannaja a line of parameters
Dim x, y, i As Integer
x = UBound (MasParams, 1)
y = UBound (MasParams, 2)
If x = 1 Then
StrParams = "'"
For i = 1 To y
StrParams = StrParams AND MasParams (1, i)
If i <y Then
StrParams = StrParams AND "', '"
End If
Next
StrParams = StrParams AND "'"
ElseIf x = 1000 Then
StrParams = ""
End If
If TypeComand = "INSERT" Then
CommandText = "INSERT INTO" AND SqlObject AND "VALUES (" AND StrParams AND ")"
ElseIf TypeComand = "EXEC" Then
CommandText = "EXEC" AND SqlObject AND StrParams
End If
F_CommandText = CommandText
End Function

8

Re: EXCEL Function returning RecordSet

Shocker. Pro;
Made still such experiment.
In problem f-tsii F_Exec_SQL_ToRcdSet, added one more parameter of type ADODB.Recordset. I transfer it as the link from exterior procedure (Calc_InSql_MapingOrgName_Put_ToExcel_Forum).
I change its value of this parameter on Con. Execute (CommandText)
Can at an output from F_Exec_SQL_ToRcdSet all the same the cursor it is closed?

9

Re: EXCEL Function returning RecordSet

At you function F_Exec_SQL_ToRcdSet returns nothing (more precisely, returns Nothing) that you and watch in a debugger

10

Re: EXCEL Function returning RecordSet

http://office-guru.ru/excel/procedury-f … a-461.html

11

Re: EXCEL Function returning RecordSet

Shocker. Pro wrote:

At you function F_Exec_SQL_ToRcdSet returns nothing (more precisely, returns Nothing) that you and watch in a debugger

Thanks! Saw.
The problem is settled. Most likely, I  at a stage of returned value.
Thanks for the help!

12

Re: EXCEL Function returning RecordSet

Yagrus2 wrote:

I  at a stage of returned value.

against it just also insures Option Explicit - I recommend to use it permanently

13

Re: EXCEL Function returning RecordSet

Shocker. Pro;
Once again wanted to thank for your council with Option Explicit
Reconsidering once again a topic also saw that in function from the first message, Recordset it is put in not declared variable.
And it should be returned

14

Re: EXCEL Function returning RecordSet

Yagrus2 wrote:

Once again wanted to thank for your council with Option Explicit
Reconsidering once again a topic also saw that in function from the first message, Recordset it is put in not declared variable.
And it should be returned

And so when I noted this error, and I noted it not at once - visually it to make difficult, I advised Option Explicit, supposing that you understand an error at once. But you instead declared local variable and I thought that you do not know how to return value from function, therefore sent you to read the textbook:-D

15

Re: EXCEL Function returning RecordSet

Set Cmd = CreateObject ("ADODB.Command")
Cmd. CommandTimeout = 1000000
Cmd. CommandType = adCmdText
Cmd. ActiveConnection = Con

This piece at you does nothing functions, what for it there?