Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: ADO Recordset to Report

    I have an MSAccess (2k) front end containing Forms and Reports.
    I have an MCAccess (2k) back end containing data tables.

    I would like to do the following:
    Use ADO.
    Not connect the front to the back via linked tables.

    I would like to somehow get the data from a reomote table into a local report.

    I understand a Report requires a query or table and an ADO Recordset is neither.

    One solution would be to dump the desired records from the remote table into a local temporary table with the local table as the record Source for the Report.

    If this is the recommended solution, how would I do that without reading the individual remote records and writing them one by one to a local table.

    Is there some way to do a "Insert into XXX Select * from YYY" type of syntax where the "from" table is a remote ADO recordset and the "to" table is a local table?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Since no one else is replying, I will be the bearer of bad news. A report will only connect to a query or a table (linked or otherwise). As to your other option of creating a table from the recordset, you will have to either have the structure of the table defined and then add the records one at a time, or create the table structure on the fly and then save the records one at a time. There is no way to copy a recordset into a table without looping through the recordset one record at a time.

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since your ADO recordset is based on a SQL statement, you could do away with recordset, and convert the SQL statement into an "INSERT INTO..."
    statement to populate your temp table.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Oct 2003
    Posts
    13
    Thanks DCKunkle,
    you confirmed my bad vibes.

    RedNeckGeek,
    the problem with your suggestion is that it will create the table in the back end (remote) database and not in the current or front database where the report can get to it.

    I guess I have two choices; create a temporary table and populate one record at a time or create a link connection to the back end.

    Thanks again.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by MyerK
    RedNeckGeek,
    the problem with your suggestion is that it will create the table in the back end (remote) database and not in the current or front database where the report can get to it.
    You're right... I don't think my solution would work, unless you link the tables. However, you could link the tables with code, build your temp table,
    populate your temp table, and then drop the links again.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •