Thread: Access and PG ODBC problem

Access and PG ODBC problem

From
anthony.caduto@micorp.com
Date:

Hi,
I am trying to convert some access databases to use PG linked tables and all was going good until I tried to do some stuff in VBA script..

Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "driver={PostgreSQL};server=192.168.1.29;port=5432;database=mydb;uid=postgres;pwd=;ksqo=;"
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "select * from lw_partnershipdata.switchboard_remote", conn, adOpenDynamic, adLockOptimistic, adCmdText

It all goes good until the last line and then I get this error:

Column "OID" does not exist

None of my tables have OIDs as they are large and I don't want the overhead of the OIDs.

Any help/Ideas would be appreciated.

Thanks,


Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management

Re: Access and PG ODBC problem

From
Andrew Ayers
Date:
anthony.caduto@micorp.com wrote:
>
> Hi,
> I am trying to convert some access databases to use PG linked tables and
> all was going good until I tried to do some stuff in VBA script..
>
> Dim conn As New ADODB.Connection
> Set conn = New ADODB.Connection
> conn.Open
> "driver={PostgreSQL};server=192.168.1.29;port=5432;database=mydb;uid=postgres;pwd=;ksqo=;"
>
> Dim rs As New ADODB.Recordset
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseServer
> rs.Open "select * from lw_partnershipdata.switchboard_remote", conn,
> adOpenDynamic, adLockOptimistic, adCmdText
>
> It all goes good until the last line and then I get this error:
>
> Column "OID" does not exist
>
> None of my tables have OIDs as they are large and I don't want the
> overhead of the OIDs.
>
> Any help/Ideas would be appreciated.

Don't know if this will help - but did you try checking "Row Versioning"
on in the ODBC driver?

Andrew Ayers

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

Re: Access and PG ODBC problem

From
"Dave Page"
Date:
 


From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of anthony.caduto@micorp.com
Sent: 04 October 2004 22:39
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Access and PG ODBC problem


Hi,
I am trying to convert some access databases to use PG linked tables and all was going good until I tried to do some stuff in VBA script..

Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "driver={PostgreSQL};server=192.168.1.29;port=5432;database=mydb;uid=postgres;pwd=;ksqo=;"
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "select * from lw_partnershipdata.switchboard_remote", conn, adOpenDynamic, adLockOptimistic, adCmdText

It all goes good until the last line and then I get this error:

Column "OID" does not exist

None of my tables have OIDs as they are large and I don't want the overhead of the OIDs.

Any help/Ideas would be appreciated. 
 
Hi Tony,
 
I tested this code in VB6 (where I have a nice debugger), and it works perfectly either with, or without the infamous updateable cursors option on the version of the driver that I posted yesterday (8.00.0001). Can you try with that version please?
 
Regards, Dave.

Re: Access and PG ODBC problem

From
anthony.caduto@micorp.com
Date:

Hi Dave,
Thanks for the Info, switching to that latest snapshot did the trick.

Would it be possible to get a updated msi installer out there with the newest version?

Thanks,

Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management



"Dave Page" <dpage@vale-housing.co.uk>
Sent by: pgsql-odbc-owner@postgresql.org

10/05/2004 02:45 AM

To
<anthony.caduto@micorp.com>, <pgsql-odbc@postgresql.org>
cc
Subject
Re: [ODBC] Access and PG ODBC problem




Hi Tony,
 
I tested this code in VB6 (where I have a nice debugger), and it works perfectly either with, or without the infamous updateable cursors option on the version of the driver that I posted yesterday (8.00.0001). Can you try with that version please?
 
Regards, Dave.

Re: Access and PG ODBC problem

From
"Dave Page"
Date:
 


From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of anthony.caduto@micorp.com
Sent: 05 October 2004 15:07
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Access and PG ODBC problem


Hi Dave,
Thanks for the Info, switching to that latest snapshot did the trick.

Would it be possible to get a updated msi installer out there with the newest version? 
Not with a snapshot version I'm afraid (I just don't have time at the moment). I'll produce a new MSI when we have something stable enough to call a 'real' release.
 
Regards, Dave 

Re: Access and PG ODBC problem

From
anthony.caduto@micorp.com
Date:

Hi Dave,
Is the MSI script available somewhere so I could build my own installer?
The latest snapshot is a lot more stable than the Nov 2003 stable release and i will need the version that does not have the OID issue to continue with my Access/Postgresql rollout.

Any reason why you are using MSI instead of something like Inno Setup (http://www.jrsoftware.com)?

I would be willing to maintain a snapshot installer if you need some extra help.


Thanks,

Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management



"Dave Page" <dpage@vale-housing.co.uk>

10/05/2004 09:12 AM

To
<anthony.caduto@micorp.com>, <pgsql-odbc@postgresql.org>
cc
Subject
RE: [ODBC] Access and PG ODBC problem





 


From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of anthony.caduto@micorp.com
Sent:
05 October 2004 15:07
To:
pgsql-odbc@postgresql.org
Subject:
Re: [ODBC] Access and PG ODBC problem



Hi Dave,

Thanks for the Info, switching to that latest snapshot did the trick.


Would it be possible to get a updated msi installer out there with the newest version?

Not with a snapshot version I'm afraid (I just don't have time at the moment). I'll produce a new MSI when we have something stable enough to call a 'real' release.
 
Regards, Dave

Re: Access and PG ODBC problem

From
"Dave Page"
Date:
 


From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of anthony.caduto@micorp.com
Sent: 05 October 2004 15:19
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Access and PG ODBC problem


Hi Dave,
Is the MSI script available somewhere so I could build my own installer?  
 
Yes, it's in the CVS on GBorg.
 
The latest snapshot is a lot more stable than the Nov 2003 stable release and i will need the version that does not have the OID issue to continue with my Access/Postgresql rollout.

Any reason why you are using MSI instead of something like Inno Setup (http://www.jrsoftware.com)?  
 
MSI is what Microsoft support these days - it allows packaging into merge modules for easy inclusion in other packages, and installation are easily scriptable.
 

I would be willing to maintain a snapshot installer if you need some extra help. 
 
Well I have been meaning to port the installer from Wise for Windows to Wix if you'd like to look at that and take over it's maintenance. Wix (wix.sf.net iirc) is Microsoft's first open source project and allows you to build MSI files from XML code. It will also decompile existing MSI files which should definately give you a head start. 
 
Regards, Dave.

Re: Access and PG ODBC problem

From
anthony.caduto@micorp.com
Date:

Hi Dave,
Inno can do all of that as well and it's a true open source project.  I really don't like supporting MS$ in any way.

I will take a look at the stuff on wix.sf.net iirc


Thanks,


Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management



"Dave Page" <dpage@vale-housing.co.uk>

10/05/2004 09:32 AM

To
<anthony.caduto@micorp.com>, <pgsql-odbc@postgresql.org>
cc
Subject
RE: [ODBC] Access and PG ODBC problem





 


From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of anthony.caduto@micorp.com
Sent:
05 October 2004 15:19
To:
pgsql-odbc@postgresql.org
Subject:
Re: [ODBC] Access and PG ODBC problem



Hi Dave,

Is the MSI script available somewhere so I could build my own installer?
 
 
Yes, it's in the CVS on GBorg.
 
The latest snapshot is a lot more stable than the Nov 2003 stable release and i will need the version that does not have the OID issue to continue with my Access/Postgresql rollout.


Any reason why you are using MSI instead of something like Inno Setup (http://www.jrsoftware.com)?
 
 
MSI is what Microsoft support these days - it allows packaging into merge modules for easy inclusion in other packages, and installation are easily scriptable.
 

I would be willing to maintain a snapshot installer if you need some extra help.


Well I have been meaning to port the installer from Wise for Windows to Wix if you'd like to look at that and take over it's maintenance. Wix (wix.sf.net iirc) is Microsoft's first open source project and allows you to build MSI files from XML code. It will also decompile existing MSI files which should definately give you a head start.
 
Regards, Dave.

ODBC Snapshot Installer (for Dave Page)

From
anthony.caduto@micorp.com
Date:

Dave,
I have successfully created a ODBC installer and it looks almost identical to the current MSI one.
I looked at the M$  stuff you mentioned and the learning curve is pretty high.
I created this one with Inno Setup 5 and it only took a few minutes and is much easier to use than MSI or that xml msi stuff

Like I said, I would be willing to take over creating the installer if you don't mind switching to Inno Setup.

I could also do the setup for PGAdmin III :-)

The entire setup with source is available here:

http://www.amsoftwaredesign.com/downloads/pgodbc.zip

Just to show how simple and easy Inno is to use, here is the entire setup script:

; -- psqlodbc.iss --
; Installs the PostgreSQL ODBC driver
; Installer script created by Anthony J. Caduto
[Setup]
AppName=PostgreSQL ODBC Driver
AppVerName=PostgreSQL ODBC Driver 8.0
DisableDirPage=yes
LicenseFile=lgpl.rtf
DefaultDirName={pf}\PostgreSQL ODBC Driver
DefaultGroupName=PostgreSQL ODBC Driver
Compression=lzma
SolidCompression=yes
;WindowVisible=yes
OutputBaseFilename=psqlodbc
WizardImageStretch=yes
WizardSmallImageFile=banner.bmp
WizardImageFile=screen.bmp
[Files]
Source: "psqlodbc.dll"; DestDir: "{sys}"
[Registry]
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers";ValueType: string; ValueName: "PostgreSQL";ValueData: "Installed";flags:uninsdeletevalue
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "APILevel";ValueData: "1" ;flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "ConnectFunctions";ValueData: "YYN" ;flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "Driver";ValueData: "PSQLODBC.DLL" ;flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "DriverODBCVer";ValueData: "03.00";flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "FileUsage";ValueData: "0" ;flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "Setup";ValueData: "PSQLODBC.DLL";flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: string; ValueName: "SQLLevel";ValueData: "1";flags:uninsdeletekey
Root: HKLM; Subkey:"SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL";ValueType: dword; ValueName: "UsageCount";ValueData: "00000001" ;flags:uninsdeletekey


Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management