vba and postgresql connection

Question

I have a problem with connection from VisualBasic (Excel) to postgresql. I define my connection like that:

Set ObjMyConn = New ADODB.Connection
ObjMyConn.ConnectionString = "Driver={PostgreSQL Unicode};Server=ip;Port=port;Database=db_name;Uid=user;Pwd=pass;"
ObjMyConn.Open

When I want to load 443532 rows into Excel with

Set objMyCmd = New ADODB.Command
Set objMyCmd.ActiveConnection = ObjMyConn
objMyCmd.CommandText = "select * from table"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute

It keeps showing me run-time error Out of memory error while reading tuples.

I have already upgraded my ODBC driver to the latest version. I read here that I have to set Use declare/catch to true somewhere (odbc driver I guess).

Is it possible to set it in VB code?

jpw · Accepted Answer · 2014-03-23 20:18:11Z

2

+100

I believe you can just add the UseDeclareFetch=1option to the connection string like this:

"Driver={PostgreSQL Unicode};Server=ip;Port=port;Database=db_name;Uid=user;Pwd=pass;UseDeclareFetch=1"

But, if that doesn't work, there's another method - if you have set up a ODBC system data source with the ODBC Database Connection Administrator you can modify the registry key directly (provided you have the sufficient permissions).

The key you want to change is located under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PostgreSQL35Wif you're using a System DSN and the following vba code will enable Use Declare/Fetch:

Const HKEY_CURRENT_USER     = &H80000001
Const HKEY_LOCAL_MACHINE    = &H80000002

strComputer = "."

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\PostgreSQL35W"
strValueName = "UseDeclareFetch"
strStringValues = "1"

oReg.SetStringValue   HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strStringValues

answered Mar 23, 2014 at 20:18

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

maialithar Over a year ago

Right, so easy... first solution seems to be working fine. Thanks!

Collectives™ on Stack Overflow

vba and postgresql connection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related