Problem with an open record set 3

[itmasterw]

Hi,
I am trying to put the data from the table tblPB_>_0_Crosstab_Counts and from the field 30 Days, and it just keeps running in a continues loop. If I hold the cursor over the connection it shows the right connection, but if I hold it over the Rs..EOF it say “ Operation is not allowed when object is closed”.; but it should be open. That is is a couple of lines above it is opened. Als, I used this exact same code in another program , I mean natually different fields and tables, but it works thaer just fine. And I have all the references set. So I do not know what is going on here. Any ideas would be much appreciated. Thank you
This is what I have.


Dim Rs As ADODB.Recordset
Dim Cn As Connection
Dim xl1 As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim r1 As Excel.Range

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data SOURCE = J:\APPSUPT\Adv_EscRPT\VB2\P188.mdb;"
'Cn.ConnectionTimeout = 30
Cn.Open

Set xl1 = CreateObject("Excel.Application")
Set xlBook = xl1.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Set xlSheet = xlBook.Worksheets(2)
Set xlSheet = xlBook.Worksheets(3)


FileName3 = "J:\APPSUPT\Adv_EscRPT\VB2\P188.XLS"
xl1.Workbooks.Open (FileName3)
xl1.Visible = True

Set Rs = New ADODB.Recordset

Rs.Open ("Select [30 Days] FROM tblPB_>_0_Crosstab_Counts"), _
Cn, adOpenKeyset, adLockOptimistic

x = 66
y = 9
Rs.Open
Do Until (Rs.EOF)
Set r1 = xl1.Range((Chr$(x) & Val))
r1 = Rs![30 Days]
'x = x + 1
y = y + 1
Rs.MoveNext
Loop


ITM

 

[Sheco]

After calling the .Open() method, it helps to check the .State property of your recordset.
Like this:

    Rs.Open
    IF (RS.State <> 1) THEN
       MsgBox "Error opening ADO Recordset!"
    ELSE
       Do Until (Rs.EOF)
       ....

 

[itmasterw]

Thank You for geeting back to me so fast. I did put your code in and it came back with the error message, therefore the the record set obect must be coled Right? But why would this be in happening in this program and not in the others';and what can I do about it.
Thank You

ITM

 

[Sheco]

You can use the same State property to test the connection object...

We could be barking up the wrong tree if the problem is in the connection.

 

[Sheco]

This has nothing to do with your database problem, but I noticed these lines:
Set xlSheet = xlBook.Worksheets(1)
Set xlSheet = xlBook.Worksheets(2)
Set xlSheet = xlBook.Worksheets(3)


Should this maybe be something like:
Set xlSheet1 = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets(2)
Set xlSheet3 = xlBook.Worksheets(3)

 

[tazuk]

A couple of thoughts.
1. Your code as posted shows that you are opening the recordset twice
2. The connection object is unnecessary (unless it is used for other items outside the quoted code). You can open a recordset using the connection string:

 RS.Open "Select [30 Days] FROM tblPB_>_0_Crosstab_Counts", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data SOURCE = J:\APPSUPT\Adv_EscRPT\VB2\P188.mdb;", adOpenKeyset, adLockOptimistic

HTH

TazUk

Blue-screening PCs since 1998

 

[itmasterw]

No, you are right, it returns the errorr message for the Rs ( the record set object). However, I just did this for the the connection and it was OK. So what cant I do from here. I mean why would my Rs be closing?


ITM

 

[RoyVidar]

You have seen you open the recordset twice?

[tt]Rs.Open ("Select [30 Days] FROM tblPB_>_0_Crosstab_Counts"), _
Cn, adOpenKeyset, adLockOptimistic

x = 66
y = 9
Rs.Open[/tt]

Roy-Vidar

 

[Sheco]

Doh!

I missed that!

 

[itmasterw]

Thanks for your input also, tazuk, I had that extra Open Rs statement in thre just to try something, but I took it out. and I took out the Cn; but it still does not work. This crazy because, as I said it works in my other programs. However any further ideas will be helpfull.
THank You

ITM

 

[Sheco]

Whats up with these parenthesis?

Rs.Open [red]([/red]"Select [30 Days] FROM tblPB_>_0_Crosstab_Counts"[red])[/red]", _
Cn, adOpenKeyset, adLockOptimistic

 

[Sheco]

bleh a typo... I added an extra quote in there that isnt in your source.

 

[RoyVidar]

I don't know, but since the table name contains some special characters, have you tried using brackets on it? [tblPB_>_0_Crosstab_Counts]

Roy-Vidar

 

[itmasterw]

I know you would not normally put htem in, but I saw one example that had them, but with or without the perends it still does not work. I tred taken out the Cn like tazuk had suggested, and an interesting thing happened. Now it does not give the stae error, but it still goes in a continuses loop, and if youu hold the cusro over the Rsd object you still get the message "Operation is not allowed when object is closed". Which leads me to beleive that the connection is closed, but why?

ITM

 

[itmasterw]

Yes at one point I had everthing in brackets. that is table names and fields; but still nothing.

ITM

 

[Sheco]

Maybe change the loop.

Instead of:
Do Until (Rs.EOF)


Try this:
Do While Not Rs.EOF

 

[itmasterw]

No I still get that message and not results. But that was a good try.
thank You

ITM

 

[itmasterw]

Hay it works. When RoyVidar said that I sould use brackets Itryed that and it did not work, but when I tryed changing the name all together it worked. Thank you all for your help I really appreaciate it. '
thank you

ITM

 

[vmaruv]

Hi,

How can I check if any connections to the database are opened or not ?

In my project, I am using the connection object to execute a stored procedure which will kill all the existing user sessions to a sql server 2000 database and put it in single user mode. Then I am using SQL DMO to perform the backup of the database by connecting to the server.

I am getting below error that

Database 'Test' is already open and can only have one user at a time. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

I am closing all the existing connections after putting the database in single user mode (i.e., executing the SP that does this). But still am not sure why I'm getting this error. As suggested in the above posts I checked to see if conn.state is 1 or not. It is showing as 0.

Is there a way to close all the connections to this database before opening a new one and performing the backup ?

Please help !

 

[vmaruv]

Thanks. I was able to resolve it. The mistake I was making was , I was closing the connection but forgot to set the connection object to nothing.

I did conn.close
but forgot set conn = nothing.