Calling OpenSchema Method To Find Out Database Name
So, today was the last day of my job. Seven years with the same company, out of which the most of the time was in the Accounts Department.
As you can imagine, in the Accounts one has got much to do with spreadsheets, so no wonder that it was very useful that I learned to write Visual Basic macros to manipulate data in Excel spreadsheets.
Later on, our IT supplier showed me how to use this utility they called ExcelUpdate: it would read an SQL query command from an .ini file, match the result to a given column in the given Excel spreadsheet and write the specified columns from the query result into the spreadsheet.
So it was only natural that I was learning more SQL as it came very useful all the while.
It all came to a yet higher level, though, when I learned how to use the ADODB library to query our database for the data. Now I could get results of any given SQL query.
(Maybe I should point out that this wasn't how we were normally working as for our usual day-to-day work we had a pretty good front-end application with forms and dialogues and such, which our IT supplier made, and was maintaining, for us; but all this knowledge came very useful when we needed to do some kind of a custom report that one of our clients fancied or such.)
Only in the last couple of days of my work there I was asked to provide a report for which I needed to query our Warehouse transactions... the problem was that I didn't have all the documentation for the database, only the list of tables... but none of them seemed to have the Warehouse transactions in it...
So I e-mailed our IT provider asking them where the Warehouse transactions were... They replied saying it was in a different database — which I hadn't suspected at all — i.e. the Warehouse transactions being kept in a separate database from the one being used for our manufacturing Operations.
They gave me the name of the database, so I thought it was enough to replace the INITIAL CATALOG value in the .ConnectionString with the name of the other database, but it wouldn't open...
First I was kind of desperate, but then I remembered how I had obtained that list of tables in the first place, which was with an .OpenSchema(adSchemaTables) call. So I checked the Help and realised that this time, .OpenSchema(adSchemaCatalogs) was what I needed — and alas! It showed me, that the database name the supplied had given me was incorrect (as he was apparently quoting it from memory).
Now, with the correct database name I could execute the query and complete the report. Needless to say, I was all over the moon :)