From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pgodbc + Excel + msquery + background refresh |
Date: | 2007-10-10 07:43:13 |
Message-ID: | 1192002193.442.12.camel@neuromancer.home.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michal Taborsky - Internet Mall | 2007-10-10 07:44:39 | Re: SLEEP in posgresql |
Previous Message | Ow Mun Heng | 2007-10-10 07:31:53 | Re: replacing single quotes |