Unbound reports

[De_Cisse]

I'm facing this problem:

I want to create a report dynamically from VBA. The data for this
report is stored in a Db outside Access 2003. Due to security reasons
I don't want to bind the table form the foreign Db to my Access DB. So
I'm using a Data Link to create a connection towards the Db. I get all
the data in an ADO recordset. From that recordset I want to create a
report.

I can't set the recordset of my report to the recordset I created, as
this is impossible to do in Access 2003. Is the only possiblility to
create a temporary query in my Db, build my report, and as I close my
report erase this query?

Is there someone who can help me out with this one?

 

[Dirk Goldgar]

In

I'm facing this problem:

I want to create a report dynamically from VBA. The data for this
report is stored in a Db outside Access 2003. Due to security reasons
I don't want to bind the table form the foreign Db to my Access DB. So
I'm using a Data Link to create a connection towards the Db. I get all
the data in an ADO recordset. From that recordset I want to create a
report.

I can't set the recordset of my report to the recordset I created, as
this is impossible to do in Access 2003. Is the only possiblility to
create a temporary query in my Db, build my report, and as I close my
report erase this query?

Is there someone who can help me out with this one?

I'm not sure what you mean by a "Data Link", so I can't help with that.
Is there any reason you can't set the report's RecordSource property to
a SQL statement that specifies the external database link, using the IN
clause or one of the other external-database syntaxes supported by Jet?
You wouldn't need to create a stored query, even temporarily. If you
want, you can assign the RecordSource property in code in the report's
Open event.

Is the external database a Jet database, or is it some other database
format that supports ODBC? For a Jet database you can write a query
like this:

SELECT Field1, Field2, ...
FROM MyTable IN "" "MS Access;Database=C:\ForeignDB.MDB";

or like this (equivalent):

SELECT Field1, Field2, ...
FROM [MS Access;Database=C:\ForeignDB.MDB].MyTable;

You should be able to do the equivalent for ODBC databases.

 

[De_Cisse]

With Data Link I mean the use of a .udl-file in which you can define
the connection.

In fact the foreign Db is a Sybase Db, so I don't know if I'll be able
to set the datasource that way. But I'll give it a try.

Thanks for the tip.

 

[De_Cisse]

The solution you suggested wirks for an other .mdb.-file.

So I tried it for an Oracle Connection, I use this syntax:

SELECT *
FROM table_name IN "" [Provider=OraOLEDB.oracle;
Database=dbName;Connection=C:\oracleConnection.ud'l;];

But I get this error: Could Not Find Installable ISAM ==> I assume
something is wrong with the syntax, but I can't figure out what.

 

[De_Cisse]

A part of it I got working, using the syntax:

SELECT field FROM table IN "" [ODBC;DSN=dsn_name;UID=userdid;
PWD=pwd;];"

But now I would like to use the same using my .udl file in which my
connection is defined. Any suggestions?

 

[De_Cisse]

A part of it I got working, using the syntax:

SELECT field FROM table IN "" [ODBC;DSN=dsn_name;UID=userdid;
PWD=pwd;];"

But now I would like to use the same using my .udl file in which my
connection is defined. Any suggestions?

Any other suggestions?

 

[Tom Wimpernark]

dude you're insane

just use bound reports, man it's a whole ton easier

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

dude you're insane

just use bound reports, man it's a whole ton easier

 

[Dirk Goldgar]

In

A part of it I got working, using the syntax:

SELECT field FROM table IN "" [ODBC;DSN=dsn_name;UID=userdid;
PWD=pwd;];"

But now I would like to use the same using my .udl file in which my
connection is defined. Any suggestions?

I don't know if you can do it using an OLEDB provider. If you use an
ODBC connection, you can probably parse the UDL file in a report's Open
event, extract the connect string, and build a recordsource query on the
fly. But I have no experience in this area. You might try asking in
the newsgroup <microsoft.public.access.externaldata>.

 

[De_Cisse]

In

A part of it I got working, using the syntax:

SELECT field FROM table IN "" [ODBC;DSN=dsn_name;UID=userdid;
PWD=pwd;];"

But now I would like to use the same using my .udl file in which my
connection is defined. Any suggestions?

I don't know if you can do it using an OLEDB provider. If you use an
ODBC connection, you can probably parse the UDL file in areport'sOpen
event, extract the connect string, and build a recordsource query on the
fly. But I have no experience in this area. You might try asking in
the newsgroup <microsoft.public.access.externaldata>.

Ok, thanks. I'll post my question there.