CDATA go from GoogleSheets to a ODBC data source

cdata_odata.pngOne of the nice things about Google Forms is that you can create a survey or quiz and collect the result into a Google Sheets Spreadsheet.  The problem is that Google sheets are not easily consumable as a data source.  In most cases, folks just export it to XLSX format to do additional work on it.  If you have an ongoing survey or a set of long quizzes this requires a lot maintenance and great care when moving the Results around.  A better way would be to consume the Google Sheet as an ODBC Data Source as a dynamic dataset.  CDATA has such a utility which I am going to review and document.  Disclaimer:  My copy of the CDATA driver was donated for me to review.

 

Installation

This is pretty straight forward and just takes a few minutes.  My suggestion is to load the 32 and 64 bit ODBC versions as well as everything else.  After the install is complete it automatically opens up the CData web page to guide you through configuring an ODBC data source.

This review is going to use Microsoft Access 2010 to “Link” to the Google Sheet. The sheet contains survey results from a group of testers, all using a Google Form to enter the results of their testing efforts.  The testers are distributed in multiple locations and are using different laptops including some ChromeBooks.

By “link”ing rather than “Import”ing we should never have to export the Google sheet to XLSX before every reporting session.  On some days we might have 3-5 reporting sessions.  The reports generated from Microsoft Access 2010 provide status, feedback, and progress for the testing team.

Google Drive

In order for this process to work,  you are going to need a Google account with some Google Drive space.  This space will contain both the Google Form, which can be accessed by most modern browsers, and a Google Sheet where we will choose to have the results saved.

GoogleDrive.png

Here is a sample of what the Google Form looked like.  Google Forms is very powerful and has some code capabilities as well as some add-ons to make it a very useful web data collection tool.

GoogleForm.png

Share the Google Form

Since the folks using this form are all over the place, we need to share a link with them that allows them to add new testing results.

Admin Mode

An admin user who can edit the form can (Send) the form to a user via e-mail or create a link that can be shared with users or generate embed code to include on another web site.  We are going to share the link with the reviewers.

Start.png

We want to (1) generate a link that can be shared, (2) shorten the link, so it doesn’t look scary and (3) copy the link so we can distribute it to the testers.

SendForm.png

User Mode

A user who has the original link to the form can create a link to share with another user.

Share.png

Private.png

In this example, the reviewers include both internal Phoenix Workgroup and external users, so we selected the “Anyone with the link” option

LinkShare.png

Google Sheet

The responses that are collected can optionally be put into a Google Sheet.   A nice touch is that Google Forms adds the first column with a timestamp that can be used as a unique identifier.  This is the Google Sheet we want to connect, via an ODBC Data Source,  to Microsoft Access.

Sheet.png

Create ODBC DSN

In this example, we are going to create a Data Source Name (DSN) using the 32 Bit ODBC driver with these steps:  (1)  Select System DSN, to create a connection that can be used by any user of this workstation, (2) Choose Add tocreate the new DSN, (3) Select the proper driver, in this example, it is the CDATA ODBC for Google spreadsheets and finally (4) Finish.

CreateODBC2.png

ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012, and is now no longer available. Instead, use the OAuth 2.0 authentication standard. To authenticate to Google Spreadsheets, you will need to obtain the OAuthClientId and OAuthClientSecret by registering an app with Google Spreadsheets.

See Connecting to Google Spreadsheets to connect to Google Spreadsheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.

OAuth

OAuth 2.0 requires a bit of fiddling, but it only needs to be done once for the connection.

Open up the Google Admin web site for your Google Account and go to security.  This is where we can configure the API’s

GoogleAdmin.png

API.png

api reference2.png

Developer Console for API’s

Now we need to go to the Developers Console for API Access to add a new project.

api manager.png

CDATA-ODBC is a known selection, start typing it and select the option when it displays, the project name will be displayed, in this example it is cdata-odbc-140816.

New Project.png

Click on the (Create), then (1) Select credentials, (2) Click on Credentials tab, (3) Click on Create Credentials button then click on the (4) OAuth Client ID.

creditientials.png

ConsentScreen.png

(1) Enter a product name, in this example cdata-odbc, (2) Save your work so far.

ProductName.png

Getting closer to creating that Client ID we need. (1) Select Other, (2) Repeat the project name (cdata-odbc), (3) Click on (Create)

CreateClient.png

Now comes a critical part, copy this stuff someplace secure because you’re going to need it in the next steps. (1) Client ID, (2) Client Secret and finally (3) (OK)

oauth client.png

All that just to get to this point.

tada.png

I would suggest that you download the JSON file with all the top-secret API stuff and save it in the same secure spot you saved the other stuff in.

json download.png

The file downloaded is a standard formatted JSON file with all the top-secret goodies in it.  Save it someplace secure, don’t let it out of your sight.

json.png

I use LastPass for keeping my secrets, passwords, and girlfriends’ phone numbers.

lastpassa2.png

Data Source Name properties

Transfer the OAuth 2 information into the Data Source Name on the ODBC management form.  The DSN should NOT have embedded spaces or special characters; this has been known to cause connection issues.

DSN01.png

We are so close now I can taste it; we need to identify the single Google Sheet this DSN will reference.

dsn02.png

Click on the (Test Connection) and cross your fingers.  The first time you use this ODBC-DSN, a browser window will open.  Why?  Because the very last step is you authorizing the cdata-odbc API to talk with the CDATA ODBC driver.

Auth01.pngauth02.png

We are sooooo close now. We have gotten this far, now (1) Allow cdata-odbc to manage the spreadsheets in your Google Drive.

auth03.png

OMG!!! It worked.  I need a shot of tequila.

auth04.png

Microsoft Access 2010

Create Linked Table

Either open or create an Access database.  (1) We need external Data; (2) it will come from an ODBC source. I wonder where that would come from?

accessExternal

Now we need to “Link” not “Import”. (1) Link, not Import, (2) Looks OK to me

Link.png

Now we select the DSN (1). The system DSN we created is under the (Machine Data Source) tab, (2) Select the DSN we created then (3) Again, looks OK to me.

selectDSN.png

We now need to select the (Responses).  (1) Is the responses (I don’t know what the item under it is) then (2) Still looks OK to me.

linkedtables.png

The next step takes a few seconds while the Google OAuth is completed and the Google Sheet is turned into a Linked Table.

linked.png

View and edit Linked Table

Now by double-clicking on the linked-table Access will display the contents.  The Linked Table will have a new column called ID added to it; there is a unique identifier in this column that helps make the CDATA-ODBC capable of editing that row.

ViewTable.png

Updating information on the table can be done from the table viewer

update.png

As the testers add new testing results to the Google Form they will automatically appear here, there is no need to export or copy-paste anything.  If we add a row to the Linked Table inside of Access, it will automatically appear in the Google Sheet in the Google Drive.

Linked Table Structure

The CDATA ODBC Linked Tables data types for each of the columns are selected by the ODBC driver based on the format of the Google Sheet column.  You may not like the data types selected.

TableStructure.png

If you attempt to change these you will be greeted with a message which translated means “Tough Luck, live with it”.  There are some Access tricks to handle this but that’s another post.

failed.png

Now we can use the Power of Microsoft Access to produce forms to maintain the Google Sheet or create reports.  Most Forms and reports start off with a query to drive them.

query.png

This query helps to drive the Microsoft Access form used to update the information from the Google Sheet.

Access form.png

The Microsoft Access reporting capability provides reports from the simple list report all the way to data analytics with business charts.

report.png

Summary

“See the world as a database” and not like a spreadsheet.  I am a database person; I visualize things into tables or collections, rows or documents and SQL or JSON.  I’m weird like that since it seems like most other folks use spreadsheets for doing just about everything.

The ability to take a Google Spreadsheet and see it as a database is very powerful, especially considering other Google add-ons like Google Forms, etc. use Google Sheets as their data store.

CDATA has all sorts of drivers to connect to all sorts of data sources that are not typically considered a database, and offers drivers that make it less complicated to connect sources that might otherwise be difficult to connect to as a database.

CDATA isn’t free or open-source and is limited to a Windows environment, but that doesn’t restrict how useful tools like this can be in some customer solutions.  One of my mentors, Dr. John Weiner, University of Buffalo, told me “Sometimes free is not the best buy, or even the only buy; be prepared to spend some cash.”

This post was an actual solution done for a customer; some of the details are masked out. The customer has decided to purchase the CDATA driver.  I can see additional solutions of this type and a training class or web lecture in the future.  You don’t really know something until you teach it.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Database .NET from Fish, a Swiss Army Knife tool kit for Database developers

image Teradata, SQL Server, SQLite, MySQL, Oracle, Excel, Access and dBase are databases you may use on a regular basis.  If you do you probably have 3-6 Database tools to help manage them all.  Each of these has their own user interface and capabilities.  Database .NET from Fish Code Library is the one tool to help you manage all these databases and many more.   Database .NET (DBN) is a single EXE file and can be installed as a portable product on a thumb drive or a shared product on Dropbox.

Database .NET is not the best name Fish Code Library could have picked as it makes it difficult to locate it on the web without having to look at a 100 posts about Microsoft’s .NET foundation.

DBN has so many features that DBA’s and DB Developers would like.

Features

Portable
  • Can be run on Corporate computers that don’t have install rights, you folks know who I mean
  • Can be run from a USB Thumb drive
  • Can be run from Dropbox and one instance shared across multiple computers
Free & Premium
  • Free version does 80% of what many users need
  • Premium version is $30…. Really!!  
    That’s less than the cost of a monthly cup of coffee (Cheap coffee at that)
Single EXE
  • Very small footprint
  • No Windows installation required
  • No extra DLL’s that might cause conflicts on your system
  • Updates are simple, just replace the EXE file
  • Overhead is very low, open multiple copies of DBN to work between different servers and database types
  • Fast processing
.NET Data Providers
  • DBN has all the latest .NET drivers embedded in the tool
  • No ODBC Data Source Names to maintain outside the tool
  • Configuration for DB connections maintained by DBN
Teradata
  • Teradata supported with Full capabilities of .NET 15.x.x
Server support
  • 21 databases servers supported
  • Legacy support for dBase and Paradox, anyone remember those?
Export Formats
  • Comma Separated Values (CSV) very common format
  • Tab Separated Values (TSV) gotta love Unix support
  • XML data exchange with bloated tags
  • Excel (XLS) Because if it isn’t a database it’s a spreadsheet
  • Insert SQL portable between different database formats
  • HTML ok the web isn’t a fad and is probably going to be here a while
  • Java Script Object Notation (JSON) NoSQL document format
DOS Command mode
  • DBN  be called in a Windows Batch file
  • Inexpensive and casual ETL tool and data transfer tool
    (GeekMustHave will have an example of Teradata to SQLite Windows Batch file soon.)
  • Database to Excel refresh tool with Windows shortcuts
  • Basic SQL to JSON convertor for relational databases
Version Tracking
  • Version tracking on selected SQL scripts
Code Libraries
  • Simple code library for each database/project in Windows directories and files
  • SQL code can be quickly saved in Windows files in a structured directory
  • Copy all your SQL scripts for all you projects by copy and pasting a single directory
  • Install DBN on Dropbox or other Cloud services and have you DB tool and script everywhere you go.
Support
  • Most Excellent
  • Fish Code Library also has a number of other productivity tools
  • Forum available to post improvements and vote on them
  • Many of the improvement requests show up in the frequent updates
  • Have I mentioned Teradata support

To be a fair reviewer of database tools you do have to mention some areas where the tool could be improved

Improvements

Results Limiter
  • Allows for the number of rows returned in the result to be automatically limited without changing the SQL.  Good for large database queries  and data warehouses usage.
MAC support
  • DBM is a Windows tool, get over it or get a copy of VM Fusion and run it there
  • That being said, a native MAC version would be nice

Access 2007 / 2010 Option Explicit is Important

If you are coding VBA for Access application the Option Explicit is important.  Option Explicit statement must appear in a script before any procedures.

When you use the Option Explicit statement, you must explicitly declare all variables using the Dim, Private, Public, or ReDim statements. If you attempt to use an undeclared variable name, an error occurs.

For some strange reason Access 2007 / 2010 doesn’t add this Option Explicit to every module you access so you need to add it manually.  You can set it to be default for all modules.

Go to the VBA window then Tools>> Options>> Editor Tab and check (Require Variable Declaration)

image

Access 2007 /2010 Display Report & Print to PDF

I usually have reports in my Access applications that the user wants to see the display and quickly option to print it to a PDF file.  I typically print all my reports electronically to save time, trees I’m not so much worried about. Angry smile

I do this by adding a Button using the Wizard and filling it out link I am going to print the report.  The I go to the VBA code behind the button and change it out.  If you are using macros this may be possible I just prefer VBA code.

Here is an example of the print report code

Access 2007 / 2010 ADO SQL Server 2008 execute Stored Procedure

I have a passion for writing Access front ends to SQL Server databases.  The same techniques can be used for other databases like Teradata and MySQL.  Much of what you can do in Access with SQL Server can be accomplished with linked tables however there are many time when running a SQL Server Stored Procedure or a Teradata Macro directly is a much better alternative.  Some advance SQL functions like OLAP and processing millions of rows are better done on the industrial servers not on Access direct.  Let the other server do the heavy lifting and just return a nice clean result to your Access application.

For SQL Server connections I highly recommend that you install the Microsoft Native CLI 10 drivers.  Download  This will provide your access with ADO and OLEDB support which in my opinion is the best way to talk direct to SQL Server.  The older DAO is no longer supported with 64 nit implementations of Access which is the majority of my customers.

One of the more difficult points is getting the SQL Server connection string correct for OLEDB and the Native CLI 10 drivers.  In my Access application I set this string once is a Global module that gets called from the AutoExec macro when the application starts up.

Here is an example of my SQL Server connect string for an Access application.  No you can’t have my server name and password.  Smile with tongue out

Here is an example of the code used to call an SP (spPopulateRuleLabel)  which just does some background updating of tables.  It has no parameters and returns no results. 

You will notice the (gsSQLOLEConnect) is used to supply the connection information to the Connection object.

The Stored Procedure (spPopulateRuleLabel) does some background processing and updates tables.  This would have been very difficult to do just using Access and Visual Basic.  The SQL Server TSQL allows for better control over database objects.

Just to give you an idea of what is being done by the SQL Server Stored Procedure here is the code.. Special thanks to Anush.

Access 2007 / 2010 Get popup form to open with a new record only

Sometime you want an Access popup form to open up and have a new blank record so that you add a new value to the table.  In this case the table is a linked SQL Server 2008 table.  This is not difficult to do  but there are a few traps here.

image

The form is called (frmLabelInsertPopUp).  To make it  a popup you will need to set some of the forms properties.

image

You want a Pop-up to behave like a pop-up.  The (Format) setting here accomplish this.  The ones outlined need to be changed.

image

Makes sense to set the (Allows Edit) to no as we don’t want to edit we want to add new records.  The (Data Entry) is the one used to indicate you want to add new records.  This seems confusing but oh well.  Why didn’t we set the (Allows Deletions) to No?  We are going to do that with a trick in the Query behind this form.

image

The is the (qryLabelInsertPopup) Query which is setup to allow me to just enter new rows.  The Trick is putting some Criteria in that brings back no rows.  In this case I used the Tilde (~).  I could have just as well used a zero for the LabelID.  The TRAP as I found out is that you must have the Primary Key in the results of the query to allow the query to do a new row insert.

No keep in mind the (Label_1) table is a linked table back to a SQL Server 2008 backend database.

image

Some folks don’t put a Criteria in and the query returns a bunch a rows, this will just slow down the Add process.

When I insert the row into the Labels_1 table I want to set the (ProjID) column to one that I have set in a global function.  I have a number of “Set” functions in a module called “Global” that are set by my application.  Then I can just use the Set functions in my Queries, Forms and VB Code.  I will have another post focusing on this.

image

Here is my form the (ProjID) field is the one outlined and in yellow behind the Label control.  To avoid this one displaying and to set the default value I need to set some properties of this form.

image

These (Format) setting accomplish this.

image

The (Data) setting of Default Values shows my Global set function SetProjID() which was set in the application to whatever the ID is for the current project we are focused in on.

image

The result is this pop-up which when you enter text into it the form will add a row in the SQL Server 2008 Label table and set the ProjectID field correctly as well.

Access 2007 / 2010 start up code

In Microsoft Access 2007-2010 I will often need to run some code when the Access database is first opened.  In my example I show how I set a SQL Server 2008 connection string as a global variable when the Access database file opens.

You need to know some VBA to be able to do this example.  I typically create my first module in Access and name it “Global”.  This is where many of my functions go for remembering codes, ID and connection strings that I need throughout my application.

SNAGHTML414828c

NOTE: Since the snapshot and the Code snippet this connection string has changed from ODBC to OLEDB but the concept is still the same for setting a global variable in application start up.

The Access database file name is BRM_V21m.  The module name is Global.  The function I want to run when the database opens is Application_Start(), yea I know my .NET ways are showing through.  You really didn’t think I was going to let you see my top secret connection information did you?  Smile

I made the return from the Application_Start function a Boolean value (True/False) so that I can expand this function later and if anything bad happens in the function I can set the return value to false.  Then I can check this status in the AutoExec macro and take remedial action if it’s False.

Now create a Macro and name it Autoexec, this macro automatically gets run once when the Access database opens.  Once of the things we need to do before we open any forms, queries or reports in run the Application_Start() function.

image

Teradata and Access – Personal Query Environment

PQE_2000

Download PDF version

I have been a Teradata specialist for quite some time now.  Data Warehousing and tools are in my blood.  Problem is that most data warehousing tools are either too simple requiring the non power user to know SQL or too expensive.  Microsoft Access is a great tool to use to create a what I call a Personal Query Environment or PQE.

Back in 1999-2000 I taught a class on how to use Teradata with Microsoft Access so you can do repetitive queries, reports and data load/extracts.    This class was based on Access 97 and Teradata V2.  Since 1999-2000 a number of other great tools for Teradata have come and some gone.  Recently I was asked to put together an admin utility for a Teradata database that was feeding a web site.  As a result I dug out my old notes, training materials and Access examples.  Looks like I will be updating some of this material including examples of code and I plan to post it here.  The download link above is my original course handouts, boy have things changed.