set Report.RecordSource from another mdb

[alfalf]

Hello all.

I have small problem.

WHAT I'VE GOT:
- I have main mdb app;
- I have cities.mdb in wich US cities are stored;
- Objects in another mdb are tables only and all tables in it have their owner (with password);
- From main app., I connect to this mdb via public & permanent connection (no linked tables, only coded connection) wich have user/pwd string (no matter which user is logged - coded connection is OK);

WHAT I WANT:
I want to set up recordsource of cities report to this external mdb.

WHAT I'M SUCK WITH:
I don't know how to set up Report.RecordSource to external mdb table?

WHAT I TRYED:
Since I successfully connect via public permanent link to this mdb like (it produces cities.ldb file (connection) and usual openRecordset commands works perfectly):

Set workJET = CreateWorkspace("NewJetWorkspace", "owner", "owner_pwd", dbUseJet)
Set connDbCity = workJET.OpenDatabase(Me.connVarServer & "cities.mdb", False, False, "MS Access")

I tryed following Report.Recordsource 'connection string' and variations:

Report.RecordSource = connDbCity.Execute("SELECT...;")

but no help from that.

SO MY Q WILL BE:
How to set report's record source to an external mdb that has ownership protected tables?


Any help is appriciated! Thanks!

 

[PHV]

Have you tried to set Report.Recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alfalf]

Hello PHV!

report's recordsource have to be a string (in this case SQL string (within doublequotes)).

If I set Report's recordsource, I don't get string, but object. Therefore I'm expiriencing 'Mismatch errors'...


e.g.

Set repset = connDbCity.OpenRecordset(CSQL)
Report.RecordSource = repset

where CSQL is "SELECT ... ;" (the string), BUT repset is OBJECT (ADO.Recordset or DAO.Recordset, the same).


Do You have any piece of code?

Thanks anyhow and anyway.

 

[PHV]

Report.[highlight]Recordset[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alfalf]

Yap yap, sorry, that is:

Report.Recordset = repset

Never the less, for that I get error:
'Application-defined or object-defined error.'

?

 

[PHV]

You must use the Set instruction to instantiate objects ..

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alfalf]

cool