Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Create table from ADO Recordset

Create table from ADO Recordset

Create table from ADO Recordset

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!!!

RE: Create table from ADO Recordset

 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.


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

Hope this helps,

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

RE: Create table from ADO Recordset


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.

RE: Create table from ADO Recordset

reports do not support the Recordset property
Really ? Even in ac2003 ?

RE: Create table from ADO Recordset

Nope, the same error identified in earlier versions is displayed:

Run-time error 2593:

This feature is not available in an MDB

RE: Create table from ADO Recordset

baugie, thanks for the reply.
I've just read the MSKB article saying that the help file is wrong ...

RE: Create table from ADO Recordset

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,

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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