Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

Microsoft Access

The database that comes with Office Professional. Access can handle tens of thousands of rows with ease and has great connectivity support for traditional database servers.

Using ADO with Access reports ( 1 2)


I am using Access 2003 as a front end to a SQL Server 2005 Express database. My plan is to use ADO code for all interaction between the front end and back end. Is there any way to use an ADO recordset as the recordsource of an Access report?

I found one example but what it did was create a local temporary table and set the report recordsource to that temp table. It doesn't actually use ADO, but uses parts of the ADO connection/recordset to create the temporary table.

Thanks for any replies.

BTW - Is the dbforum search feature broke? I found one post asking about it, but there was no response.

Nope ... Cannot do ... Access Reports can be bound to stored procs, tables, or set programmatically ... Can't be bound to an ADO recordset.

For sub reports I will write to a temp table. Otherwise I use pass through queries. 
 
The source of the problem (as I understand it) is that a recordset is simply a cursor (a load of pointers to individual records) whereas reports require sets of data (for groupings and aggregations). Hopefully one day this problem will be sorted out.

If that is the case then the only thing I can think of is using the ADO recordset to create a temporary table locally in Access and base the report off of that. This is different from the example above in that the example sets up a link to the table and then bases the report off of it.

I've seen some things on pass-through queries but that is not what I'm looking for either.

EDIT - pootle, we were posting at the same time and I didn't see your response.

I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.

If anyone has any more opinions I'd like to hear them still.

EDIT - another simultaneous post, with Teddy this time

You should be abstracting your data layer with stored procs and views server-side.  You lose a good deal of the really neat funcitonality you gain by going with sql express/mssql by pulling direct recordsets.  There are design and maintenance considerations for using procs and views instead of raw queries as well.  If for some reason your backend changes or the logic behind a query changes but the result set is the same, you don't have to change your front end, only the proc/view.

Quote:
	
	
		
			
				Originally Posted by gwgeller
				I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.
			
		
	
	

Sounds good  

	Quote:
	
	
		
			
				Originally Posted by gwgeller
				If anyone has any more opinions I'd like to hear them still.
			
		
	
	
I suspect you will eventually find you need a pass through one day - try writing n thousand line recordsets to local tables if you don't believe me. 
 
Partly because of the ADO issue I export most of my reports to Excel now (using a nifty Excel method - CopyFromRecordset). Obviously doesn't work for reports requiring formatting (such as invoices and the like) but the punters can get their hands dirty with the data in Excel and so it has been a popular move.

Quote:
	
	
		
			
				Originally Posted by gwgeller
				I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.

If anyone has any more opinions I'd like to hear them still.

EDIT - another simultaneous post, with Teddy this time 
			
		
	
	

I don't see the reason for a temp table?

pootle, I believe you on the huge recordsets, but mine should be limited. I don't know much about pass through queries but how do you create yours? Do/can you create them dynamically or set them up in the .mdb/.adp file? If I have to store the queries in the .mdb/.adp file that is taking away from the abstraction right? I envisioned having nothing but forms, reports and modules in my front end.

Teddy if I don't use a temp table how would my report access the data?

All my data manipulation (crosstabs excluded) is in views and sprocs on the server. My app accesses sprocs only (via pass through queries). A pass through is just a connection and a string of unparsed text sent to the server to execute - it is just about the dummest query there can be. 
 
So - I use pass throughs (created dynamically - there is no other way - you need to programmatically change the SQL if you need to pass parameters) to call sprocs.
 
Pass throughs do present security holes. You are passing text to the server to execute and the connection string is stored as part of the pass through properties.

Quote:
	
	
		
			
				Originally Posted by pootle flump
				All my data manipulation blah blah blah ...
			
		
	
	


What's up???? What happened to the audacious cuddler of flumps????  Too hoity-toity to associate with us riff-raff??? So, what brought about this change of heart ...

Quote:
	
	
		
			
				Originally Posted by M Owen
				So, what brought about this change of heart ...
			
		
	
	
Boredom

	Quote:
	
	
		
			
				Originally Posted by M Owen
				Too hoity-toity to associate with us riff-raff??? 
			
		
	
	

Yup

Refers to some pointless nonsense in the 'Coral:
http://www.dbforums.com/showthread.p...89246&page=330
 
Concluding here:
http://www.dbforums.com/showthread.p...89246&page=331

Quote:
	
	
		
			
				Originally Posted by gwgeller
				pootle, I believe you on the huge recordsets, but mine should be limited. I don't know much about pass through queries but how do you create yours? Do/can you create them dynamically or set them up in the .mdb/.adp file? If I have to store the queries in the .mdb/.adp file that is taking away from the abstraction right? I envisioned having nothing but forms, reports and modules in my front end.

Teddy if I don't use a temp table how would my report access the data?
			
		
	
	

Recordsource = "EXEC sp_MyProc"

Quote:
	
	
		
			
				Originally Posted by Teddy
				Recordsource = "EXEC sp_MyProc"
			
		
	
	
ADP\ ADEs only


Related Links

How to bind Text Box Data from a Combo Box Selection
Using one lookup table for multiple tables
Adding a NEW Record Using a Form
Last name didn't show up on the report
Rounding issue - off by 1
maintaining color in access tables
export to excelsheet with selected cells to be colored
Cascading forms
Text oddity in report
What Am I Doing Wrong?
Datasheet to Open Child Form
Please help me figure this out how to do this...
Querying Active Directory
Query to find median
Relationship Question
Text to Integer