Create table from ADO Recordset

[baugie]

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

 

[CautionMP]

baugie,
Have you looked at the [tt]RecordSource[/tt] 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,
CMP

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

 

[baugie]

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]

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

 

[baugie]

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

Run-time error 2593:

This feature is not available in an MDB

 

[PHV]

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

 

[CautionMP]

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.[ol][li]Create the recordset for the report in code, then cache it to the local users harddrive (in a text file).[/li]
[li]Create a query sting that points to this file to build your report.[/li][/ol]

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)