Export recordset from ADP project

  • Thread starter Thread starter Angy007
  • Start date Start date
A

Angy007

In a ADP project, is there a way to export a recordset of a ListBox which
displays data from a stored procedure with parameter?
I have tried DoCmd.OutputTo acOutputStoredProcedure ... method, but ask me to
input parameter again.
Many thanks
Angy
 
Not sure to understand the question about the ListBox. Try to add the EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.
 
Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a ListBox
that
displays the results of a stored procedure, a command button which run the
stored
procedure.

Here my code:

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SP_name"
cmd.CommandType = adCmdStoredProc

Set Me.ListBox.Recordset = cmd.Execute(, Me.TextBox)

...

Now, I need to export what the ListBox shows after the user's research is
finished.

I'm trying the suggested solutions, but I don't know how specify the
control's name
to passing parameter .

...
DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP ? "


Thanks
Angelo




Sylvain said:
Not sure to understand the question about the ListBox. Try to add the EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.
In a ADP project, is there a way to export a recordset of a ListBox which
displays data from a stored procedure with parameter?
Click to expand...
[quoted text clipped - 3 lines]
Many thanks
Angy
Click to expand...
Click to expand...
 
The DoCmd.OutputTo is run as a macro and as such, you cannot use in-place
parameter like ?

You must dynamically built the whole string:

Dim sql as string
sql = "EXEC MySP " & FirstParameter
DoCmd.OutputTo acOutputStoredProcedure, sql, ....

If the parameter is a string, you must enclose it between single quote (and
doubling any embedded single quote to two single quotes):

sql = "EXEC MySP '" & Me.TextBox & "'"

or:
sql = "EXEC MySP '" & Replace (Me.TextBox, "'", "''") & "'"

For your ListBox, you can do the same thing and use a sql string for the
RowSource instead of setting the Recordset:

Me.ListBox.RowSource = sql

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Angy007 via AccessMonster.com said:
Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a
ListBox
that
displays the results of a stored procedure, a command button which run the
stored
procedure.

Here my code:

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SP_name"
cmd.CommandType = adCmdStoredProc

Set Me.ListBox.Recordset = cmd.Execute(, Me.TextBox)

..

Now, I need to export what the ListBox shows after the user's research is
finished.

I'm trying the suggested solutions, but I don't know how specify the
control's name
to passing parameter .

..
DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP ? "


Thanks
Angelo




Sylvain said:
Not sure to understand the question about the ListBox. Try to add the
EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.
In a ADP project, is there a way to export a recordset of a ListBox
which
displays data from a stored procedure with parameter?
Click to expand...
[quoted text clipped - 3 lines]
Many thanks
Angy
Click to expand...
Click to expand...
Click to expand...
 
Many many thanks for your reply.
It's exactly what I wanted to do.

Angy



Sylvain said:
The DoCmd.OutputTo is run as a macro and as such, you cannot use in-place
parameter like ?

You must dynamically built the whole string:

Dim sql as string
sql = "EXEC MySP " & FirstParameter
DoCmd.OutputTo acOutputStoredProcedure, sql, ....

If the parameter is a string, you must enclose it between single quote (and
doubling any embedded single quote to two single quotes):

sql = "EXEC MySP '" & Me.TextBox & "'"

or:
sql = "EXEC MySP '" & Replace (Me.TextBox, "'", "''") & "'"

For your ListBox, you can do the same thing and use a sql string for the
RowSource instead of setting the Recordset:

Me.ListBox.RowSource = sql
Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a
Click to expand...
[quoted text clipped - 48 lines]
Click to expand...
 
Back
Top