I've got a dynamic report which takes perameters set by the user and adds
them into predefined SQL statements on a tab within the workbook, This was
taking too long to return the results so i then looked at stored procedures
within access. I'm using the same connection code but am having trouble
executing the commeand....

I'm using the code below to execute the stored procedures and pass values
into the SP's parameters:

Public Sub tester()
Dim adoConnection As New ADODB.Connection
Dim rsResults As ADODB.Recordset
Dim SQL_hierarchy_level As String
Dim SQL_selection_name As String
Dim SQL_SDate As String
Dim SQL_EDate As String
Dim SP_Name As String
Dim adoCommand As ADODB.Command
Dim prmSDate As ADODB.Parameter
Dim prmEDate As ADODB.Parameter
Dim prmSelectName As ADODB.Parameter

Set rsResults = New ADODB.Recordset

SQL_hierarchy_level = Sheets("MainSQL").Range("C2")
SQL_hierarchy_level = SQL_hierarchy_level & "_i"
SQL_selection_name = Sheets("MainSQL").Range("C3")
SQL_SDate = Sheets("MainSQL").Range("C5")
SQL_EDate = Sheets("MainSQL").Range("C6")
SP_Name = Sheets("MainSQL").Range("C31")

Call setDNSConnection(adoConnection, 1)
Set adoCommand = New ADODB.Command
adoCommand.CommandText = SP_Name
adoCommand.CommandType = adCmdStoredProc

Set prmSDate = adoCommand.CreateParameter("SDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmSDate
prmSDate.Value = (SQL_SDate)

Set prmEDate = adoCommand.CreateParameter("EDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmEDate
prmEDate.Value = (SQL_EDate)

Set prmSelectName = adoCommand.CreateParameter(SQL_hierarchy_level,
adVarChar, adParamInput, 200)
adoCommand.Parameters.Append prmSelectName
prmSelectName.Value = (SQL_selection_name)

Set rsResults = adoCommand.Execute

ThisWorkbook.Worksheets("DATA_Report").Cells.Clear
ThisWorkbook.Worksheets("DATA_Report").Range("A2") .CopyFromRecordset rsResults

rsResults.Close
Set rsResults = Nothing

adoConnection.Close
Set adoConnection = Nothing
End Sub


The connection code i'm using is the same as before when i wasn't using
stored procedures. I've attached this below:

Function setDNSConnection(ByRef adoConnection As ADODB.Connection, _
intDNSSource As Integer)

Dim strConnectionString As String
Dim strDatabasePathAndName As String

If intDNSSource 0 And intDNSSource <= 2 Then
Select Case intDNSSource

Case 1
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
'.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With

Case 2
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With

End Select
End If
End Function


When i execute the tester code, it bugs out at the executing ado command
stage. The message reads "Run-Time error '3709': the connection cannot be
used to perform this operation. It is either closed or invalid in this
context"

Do you or anybody else have any ideas?

I believe that "adCmdStoredProc" does not work as expected (at least by you
& me).
I have had to use adCmdText with MySQL stored procedures.

NickHK

"Azza" wrote in message
...
I've got a dynamic report which takes perameters set by the user and adds
them into predefined SQL statements on a tab within the workbook, This was
taking too long to return the results so i then looked at stored
procedures
within access. I'm using the same connection code but am having trouble
executing the commeand....

I'm using the code below to execute the stored procedures and pass values
into the SP's parameters:

Public Sub tester()
Dim adoConnection As New ADODB.Connection
Dim rsResults As ADODB.Recordset
Dim SQL_hierarchy_level As String
Dim SQL_selection_name As String
Dim SQL_SDate As String
Dim SQL_EDate As String
Dim SP_Name As String
Dim adoCommand As ADODB.Command
Dim prmSDate As ADODB.Parameter
Dim prmEDate As ADODB.Parameter
Dim prmSelectName As ADODB.Parameter

Set rsResults = New ADODB.Recordset

SQL_hierarchy_level = Sheets("MainSQL").Range("C2")
SQL_hierarchy_level = SQL_hierarchy_level & "_i"
SQL_selection_name = Sheets("MainSQL").Range("C3")
SQL_SDate = Sheets("MainSQL").Range("C5")
SQL_EDate = Sheets("MainSQL").Range("C6")
SP_Name = Sheets("MainSQL").Range("C31")

Call setDNSConnection(adoConnection, 1)
Set adoCommand = New ADODB.Command
adoCommand.CommandText = SP_Name
adoCommand.CommandType = adCmdStoredProc

Set prmSDate = adoCommand.CreateParameter("SDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmSDate
prmSDate.Value = (SQL_SDate)

Set prmEDate = adoCommand.CreateParameter("EDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmEDate
prmEDate.Value = (SQL_EDate)

Set prmSelectName = adoCommand.CreateParameter(SQL_hierarchy_level,
adVarChar, adParamInput, 200)
adoCommand.Parameters.Append prmSelectName
prmSelectName.Value = (SQL_selection_name)

Set rsResults = adoCommand.Execute

ThisWorkbook.Worksheets("DATA_Report").Cells.Clear
ThisWorkbook.Worksheets("DATA_Report").Range("A2") .CopyFromRecordset
rsResults

rsResults.Close
Set rsResults = Nothing

adoConnection.Close
Set adoConnection = Nothing
End Sub


The connection code i'm using is the same as before when i wasn't using
stored procedures. I've attached this below:

Function setDNSConnection(ByRef adoConnection As ADODB.Connection, _
intDNSSource As Integer)

Dim strConnectionString As String
Dim strDatabasePathAndName As String

If intDNSSource 0 And intDNSSource <= 2 Then
Select Case intDNSSource

Case 1
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
'.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With

Case 2
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With

End Select
End If
End Function


When i execute the tester code, it bugs out at the executing ado command
stage. The message reads "Run-Time error '3709': the connection cannot be
used to perform this operation. It is either closed or invalid in this
context"

Do you or anybody else have any ideas?