How to set report RecordSource to ADO recordset? 
Author Message
 How to set report RecordSource to ADO recordset?

How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve



Wed, 01 Oct 2003 02:48:30 GMT  
 How to set report RecordSource to ADO recordset?
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane


How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve



Wed, 01 Oct 2003 02:53:38 GMT  
 How to set report RecordSource to ADO recordset?
That kinda defeats the purpose of a stored procedure doesn't it?  One of my main
goals is fast performance.  We have a VERY(read slow most of the time) congested
LAN.  Maybe I could use a form and put a print button on it as a substitue for a
report.  Then I should be able to still use the stored procedure, right?

Thanks for the info,
Steve

Quote:
-----Original Message-----
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane



How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve

.



Wed, 01 Oct 2003 03:34:13 GMT  
 How to set report RecordSource to ADO recordset?
A pass-through query can execute your stored procedure and return the
necessary records. This is generally very fast. This is not a regular access
query and it bypasses Jet.

You need to create a new query without choosing any tables. Then select
Query | SQL Specific | Pass-Through. There is a connection builder to set
various properties. You can then enter the syntax to call your stored
procedure.

EXEC spUnMappedLabr para1, para2,...

You can easily modify the SQL property of the p-t in code.

Duane


That kinda defeats the purpose of a stored procedure doesn't it?  One of my
main
goals is fast performance.  We have a VERY(read slow most of the time)
congested
LAN.  Maybe I could use a form and put a print button on it as a substitue
for a
report.  Then I should be able to still use the stored procedure, right?

Thanks for the info,
Steve

Quote:
-----Original Message-----
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane



How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve

.



Wed, 01 Oct 2003 04:43:08 GMT  
 How to set report RecordSource to ADO recordset?
You're right about using a pass-through query, it works great when I hard code a
parameter value into the p-t query.  The problem now is that I need to be able to
pass a parameter value to the p-t query.  I haven't been successful getting any
type of variable recognized by the p-t query.  I suspect that the very nature of
a pass-through query(no JET action) means that it doesn't know about any
variables that may exist in VB.  Any thoughts on this?

Steve

Quote:
-----Original Message-----

A pass-through query can execute your stored procedure and return the
necessary records. This is generally very fast. This is not a regular access
query and it bypasses Jet.

You need to create a new query without choosing any tables. Then select
Query | SQL Specific | Pass-Through. There is a connection builder to set
various properties. You can then enter the syntax to call your stored
procedure.

EXEC spUnMappedLabr para1, para2,...

You can easily modify the SQL property of the p-t in code.

Duane



That kinda defeats the purpose of a stored procedure doesn't it?  One of my
main
goals is fast performance.  We have a VERY(read slow most of the time)
congested
LAN.  Maybe I could use a form and put a print button on it as a substitue
for a
report.  Then I should be able to still use the stored procedure, right?

Thanks for the info,
Steve
-----Original Message-----
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane



How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve

..

.



Sat, 04 Oct 2003 06:18:14 GMT  
 How to set report RecordSource to ADO recordset?
If your pass-through is qsptUnMappedLabr and your stored procedure is
spUnMappedLabr and one text parameter is expected. The value for the
parameter comes from Forms![frmImport]![cboCurPer]:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("qstUnMappedLabr")
'create the SQL with single quotes around the parameter
strSQL = "EXEC spUnMappedLabr '" & _
        Forms![frmImport]![cboCurPer] & "'"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

You can now open a report based on the pass-through with the parameter
loaded from your code.

Duane


You're right about using a pass-through query, it works great when I hard
code a
parameter value into the p-t query.  The problem now is that I need to be
able to
pass a parameter value to the p-t query.  I haven't been successful getting
any
type of variable recognized by the p-t query.  I suspect that the very
nature of
a pass-through query(no JET action) means that it doesn't know about any
variables that may exist in VB.  Any thoughts on this?

Steve

Quote:
-----Original Message-----

A pass-through query can execute your stored procedure and return the
necessary records. This is generally very fast. This is not a regular access
query and it bypasses Jet.

You need to create a new query without choosing any tables. Then select
Query | SQL Specific | Pass-Through. There is a connection builder to set
various properties. You can then enter the syntax to call your stored
procedure.

EXEC spUnMappedLabr para1, para2,...

You can easily modify the SQL property of the p-t in code.

Duane



That kinda defeats the purpose of a stored procedure doesn't it?  One of my
main
goals is fast performance.  We have a VERY(read slow most of the time)
congested
LAN.  Maybe I could use a form and put a print button on it as a substitue
for a
report.  Then I should be able to still use the stored procedure, right?

Thanks for the info,
Steve
-----Original Message-----
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane



How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve

..

.



Sat, 04 Oct 2003 06:29:28 GMT  
 How to set report RecordSource to ADO recordset?
Thanks so much for the tips, Duane.  This last part about modifying the Pass-
through query was the final piece to the puzzle.  This report runs very slick
now.  I really appreciate your persistence with this.

Thanks again,
Steve

Quote:
-----Original Message-----

If your pass-through is qsptUnMappedLabr and your stored procedure is
spUnMappedLabr and one text parameter is expected. The value for the
parameter comes from Forms![frmImport]![cboCurPer]:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("qstUnMappedLabr")
'create the SQL with single quotes around the parameter
strSQL = "EXEC spUnMappedLabr '" & _
        Forms![frmImport]![cboCurPer] & "'"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

You can now open a report based on the pass-through with the parameter
loaded from your code.

Duane



You're right about using a pass-through query, it works great when I hard
code a
parameter value into the p-t query.  The problem now is that I need to be
able to
pass a parameter value to the p-t query.  I haven't been successful getting
any
type of variable recognized by the p-t query.  I suspect that the very
nature of
a pass-through query(no JET action) means that it doesn't know about any
variables that may exist in VB.  Any thoughts on this?

Steve

-----Original Message-----
A pass-through query can execute your stored procedure and return the
necessary records. This is generally very fast. This is not a regular access
query and it bypasses Jet.

You need to create a new query without choosing any tables. Then select
Query | SQL Specific | Pass-Through. There is a connection builder to set
various properties. You can then enter the syntax to call your stored
procedure.

EXEC spUnMappedLabr para1, para2,...

You can easily modify the SQL property of the p-t in code.

Duane



That kinda defeats the purpose of a stored procedure doesn't it?  One of my
main
goals is fast performance.  We have a VERY(read slow most of the time)
congested
LAN.  Maybe I could use a form and put a print button on it as a substitue
for a
report.  Then I should be able to still use the stored procedure, right?

Thanks for the info,
Steve
-----Original Message-----
You can't use a recordset as the record source of a report like you can with
a form. I would suggest that you use a pass-through query as the record
source for the report. You can use code to modify the SQL property of the
p-t query.

Duane



How do I set a report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

****************END CODE*************************

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

Me.RecordSource = rst.Properties.Item.Name doesn't work either.  I tried
this
because I know that under DAO you usually use "rst.Name".

Me.Recordset doesn't apply to reports, only forms according to the help
files.  I
tried it anyway, but still doesn't work.

BTW, this is in Access 2000 mdb file.

TIA,
Steve

...

..

.



Sat, 04 Oct 2003 23:25:11 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. How to set report RecordSource to ADO recordset?

2. Not able to assign an ADO recordset as the recordsource/set of a report or form

3. Microsoft Access Project 2000-- set a ado recordset as a recordsource

4. Access 2000 How to set RecordSource to ADO Recordset in ADP SQL 7 Project

5. ?Using a stored procedure or an ADO recordset as a report recordsource

6. Setting a Report's Recordsource to a Recordset

7. ADO recordset errors when trying to set to form recordset

8. Assigning a created ado recordset to a sub forms recordsource

9. Use ADO recordset as form's recordsource

10. using ADO recordset as recordsource


 
Powered by phpBB® Forum Software © phpBB Group