CODEWITHSUNDEEP
postgresqlexcelvba
maialithar
maialithar

Reputation: 3123

vba and postgresql connection

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?

Upvotes: 1

Views: 6910

Answers (1)

jpw
jpw

Reputation: 44891

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

Upvotes: 2

Related Questions