Excel vba connect to postgresql

Name already in use

PostgreSQL_Applications / ExcelClient.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

Copy raw contents

Copy raw contents

I have shown how to use PostgreSQL as a back-end for a Shiny application here. Users often like an Excel application that allows them to download their data directly as a table that they can then manipulate at will in a familiar environment. I know Excel is less than ideal but its ubiquity and popularity make it hard to ignore. Also, I can re-use the same PL/pgSQL code I wrote for the Shiny app by calling it from Excel VBA. I know! VBA should have been retired years ago but this is an easy win for me because, thanks to the database code I have already written for the Shiny application, I really don’t have to write much VBA at all. I think this is an oft-overlooked advantage of database procedures/function: they are written once but can be used in multiple applications.

Steps in developing the Excel client

1. Install the psqlODBC client from here
2. Set up a DSN as per instructions here.
3. In the VBA Editor add the required ADO reference as described here
4. Test connection to the database using VBA
5. Write a function calling the PL/pgSQL stored procedure to return a Recordset.
6. Write the *recordset data to a new sheet.
7. Programmatically create a pivot table and pivot chart.

Extra PL/pgSQL Functions

I want to minimize the amount of work done in VBA. In the R client version, I defined two functions in the server.R file for retrieving data from the database. The first returns all the Ensembl gene IDs for a given gene while the second one uses this list to build the data frame by concatentaing the data frames for each Ensembl gene ID. For the Excel VBA version, I want to roll these two steps into one so I defined a new PL/pgSQL function that does this. It also gives me the opportunity to use the RECORD and the RETURN NEXT construct. Here it is:

I also need a function to return all the metadata IDs so that they can be used in the form listbox for user selection. It might appear like over-kill to write a function for a task such as this that simply selects all the values for one column in a table. Why not use a simple SQL statement in the VBA code? My reasons for doing it this way are two-fold:

1. The account I am using has been set up so that it has no privileges on any tables, views or any other objects except stored functions.
2. I can re-use this same function in other clients so that if anything changes in the definition, I need only update the stored function. Here is this simple function:

Calling PL/pgSQL in VBA

In order for this to work I had to install the pgODBC library and reference the Active X Data Objects (ADO) library in the Visual Basic Editor (Tools->References). The first thing to verify is that I can connect to the database using same read-only account as I used in the R client. I have put the VBA code to create the connection, open and close it into a VBA module called modPgConnect:

Here is a simple VBA Sub that I wrote to test this module:

The «OK!» message box tells me that my set-up is working and that I have all the necessary pieces in place to connect to the PostgreSQL database.

I can now check that I can call the PL/pgSQL stored functions defined above. I have defined VBA to call these functions in a class. I know that VBA’s object-oriented feature set is incomplete but it is still worth using, especially for bigger projects. The class I have written is called DataRetriever and it contains just two methods:

Ultimately, I want to use this class in a VBA form to write calues to sheets. Before I add the form, I use a plain module to create an instance of this class and test its methods. This module is simply called testModDataRetriever:

The Excel VBA Range object has a very convenient method called CopyFromRecordset that takes a ADODB.Recordset instance and writes its rows to the range. I use it here to dump the entire Recordset rows and columns into a sheet. It saves me having to loop over the recordset in a nested loop to write rows and columns to the target sheet.

I have now defined all the VBA and PL/pgSQL code that I need for a VBA form that will allow me to select a metadata ID from a listbox and a gene name from a textbox and then write the output data to a new Excel workbook that I can save where I wish. I’ll also add VBA code to automatically generate pivot charts and pivot tables for the output data.

Источник

Pragmateek

A geek on his way to IT pragmatism

Using an Access, MySQL, PostgreSQL or SQLite database from VBA

Introduction

Note to pedantic guys: yes Access is not a database engine, only the graphical front-end to Jet/ACE, but we’ll stand with this simplification. 🙂

The sample application (Excel + VBA) and the SQL source code are available in this ZIP archive: VBA_Databases_Source.zip.

If you are an advanced VBA developer who builds applications that manage a non trivial amount of data odds are good you are using an Access database to store them.
If this setup perfectly fits your current needs, you completely master it, you’re not experiencing any issue and your needs won’t evolve in the near future you can skip this article and continue enjoying your system. 😉

Indeed, do you really need a new database management system (DBMS)?
Often the only argument in favor of migrating to other DBMS is they are “better”; while it’s true for some technical capabilities, it may not be regarding other “metrics” like simplicity: Access is easy to understand and manage for non IT staff and is often installed with default business workstation setup along with the rest of the Office suite.

So let’s say you have strong reasons to migrate to a new DBMS because you’ve come to the point where you feel you need support for at least one of the following features: interoperability, big storage, high concurrency (hundreds of users) and/or high performance, and Access starts to become a part of the problem.
So what can you do if you want to enhance the quality of your database without making your total cost of ownership (TCO) explode?

Your TCO is essentially made of:

• licensing costs: limiting them is quite simple: using a free, usually open-source, database and paying only for support
• management costs: they are by far bigger than the licensing costs and are directly impacted by the complexity of the DBMS; so you need a simple DBMS that you’ll be able to setup and manage yourself as you used to do with Access without the help of system or database administrators
• development costs: every additional change to your current schema or VBA implementation to fit the new DBMS will have a cost; so we want things to be transparent with zero additional development, which in particular means a standard SQL-based DBMS.

While the equation may seem a little complex it has at least three solutions:

• SQLite is the ideal candidate if you’re happy with the “single-file” model, you don’t have high concurrency constraints, and your only needs are interoperability (with Mac OS, Linux, Unix…), bigger storage and/or costs savings,
• MySQL and PostgreSQL: if you need support for high-concurrency, really big storage (e.g. tens of GBs), advanced user-management, performance fine tuning and other advanced features you’ll have to jump out of the single-file world.
  If you don’t have specific requirements then MySQL and PostgreSQL will appear similar to you and equally do the job. However, in this kind of situation, I have a preference for MySQL, not because its inherent capabilities would be superior (as I’ve said MySQL and PostgreSQL are roughly equivalent for simple setups), but because, as the reference open-source DBMS for years, MySQL benefits from a huge community and toolbox. Moreover, while you’ll sure find the tools to work in good conditions with PostgreSQL, if you ever need to justify your choice to your hierarchy you’ll be in a better position if you choose the standard solution instead of the challenger.
  But as I’m not sectarian, and for completeness, I’ll cover both.

In this article I’ll quickly cover the setup of these three DBMS (with links to other resources for more extensive instructions) and illustrate their usage with a small VBA application, a revolutionary todo-list manager, that uses Access too.

Data schema

The data schema used by our application is really basic: one table with 3 columns:

• Description : a textual description of the task,
• Category : a tag that will help further categorization of the bunch of tasks you’ve created,
• Due date : the limit date for accomplishing the task, after you could be in trouble with your boss!

Here is how it looks like in Access:

Access Tasks Table

The equivalent in Access SQL dialect is:

MySQL

Installation

You can download the MSI installer from here: MySQL.
Once downloaded start it and accept any Windows security popup that could appear.
Then you can follow this slide-show for further instructions:

Источник

Pragmateek

A geek on his way to IT pragmatism

Using an Access, MySQL, PostgreSQL or SQLite database from VBA

Introduction

Note to pedantic guys: yes Access is not a database engine, only the graphical front-end to Jet/ACE, but we’ll stand with this simplification. 🙂

The sample application (Excel + VBA) and the SQL source code are available in this ZIP archive: VBA_Databases_Source.zip.

If you are an advanced VBA developer who builds applications that manage a non trivial amount of data odds are good you are using an Access database to store them.
If this setup perfectly fits your current needs, you completely master it, you’re not experiencing any issue and your needs won’t evolve in the near future you can skip this article and continue enjoying your system. 😉

Indeed, do you really need a new database management system (DBMS)?
Often the only argument in favor of migrating to other DBMS is they are “better”; while it’s true for some technical capabilities, it may not be regarding other “metrics” like simplicity: Access is easy to understand and manage for non IT staff and is often installed with default business workstation setup along with the rest of the Office suite.

So let’s say you have strong reasons to migrate to a new DBMS because you’ve come to the point where you feel you need support for at least one of the following features: interoperability, big storage, high concurrency (hundreds of users) and/or high performance, and Access starts to become a part of the problem.
So what can you do if you want to enhance the quality of your database without making your total cost of ownership (TCO) explode?

Your TCO is essentially made of:

• licensing costs: limiting them is quite simple: using a free, usually open-source, database and paying only for support
• management costs: they are by far bigger than the licensing costs and are directly impacted by the complexity of the DBMS; so you need a simple DBMS that you’ll be able to setup and manage yourself as you used to do with Access without the help of system or database administrators
• development costs: every additional change to your current schema or VBA implementation to fit the new DBMS will have a cost; so we want things to be transparent with zero additional development, which in particular means a standard SQL-based DBMS.

While the equation may seem a little complex it has at least three solutions:

• SQLite is the ideal candidate if you’re happy with the “single-file” model, you don’t have high concurrency constraints, and your only needs are interoperability (with Mac OS, Linux, Unix…), bigger storage and/or costs savings,
• MySQL and PostgreSQL: if you need support for high-concurrency, really big storage (e.g. tens of GBs), advanced user-management, performance fine tuning and other advanced features you’ll have to jump out of the single-file world.
  If you don’t have specific requirements then MySQL and PostgreSQL will appear similar to you and equally do the job. However, in this kind of situation, I have a preference for MySQL, not because its inherent capabilities would be superior (as I’ve said MySQL and PostgreSQL are roughly equivalent for simple setups), but because, as the reference open-source DBMS for years, MySQL benefits from a huge community and toolbox. Moreover, while you’ll sure find the tools to work in good conditions with PostgreSQL, if you ever need to justify your choice to your hierarchy you’ll be in a better position if you choose the standard solution instead of the challenger.
  But as I’m not sectarian, and for completeness, I’ll cover both.

In this article I’ll quickly cover the setup of these three DBMS (with links to other resources for more extensive instructions) and illustrate their usage with a small VBA application, a revolutionary todo-list manager, that uses Access too.

Data schema

The data schema used by our application is really basic: one table with 3 columns:

• Description : a textual description of the task,
• Category : a tag that will help further categorization of the bunch of tasks you’ve created,
• Due date : the limit date for accomplishing the task, after you could be in trouble with your boss!

Here is how it looks like in Access:

Access Tasks Table

The equivalent in Access SQL dialect is:

MySQL

Installation

You can download the MSI installer from here: MySQL.
Once downloaded start it and accept any Windows security popup that could appear.
Then you can follow this slide-show for further instructions:

Источник