Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Illinois
    Posts
    2

    Unanswered: External data for a report using a module

    Greetings all!

    I am trying to create a report in Access that is based on a recordset from a different (in this case Oracle) database. In my report I create a connection to my database and open my recordset (form load module). This works fine, but how do I relate my recordset fields to the fields on the report? I have tried the rs.fields("ITEM") as the record source for the report object (fields on the report) but that doesn't work.
    I'm basically trying to get around the log in screen for running the report. If I base the report off of a linked table, then I close the access database, I have to authenticate again to run the report. I should be able to authenticate in a module and populate the report thru the module, right?
    Any help is greatly appreciated!

    Robert Lewis

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: External data for a report using a module

    Perhaps creating a temporary table when the connection is establised and deleting the temporary table on close, and basing report on the temporary table.

    Originally posted by rjlewis5
    Greetings all!

    I am trying to create a report in Access that is based on a recordset from a different (in this case Oracle) database. In my report I create a connection to my database and open my recordset (form load module). This works fine, but how do I relate my recordset fields to the fields on the report? I have tried the rs.fields("ITEM") as the record source for the report object (fields on the report) but that doesn't work.
    I'm basically trying to get around the log in screen for running the report. If I base the report off of a linked table, then I close the access database, I have to authenticate again to run the report. I should be able to authenticate in a module and populate the report thru the module, right?
    Any help is greatly appreciated!

    Robert Lewis
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  3. #3
    Join Date
    Dec 2002
    Location
    Illinois
    Posts
    2
    Hmm, well...that might work. Is there any way I can pass in a variable to name the temportary table so that each time its run it has a different name? Reason being, the report is called from a web page. Currently I'm using the webpage to insert a value into a temp table (that all users share) to run the report against. As you can imagine, if 2 people hit at the same time one person gets the others data. I need to avoid this problem. I think the connection / recordset would fix the problem, I'm not sure a temp table would unless it was exclusive to that web user. Maybe I can pass in the username (authenticated to the webserver) and make the table that name? Hmmm, thanks for the help!!

    Robert

  4. #4
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Yes you certainly can pass a variable as a table name when creating a table. Assign the username to some string and use that string to create a new table.

    Best place for help on this is Access VBA help files under tabledef.


    Originally posted by rjlewis5
    Hmm, well...that might work. Is there any way I can pass in a variable to name the temportary table so that each time its run it has a different name? Reason being, the report is called from a web page. Currently I'm using the webpage to insert a value into a temp table (that all users share) to run the report against. As you can imagine, if 2 people hit at the same time one person gets the others data. I need to avoid this problem. I think the connection / recordset would fix the problem, I'm not sure a temp table would unless it was exclusive to that web user. Maybe I can pass in the username (authenticated to the webserver) and make the table that name? Hmmm, thanks for the help!!

    Robert
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

Posting Permissions

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