HELP! Data control error (I've looked everywhere)

[bhieb]

I have a data control that I write a sql statement on the fly and
populate the recordset like so.

Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster
.... join and where clause")
set dat_Equip.Recordset = Rst (this is a DAO recordset)

This gives me a "operation is not supported by this object" at the
second line.

Oddly if I remove the DISTINCT it does not error out. Even more
weird, if I remove a few fields from the Source_UnitMaster it works
with the DISTINCT.

As far as I can tell it seems to be a jet issue because the code works
fine in Access 2002. I read somewhere that VB 5.0 uses a diferrent
version of Jet than Access 2002.

Is there a size limit to the data control, if so how do I get around
this?

I realize that the data control is problably not the best way to do
it, but this is an existing app that i really don't want to redo if
possible.

Some stats.
VB version 5.0 sp3
Access 2002
DAO 3.6 referenced

Thanks for the help.

[Loop]

I could be mistaken, but isnt DISTINCT a shape operator....

"bhieb" <bh***@alanritchey.com> wrote in message
news:29**************************@posting.google.c om...

I have a data control that I write a sql statement on the fly and
populate the recordset like so.

Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster
... join and where clause")
set dat_Equip.Recordset = Rst (this is a DAO recordset)

This gives me a "operation is not supported by this object" at the
second line.

Oddly if I remove the DISTINCT it does not error out. Even more
weird, if I remove a few fields from the Source_UnitMaster it works
with the DISTINCT.

As far as I can tell it seems to be a jet issue because the code works
fine in Access 2002. I read somewhere that VB 5.0 uses a diferrent
version of Jet than Access 2002.

Is there a size limit to the data control, if so how do I get around
this?

I realize that the data control is problably not the best way to do
it, but this is an existing app that i really don't want to redo if
possible.

Some stats.
VB version 5.0 sp3
Access 2002
DAO 3.6 referenced

Thanks for the help.

[Dag Sunde]

"Loop" <lo**@loop.com> wrote in message
news:Df*******************@newsread1.news.pas.eart hlink.net...

I could be mistaken, but isnt DISTINCT a shape operator....

"bhieb" <bh***@alanritchey.com> wrote in message
news:29**************************@posting.google.c om...

I have a data control that I write a sql statement on the fly and
populate the recordset like so.

Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster
... join and where clause")
set dat_Equip.Recordset = Rst (this is a DAO recordset)

This gives me a "operation is not supported by this object" at the
second line.

Oddly if I remove the DISTINCT it does not error out. Even more
weird, if I remove a few fields from the Source_UnitMaster it works
with the DISTINCT.

<snip>

[Loop]:
DISTINCT is an ANSI SQL modifier to the SELECT statement, limiting the
resultset to unique rows only, if there are duplicate rows in the result.

[bhieb]:
I think that the JOIN and WHERE statements are important in this context,
so please post them too... Preferably a stripped-down, complete sample.

Also, which of the statements give you the error? The ".OpenRecordset()"
or the ".Recordset = Rst" part?

--
Dag.

[Niche Software LLC]

YOu need to provide a column (field) name for the DISTINCT query, do not
use the wildcard.

I could be mistaken, but isnt DISTINCT a shape operator....

"bhieb" <bh***@alanritchey.com> wrote in message
news:29**************************@posting.google.c om...

I have a data control that I write a sql statement on the fly and
populate the recordset like so.

Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster
... join and where clause")
set dat_Equip.Recordset = Rst (this is a DAO recordset)

[Gemini]

bhieb wrote:

I have a data control that I write a sql statement on the fly and
populate the recordset like so.

Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster
... join and where clause")
set dat_Equip.Recordset = Rst (this is a DAO recordset)

This gives me a "operation is not supported by this object" at the
second line.

Oddly if I remove the DISTINCT it does not error out. Even more
weird, if I remove a few fields from the Source_UnitMaster it works
with the DISTINCT.

As far as I can tell it seems to be a jet issue because the code works
fine in Access 2002. I read somewhere that VB 5.0 uses a diferrent
version of Jet than Access 2002.

Is there a size limit to the data control, if so how do I get around
this?

I realize that the data control is problably not the best way to do
it, but this is an existing app that i really don't want to redo if
possible.

Some stats.
VB version 5.0 sp3
Access 2002
DAO 3.6 referenced

Thanks for the help.

Don't you have to do a SET...NEW command first?

g
z

[bhieb]

Sorry for taking so long to respond.

[Dag]
For testing purposes there is no WHERE clause just from [tableName].

[Niche]
It errors out at Set dat_Equip.Recordset = Rst

If I use field names it kinda works. If I remove any 4 fields it will
work, that is the weird part I ran into (see below snip). It does not
seem to matter which fields just any 4. Could it be a size
limitation?

(Snip)
"Even more weird, if I remove a few fields from the Source_UnitMaster
table it works with the DISTINCT."