We do a ton of document imaging where I work.  As a healthcare organization, we have made it a priority to work towards our ultimate goal of being a paperless entity.  For now, most printed documents we use are scanned in and saved to a dedicated SQL Server.  The software that handles this is 3rd party and the connections to handle the data exchange are built in and permanently set.  The software does allow us the ability to alter export settings for individualized documents, as was the case for a client satisfaction survey we are required to implement.  These documents were set up to have their data stored in our primary clinical SQL Server, with an SSIS package that is run monthly to collect the reportable data to send to the responsible insurance agency.  As usual, specifications for the surveys were changed quite dramatically, which resulted in a need to redo the surveys, as well as the database tables that hold the data and the package that exports it.  As the DBA, it was my job to set up the tables, the SSIS package, and the ODBC System DSN entries that would allow for the exporting of the data to our primary SQL Server.  Our report writer and document master created the new surveys, and we sat down to test them out.  All documents scanned in to the system are subject to verification of data, a semi-automated process that requires user interventions whenver the software is unable to validate entries on the scanned documents itself.  It was upon verification of our new forms that we ran into some errors related to the back-end VBA script that handles external calls for verification of client data.  The scripts were set up by someone no longer working here, and at a time before I began my career here.  While not terribly complicated, I am not familiar with the script and do not have the time to diagnose the problem.  As a programmer, I understand that one simple misplaced character can wreak catastrophic havoc on code, and the offending part may not be easily visible, especially when dealing with a language that I have not looked at in over 6 years.  While I was confident that a problem existed with the script, my report writer seemed to lean towards an issue with the new DB tables and the ODBC data source.  Up until now, I had never known how to test the connection to an existing data source.  After creation of a new data source, there is a button that performs a connection test, and that is all.  I needed a way to test an existing connection, and an easy way to see if I could retrieve and push data through it.  After a quick search, I came across this site which has all kinds of goodies.  From there I downloaded a small and powerful tool called OdbcTest.  This tool allows you to connect to any defined ODBC connection, and perform queries against it, including inserts and updates.  This was exactly what I was looking for.  Using this tool I was able to confirm that the data source I set up was functioning as designed and data could flow in and out.  Armed with this info, I asked the report writer to generate a new survey using an existing template that verified without problem.  He set the new form up and it both verified correctly and the data was successfully sent through the ODBC connection to the tables.  So if you are looking for a great utility to help analyze your connections, make sure to check out OdbcTest.

 

Hope this helps.   Happy connecting!