can't open a recordset based on a query

[DerFarm]

   Conn.ConnectionString = BuildConnectionString(TargetDB)
   
   
   strTemp0 = ...code to build a select query...
   strTemp1 = CreateView(TargetDB, strTemp0, "CountMatches")

   CountMatches_RC.Open "CountMatches", Conn, adOpenForwardOnly, adLockReadOnly

CountMatches_RC is properly declared as New ADODB.Recordset
CreateView is a function which creates a query and puts it in the TaregetDB...it works and I can see the query when I open the target, and it runs properly when opened.

at the open statement I get the error:
"The connection cannot be used to perform this operation. It is either closed or invalid in this context"

The views have been refreshed and are available at the run time.

Any suggestions?

 

[DerFarm]

just did a compact to make sure, same thing happened

 

[harebrain]

What are you trying to accomplish that wouldn't be achieved by:

CountMatches_RC.Open strTemp0, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

which does, ostensibly, return a recordset?

 

[DerFarm]

I get exactly the same response.

I had tried that in a previous incarnation and it hadn't worked. At the time I thought it might be because I was using a query within a query.

 

[harebrain]

In your code fragment, we see you assigning a value to Conn.ConnectionString -- Do you ever execute Conn.Open?

 

[strongm]

Or are you opening it and closing it in CreateView?

 

[harebrain]

My point was that with the code given, you can't tell whether the problem is with the connection or the command. If the connection were to be opened explicitly, the problem would be narrowed down one way or the other.

 

[SBerthold]

The connection is not opened. You are building a connection string, but it appears that the connection hasn't been opened yet.

You have two choices:

1. Open the connection first (Conn.Open). And then it should work, if the connection was properly opened.

2. Let the recordset object open the connection, which appears to be what you are trying to do, not seeing the connection's "Open" method being called. In this case you need to change the recordset's ActiveConnection to:

CountMatches_RC.Open "PERS", conn.ConnectionString, adOpenForwardOnly, adLockReadOnly

For both, make sure everything needed in the connection string is there (Provider, Data Source, Password, User ID, etc.), otherwise you will get a different error (provider error, Authentication error, etc.):

 

[SBerthold]

I tested this first, and everything seems to be ok if the connection string is alright, and if the View "CountMatches" has been added and not still sitting only in the OS Cache. If it is, and not actually physically written to the db yet, you will need to wrap open and close a transaction and flush the cache. If there is a problem with View delayed in being added due to the OS, then a different error will come back that the object cannot be found.
BTW, the previous posted line of code I wrote, should have read:

CountMatches_RC.Open "CountMatches", Conn.ConnectionString, adOpenForwardOnly, adLockReadOnly

 

[DerFarm]

Alright, it's official. I'm an absolute idiot. You guys were right, I wasn't opening the connection.

Thank you for seeing it. This is what comes of now programming for 6 months and then trying to get back into it.

 

[SBerthold]

You know, we have special emoticons we use for times like these:


And then there are the ones who sometimes just keep silent when a silly mistake was made ...