Skip site navigation (1) Skip section navigation (2)

Re: pgodbc + Excel + msquery + background refresh

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: pgodbc + Excel + msquery + background refresh
Date: 2007-10-10 08:17:50
Message-ID: 1192004270.442.19.camel@neuromancer.home.net (view raw or flat)
Thread:
Lists: pgsql-odbc
On Wed, 2007-10-10 at 16:07 +0800, Ow Mun Heng wrote:
> Just wonder if anyone here uses Excel to connect to PG via ODBC.
> 
> I'm using it extensively as my platform to get data from PG/MSSQL
> directly into excel. (Excel uses the msqry32.exe file which is like a
> stripped down sql query tool and returns data directly into excel)
> 
> When using mssql, connecting from excel to mssql, I can get the query to
> run in the background. Hence, a long running query will not interfere
> with normal running of other excel works. Eg: Create new sheets,
> graphing etc.
> 
> However, when trying to achieve the same thing using PG, somehow it
> either :
> 
> 1. PG/PG_ODBC doesn't parse/handle the request to do the query in the
> background
> 2. I'm doing something wrong.
> 
> I'm partial to #1 as it works find on mssql.
> 
> Here's a sample query macro which you can stick into Excel. (alt-F11,
> Module, Insert-New-Modules)
> 
> 
> The Keyword here is "Refresh BackgroundQuery = True"
> 
> Sub macro1()
> 
> SQL = "Select * from public.tablename limit 5000"
> With
> ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DRIVER={PostgreSQL Unicode};DATABASE=public;SERVER=127.0.0.1;PORT=5432;UID=pguser;PWD=pguser")), Destination:=Range("A1"))
>                 .CommandText = SQL
>                 .Name = ""
>                 .FieldNames = True
>                 .RowNumbers = False
>                 .FillAdjacentFormulas = False
>                 .PreserveFormatting = True
>                 .RefreshOnFileOpen = False
>                 .BackgroundQuery = True
>                 .RefreshStyle = xlInsertDeleteCells
>                 .SavePassword = False
>                 .SaveData = True
>                 .AdjustColumnWidth = True
>                 .RefreshPeriod = 0
>                 .PreserveColumnInfo = True
>                 .Refresh BackgroundQuery:=True 
>                 End If
>             End With
> End Sub
> 
> I think this is like the last hurdle for me from moving from mssql to
> PG.
> 
> Thanks and hopefully, there will be someone who uses it this way.


Digging a bit... Found this in MS Knowledge base.

http://support.microsoft.com/kb/211931/en-us

...
This behavior occurs when one of the following conditions is true:

	The Open Database Connectivity (ODBC) driver you use to create 	
	the query does not support asynchronous queries.

-or-
	You record or run a macro while the query is executing.

.....

So.. does Pqsqlodbc supports async queries?

In response to

pgsql-odbc by date

Next:From: noreplyDate: 2007-10-11 06:02:44
Subject: [ psqlodbc-Bugs-1009413 ] 08.02.0500 SELECT * returns double with for CHAR columns
Previous:From: Ow Mun HengDate: 2007-10-10 08:07:14
Subject: pgodbc + Excel + msquery + background refresh

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group