Bug: Exporting Linked Databases Causes Error 91

[hecon5]

While attempting to use the External Database Connection export tool, I get error 91 and cannot continue.

It appears ScanDatabaseObjects is not initializing m_AllItems or m_ModifiedItems. In turn, it

Digging into ScanDatabaseObjects, it appears conn isn't connecting at all.

When I dig into GetNewOpenConnection, it appears there's an error (-2147467259,"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", "Microsoft OLE DB Provider for ODBC Drivers") which is cleared by the CatchAny statement.

This leads to the connection being eliminated and error 91 later.

[hecon5]

I should add: The log does not display the issue (it's told not to, so that makes sense). The CatchAny is also not displayed regardless of Show Detailed output or Debug VBA Errors state.

[hecon5]

Huh: here's an interesting tidbit, and one that should be handled in this addin. I forgot that the connection string used for your tables (connected via OBDC) cannot be used (without parsing) by ADODB. You need to set a few extra bits.

I don't know how I didn't realize this at first, because I bashed my head against the wall a few months ago on our database for literally the same issue. We also have to test our db connections (in some cases...they don't connect on the first try anyway).

OOH: and another bit, maybe to why the CatchAny is failing, one I've gotten caught before: if you call a subroutine in the call of CatchAny and you don't cache (and then return) the Err code, it will poof out of existence if your subroutine has error handling.

[hecon5]

Ok, source of the issue is that the connection string did not work, but the class also did not correctly detect that it wasn't connecting.

When CatchAny was checked, the log did not capture the issue, because it was cleared and ignored.

I don't know if we want to be in the business of parsing ODBC strings and converting them to ADO ingestible connection strings, but we definitely need to capture the connection error(s) and bail out if it doesn't work before just running into a crash.

[hecon5]

One way to handle this is to initialize m_AllItems and m_modifiedItems before checking the connection in ScanDatabaseObjects. This would actually be preferred, as it would ensure bailing out of the function doesn't cause a downstream unrelated failure.

Another is we should consider caching the Conn in use; it's used several times for the same purpose, so not needing to (re)establish a connection can save some time (and overhead). Since it's destroyed when the class exits, we're not storing it for a ton of time, so the overhead burden shouldn't be too big.

[hecon5]

I'll put together a PR for this, I think I have a pretty good handle on most of this.

[hecon5]

Another find: LoadSampleConnectionStrings grabs the ODBC connection strings; so they need conversion to the ADO (OLE) connection strings for them to load.