0

My dog-and-pony VBA program to get a store/restaurant name from my spreadsheet and look up its town & state in a little Access database is coming together; I made some now-corrected flow control errors that had the lines

Set dbconnect = New ADODB.Connection
dbconnect.Open conn_str

executing more than once, without a dbconnect.Close occurring anywhere. Assuming that that's the reason for the error '-2147467259 (80004005)' (The table 'DevTableReal' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically), I tried executing a single sub with the line "dbconnect.Close" and was unsurprised to see this sub fail since dbconnect wasn't, at execution time, considered a valid object. Yet the ADODB connection, that "dbconnect" had been a handle for in the program's previous run, was apparently still out there.

Is there a way for a VBA program to look for & get a handle on an existing ADODB connection that is open - was opened, initialized, referenced - before said VBA program's run-time?

(Rebooting the machine (after fixing the code :D ) got rid of the connection, but I sooooo suspect there was a cooler way to do it.)

stuey
  • 23
  • 4
  • 1
    @stuey Aren't you creating the connections? – norie Nov 10 '21 at 03:36
  • norie - yes, one run of the program created the connection that the ADODB.Connection object "dbconnect" referred to, but foolishly didn't close it :/ – stuey Nov 10 '21 at 04:19
  • I explained that the second run of the program (the first run of which created & opened an ADODB.Connection object "dbconnect" but didn't close it) encountered a "table is already opened by another user" error when the connection object's "Open" method was attempted on the same database. I also explained that in that situation, a new little programmette to close the connection using "dbconnect.Close" won't work because the programmette doesn't have the "dbconnect" object initialized. – stuey Nov 10 '21 at 05:58
  • 1
    The question therefore is "Is there a way to see open database connections without object handles to reference them with?" – stuey Nov 10 '21 at 06:05
  • @stuey, I'm going off topic. Consider managing your connections, and if you need multiple connections to a data source, release connections no longer needed, or have somekind of indexed connection array or collection, and know which one does what. If you can run the application entirely on one connection do so. Make your own connection class that opens, closes, and executes commands. That way, "disposing" the class, releases that connection. I'm replying more to your, "...I sooooo suspect there was a cooler way to do it", part of the question. – njc Nov 10 '21 at 06:13
  • This will be a property of the database system, not of the ADO connector. There may be a way to query the database properties through ADO, but it would depend on database system. – david Nov 11 '21 at 00:33
  • VBA does not treat ADO objects in a special way (see the duplicate for the general mechanism). When you assign a `New` object to an existing variable, the previous instance will see its reference count decrementing by one, and if that was the last one, it will be destroyed - which in the case of an ADO connection means closing it. If the closing does not occur for you when you replace the content of the variable in that way, then the replaced connection is still referenced from somewhere - from an existing `ADODB.Command` object for example. – GSerg Nov 13 '21 at 09:00

0 Answers0