G
Guest
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?
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?