Monthly Archives: April 2014

Connecting qUtopic templates to Azure

All templates’ sample data are stored in a database in Azure so you can quickly check the functionality of the Excel Add-In.

If you are behind a Firewall you need to make sure that your port 1433 is not blocked.
note Note

In addition to configuring the Azure SQL Database firewall, you may also need to configure the firewall on your network and local computer. To access a Azure SQL Database database from your computer, ensure that the firewall on your network and local computer allows outgoing TCP communication on TCP port 1433. (The Microsoft Azure SQL Database service is only available with TCP port 1433.)

Further reading can be found here:

Rapid Application Development with Excel and databases

Rapid Application Development (RAD), Agile Development, and Scrum, have been major topics in the front offices of large and medium size companies worldwide. The urgent need of having an application working ASAP have put Excel as the main tool at hand.  Excel facilitates the work because it is a flexible and familiar user interface. Excel can perform calculations, format text, and display charts natively. However, the connection to databases have been an obscure part of it. Mostly solved via a Data Connection Object, which is complicated and difficult to share with colleagues or using a VBA macro, that despite being effective, not always perform as fast and robustly as desired.

At qUtopic we know these issues because we have been dealing with them for many years. Our answer are the qUtopic functions. A set of database querying functions encapsulated in an XLL Add-in coded in C++.

qUtopic is an ideal lightweight affordable solution to any RAD development. Try qUtopic today!



Connect using an ODBC string


In computingODBC (Open Database Connectivity) is a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems; an application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as “ODBC-compliant”. Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or CSV files.

ODBC was originally developed by Microsoft during the early 1990s, and became the basis for the Call Level Interface (CLI) standardized by SQL Access Group in the Unix and mainframe world. ODBC retained a number of features that were removed as part of the CLI effort. Full ODBC was later ported back to those platforms, and became a de facto standard considerably better known than CLI. The CLI remains similar to ODBC, and applications can be ported from one platform to the other with few changes.

Most commonly used ODBC connection strings
We suggest using the following string when connecting to a local database (for example SQL Express or MS Access running in the final user’s machine):

When connection to a networked SQL server within company:

DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;DATABASE=qUtopic;Trusted_Connection=Yes
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Data\AdventureWorks.accdb

When connecting to Azure SQL Server:

Driver={SQL Server Native Client 10.0};,1433;Database=qUtopicTest;Uid=TestUser@vyi1qe1zgy;Pwd=qUtopic1234;Encrypt=yes; 

When connected to a web hosted MySQL database:

Driver={MySQL ODBC 5.2 ANSI Driver};;Port=3306;Database=qUtopicTest;User=YourUserName;Password=YourPassword;Option=3;

 * Driver need to be downloaded from here.

 You can also find a variety of ODBC strings at this great useful website