Report with ADODB.Recordset

[ezfriend]

I have a report that I want to run with a recordset instead of using query?

The BE database is connected through a ADODB.Connection and BE tables are not linked to the front-end. It was done this way to prevent the number of active connections to the BE since we have so many users using this small application.

I have been googling all day to day and searching this forum, but could not find a good tutorial on how to create MS Access reports with recordset.

My plan is when the Report is open, I will create a SQL statement which retrieve records to a recordset (m_rs), then loop through the recordset and populate the report.

Not sure how this is done.

Also need some help on setting up group value using recordset.

Thanks in advance.

EZfriend.

 

[Magster]

I asked a similar question today and I was able to solve my problem and it is under VBA questions and here is the title of my question:
Open a report using a form's recordsetclone as the report data source

This will work for you... I am still trying to find out how to do this using a recordsetclone from a form that is calling the report. When I filter down to a subset of data I want that in the report and all I keep getting is the entire recordset from the form.

Hope this helps!

 

[ezfriend]

I'll give it a try.

The thread, just in case.
http://www.access-programmers.co.uk/forums/showthread.php?t=161868

 

[ezfriend]

That doesn't seem to work.

I am assuming that your form is bounded to a table and that's probably why it doesn't work for my scenarion. My report is based on a ADODB.Recordset and when I set the record source the recordset (m_rs in this case), I get the error

Run-time error '13'
Type mismatch

Any other suggestions?

 

[LPurvis]

ezfriend, are you using an ADP?
(You mention that "The BE database is connected through a ADODB.Connection" - which could be interpreted as the OLEDB connection that Access maintains with a SQL Server db and the ADODB connection which it exposes which returns that).

If so then you can assign a recordset as the report's source.
If not then you can't. Only ADPs support this functionality.

If you're connecting only through ADO then you'll need some local storage of the data for the report to read from.
To insert it into a local table would be an iterative process (navigating through the recordset rows and inserting the data from each into the local table).

You can use reports in a quasi-unbound manner, but it limits the functionality you're able to represent in the report (for example a loss of grouping and subreports).

What scenario are you looking at?


Magster - see that earlier thread of yours for my thoughts on what you have.

 

[datAdrenaline]

ezfriend....

Leigh has definitely provided some good advice ... I certainly would not expect anything else from the 'ole bloke either! ...

....

If you are using an MDB/ACCDB, you can still go without linked tables by having your recordsource of the Report embedded with an ODBC connection string ... something like this ...

{JET/ACE (Access) Back End .. (note: extension is .accdb with ACE)}

SELECT *
FROM [;DATABASE=C:\SomeFolder\SomeFile.mdb].SomeSource
 
... Or ...
 
SELECT *
FROM SomeSource IN '' [;DATABASE=C:\SomeFolder\SomeFile.mdb]
 
... Or ...
 
SELECT *
FROM SomeSource IN 'C:\SomeFolder\SomeFile.mdb'

.....

{With a SQL Server Back End}

SELECT *
FROM [ODBC
    ;DRIVER=SQL Server
    ;SERVER=T02ADN01
    ;DATABASE=SomeDatabase
    ;Network=DBMSSOCN].SomeSource
 
... Or ...
 
SELECT *
FROM SomeSource IN '' [ODBC
    ;DRIVER=SQL Server
    ;SERVER=T02ADN01
    ;DATABASE=SomeDatabase
    ;Network=DBMSSOCN].SomeSource

(Note: in ALL cases SomeSource can be a table or queyr in the referenced db)

Also, you can specify the userID (UID=userID) and password (pwd=yourpwd) if you are using SQL Server Authentication.

 

[LPurvis]

Absolutely. Just bear in mind that you'd not be then using ADO - which is what you asked about.
Also bear in mind that there's not anything wrong with not using ADO. ;-)

 

[ezfriend]

Now the mystery is solved. I do not use an ADP and the BE is just another Access file.

The reason I ended up creating the application this way is due to the number of active connections to the BE database that keep locking records.

I ended up wrinting some classes that handles data manipulation instead of bounding objects to the tables. By doing so, eliminate the record locking issues so I was hoping I can use the recordset to run my reports as well. My reports do contain subreports and grouping as well so my options with recordset are limited per your suggestion above.

I will definitely give datAdrenaline's suggestion a try.

My application has two parts: User module and Admin module.

The user module is fine with they way they are running right now since users don't run report.

Admin Module runs a lot of reports and this is where my concern is; however, since I only expect at most three users to use the database at any one time, hopefully this won't post much issue in the long run.

Here is another question that I may need more suggestions from you guys or the forum.

The User Module is copied to the Admin module for manual data entry. The user module doesn't use table link so; therefore, i use the adodb.connectoin to get the data to the interface.

Question:
If I leave the User module (the one that is copied/imported to the admin module) with using ADODB.Connection while other function of the Admin Module such as reports use a direct link table to get the data, would this cause any issue? Is this a bad approach?

Please note that if administrator runs the User Module, it will still establish a connection to the BE (same database) as new connection and retrieve data to the forms.

Thanks in advance for your suggestions/helps.
May thanks to LPurvis, datAdrenaline, and Magster for your contributions to this topic. It will help many users (like me) in the long run.

Ezfriend.

 

[LPurvis]

Falling back on the linked tables when it's convenient to do so - but taking advantage of your direct connection at other locations should provide a good solution for you.
You have the slight overhead of accurately establishing the links as well as the connection in code, but as the connection alone isn't enough then you have to do something.
Bear in mind the alternative offered to you by Brent of not actually creating linked tables, but instead using a well formed ODBC connection string.
(Jet still wraps the request).

Cheers.