Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

baugie (Programmer) (OP)
23 May 06 13:34
I am using one MS Access 2003 as a front end user interface for forms and reports, and another as the back end.  This is a user requirement.  I cannot use linked tables in the front end, so I am managing all forms through ADO Recordsets and the form recordset properties.  

I was hoping to do the same thing with the reports, but have now discovered that reports do not support the Recordset property.  I cannot store data in the front end system due to security constraints.  I am willing to create temporary tables that are automatically deleted when the report is closed since they would only show user specific data.  I could then dynamically set the recordsource of the report to the temp table.

Can I do either of the following, and if so please let me know how:

1)  Run create table query from the front end that will create a table in the front end using data from the back end

2)  Run create table with one ADO recordset on data retrieved by another ADO recordset?  (one to retrieve data, 2nd to write out data to table)

Any help is appreciated!!!
cheers
CautionMP (Programmer)
23 May 06 13:46
baugie,
 Have you looked at the RecordSource property of the Report? You can use a fully qualified SQL string that points to the back end database.

CODE

Report.RecordSource = "SELECT * FROM RemoteTable IN OtherDB.mdb;"

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

baugie (Programmer) (OP)
23 May 06 14:55
CautionMP;

This is a good idea, but the backend database is password protected.  How do I include the password in the SQL syntax?  When I use the SQL it says invalid password.
PHV (MIS)
23 May 06 15:37
reports do not support the Recordset property
Really ? Even in ac2003 ?
baugie (Programmer) (OP)
23 May 06 15:50
Nope, the same error identified in earlier versions is displayed:

Run-time error 2593:

This feature is not available in an MDB
PHV (MIS)
23 May 06 16:09
baugie, thanks for the reply.
I've just read the MSKB article saying that the help file is wrong ...
CautionMP (Programmer)
23 May 06 16:28
Isn't that the strangest thing. I can create the query, point to the password protected database, and supply the password. I can look at the table definition, but not the data. Goofy.

 Plan B: Since you are already manipulating the database connection in code, here is a thought.
  1. Create the recordset for the report in code, then cache it to the local users harddrive (in a text file).
  2. Create a query sting that points to this file to build your report.
It sounds a little ugly but it would eliminate creating and deleting temporary objects in the front end, and you should not have the password problem because the test file won't have a password.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close