Leave OLEDB connection open

Hey all,
I have an excel document that when opened takes the users ID and password and creates an OLEDB connection to a Netezza database. I have no problem opening this connection but when the workbook_open sub is finished running, the connection is no longer accessible by other subs/functions. I need a way for this connection to be left open until the file is closed (or activity timed out) so that query parameters can be changed within the excel document and then a query run by a separate macro. I have the connection declared as Public in a regular module but still once that opening sequence is complete that connection value goes away.
Thanks

Check this out: http://msdn.microsoft.com/en-us/libr.../ff839237.aspx

Hmm, I'll have to test that when I have a minute. The way I've used it is in the creation of WorkbookConnections, as in use code to create a WorkbookConnection and set .MaintainConnection = True, then use the connection as needed. If you don't want the newly created connections to persist, you could have code delete the connection on workbook close or next open. I'll try to do it your way in a bit, in the middle of something right now.

Which line threw the error?

Try setting maintain connection before opening the connection.

It doesn't look like the maintain connection property is available when creating connection through ADODB. Since you are using conn.Open to open, I assume you are closing with conn.Close. If that is true, I think you can just delete the conn.Close. Note, though, that in general, it's a very bad idea to maintain an open connection to a database. It sucks resources on both ends and offers way more opportunity for corruption and concurrency issues. I know sometimes you just gotta do what you just gotta do, but, if there is any other alternative, like storing a local copy on a worksheet, processing as needed, then reopening connection when you're actually ready to use it. Not sure what your exact situation is, but you may want to give this some more thought.

And, again, you may find the connection manipulation much easier if you create WorkbookConnections as mentioned above.

Absolutely, you're already doing it in your connection string:

User ID=" & NZuser & ";Password=" & NZpass

and:

Persist Security Info=True

(though, I think you need to remove the spaces).

Once set, if you need to change, you can read, change, then rewrite the connection string properties. The code below has everything you need to alter the connections. It's probably way overkill for your needs, but it's all I have handy. It reads the necessary connection and command info from the existing connection, and uses "Replace" to change the necessary connection and command properties, then creates a new connection, merging previous and new info. If the new connection is successful, the old connection is deleted.

Please Login or Register  to view this content.

Note: in the above code, I copy the connection and command text into a new worksheet, rather than just loading them into string variables for the Replace function. I found that some of my really long connection and command strings were being truncated (to somewhat random lengths) when I used variables. A worksheet cell has no virtually no limit to the amount of text it can hold, so, I add a sheet, name two ranges, "nmConnection" and "nmCommandText", paste the old values, edit, create the new connection, then delete the worksheet that contained the named ranges.

Hmm, I'm not entirely sure I'm following your last post. If you create a "WorkbookConnection" object in your code, that connection will persist until it is removed, either by deleting it or by closing the file without saving. If you create an ADODB connection, like in your original post, it definitely will not persist; other than, perhaps, if you open the connection in one sub and close it later, in another sub, after you are done with it.