Can't change CommandText property in DataEnvironment

[nyx]

Hi,

I'm having a lot of trouble changing the CommandText property of a Data Environment.

I just moved over to an Access 2000 Database which in turn uses the Jet 4.0 OLE DB Provider. But when I try and change the CommandText property in code I get the following error:

Run Time Error

'-2147352567 (80020009)'

Method 'Item' of Object 'Commands' failed.

Here's the code I'm trying to execute:

     Dim sqlstr as string

     sqlstr = "SELECT BLAH FROM BLAH"

     DataEnvironment.Commands(1).CommandText = sqlstr
     DataReport.Refresh
     DataReport.Show

It bugs out right at the DataEnvironment line. I have NO idea why it doesn't work. Can anyone enlighten me please? Thanks.

Distressed, upset, frustrated...

Nicholas

 

[nyx]

OK,

solved that problem, but now I face a new one...

The first problem, was due to the fact that I'm an idiot and ... well yeah, I'm an idiot. I didn't open the database in Shared mode so when the DataEnvironment tried to make another connection to it I guess, it crapped out. I'm using DAO btw...

ANYHOO, now I'm facing a new problem. I would like for the user to open many reports at the same time so I have it as follows:

Dim sqlstr as String
dim r as New DataReport1

if <user clicked button 1> then sqlstr = &quot;SELECT BLAH1&quot;
if <user clicked button 2> then sqlstr = &quot;SELECT BLAH2&quot;
if <user clicked button 3> then sqlstr = &quot;SELECT BLAH3&quot;

DataEnvironment.Commands(1).CommandType = adCmdText
DataEnvironment.Commands(1).CommandText = sqlstr
r.show

However, when I do this, its not exactly doing what I hoped it would. It keeps giving me the same data of the first sql statement that I pick. What do I need to do to refresh it?

Any ideas?

Thanks

Nick

 

[nyx]

well, I solved it... took me a while and gave me quite a headache but I did it...

found this article, and it was a bit of help...

http://support.microsoft.com/support/kb/articles/Q251/3/29.asp

Here's the line that was the real kicker:

    If DataEnvironment.recordset.state then DataEnvironment.<recordset>.close

All the headache for one bloody line. geesh...

Nicholas