pgodbc + Excel + msquery + background refresh

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;DR IVER={PostgreSQL Unicode};DATABASE=public;SERVER=127.0.0.1;PORT=5432;UID=pgus er;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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: pgodbc + Excel + msquery + background refresh

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;DR IVER={PostgreSQL Unicode};DATABASE=public;SERVER=127.0.0.1;PORT=5432;UID=pgus er;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?

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate