problem connecting postgresql 9.0 tables from vba

Started by Adriano V. Autinoover 14 years ago3 messagesgeneral
Jump to latest
#1Adriano V. Autino
andromedasrl.com@gmail.com

I develop a software product in msAccess, connecting a shared database
on SQL Server, on a MS Windows Server machine.

Recently i decided to enhance my product, porting the database to
Postgresql 9.0, on an Ubuntu 10.0 Server machine.

My software works on the db mainly connecting tables via ODBC, on the LAN.

Here comes the problem.

If I make a table connection using the Access connection wizard it works.
But this is not enough for my application, since it dynamically connect
different schemas tables, according to the project the user wants to open.

Therefore I use a docmd.transferdatabase acLink method, that works
perfectly on SQL Server.

Trying to connect Postgresql tables, having solved:
- the postgresql server start,
- listening on the proper port,
- allowing clients on the ip network to acceed,
- authentication problems
(using the MS Administrative Tools ODBC the connection is succesful),
the connection fails, with "3021 Error: no current record".

I tried closing the table's name within quotes, but nothing changes

Some of the connection strings that i tried:

conneTest = "ODBC;DRIVER={PostgreSQL ODBC
Driver(UNICODE)};DATABASE=dbname;" & _
"SERVER=VM-UBUSRV-09;PORT=5432;UID=dbuser;pwd=dbpassword;"
conneTest = conneTest +
"A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _

"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;" & _
"C0=0;C1=0;C2=dd_"

conneTest =
"DSN=PostgreSQL30;DATABASE=dbname;SERVER=VM-UBUSRV-09;PORT=5432" & _

"CA=d;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;;CX=1b502bb;A1=7.4"

conneTest =
"ODBC;DSN=PostgreSQL30;DATABASE=SSMPTE_;SERVER=VM-UBUSRV-09;PORT=5432;UID=postgres;;"
& _
"SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIn"

DoCmd.TransferDatabase acLink, _
"ODBC", _
conneTest, _
acTable, _
"TABLE_NAME", _
"TABLE_NAME"

Thank you so much in advance for any suggestion.
Adriano V. Autino
*Andromeda Systems Engineering, LLC*
] CEO-CTO - http://www.andromedasystemsengineering.com/
] Space Renaissance Initiative - President -
http://www.spacerenaissance.org/
] skype : adriano_autino - linkedin:
http://www.linkedin.com/in/adrianoautino
] tel. : +39.0161.433688 - cell. +39.335.8244435
Information reserved to the recipients. If you got this message wrong
please notice to the sender.
This e-mail has been scanned for any known virus by AVG Antivirus.

#2Craig Ringer
craig@2ndquadrant.com
In reply to: Adriano V. Autino (#1)
Re: problem connecting postgresql 9.0 tables from vba

On 09/17/2011 03:51 PM, Adriano V. Autino wrote:

Therefore I use a docmd.transferdatabase acLink method, that works
perfectly on SQL Server.

When you say it fails: What's the full, exact text of the error message?

What SQL, if any, is sent to the server? (Enable "mylog" on the ODBC
driver and/or enable query logging on the server).

--
Craig Ringer

#3c k
shreeseva.learning@gmail.com
In reply to: Craig Ringer (#2)
Re: problem connecting postgresql 9.0 tables from vba

why didn't you use
Set TD = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append TD

to link to a new table and then do your work.

C P Kulkarni

On Sun, Sep 18, 2011 at 4:32 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

Show quoted text

On 09/17/2011 03:51 PM, Adriano V. Autino wrote:

Therefore I use a docmd.transferdatabase acLink method, that works
perfectly on SQL Server.

When you say it fails: What's the full, exact text of the error message?

What SQL, if any, is sent to the server? (Enable "mylog" on the ODBC driver
and/or enable query logging on the server).

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general&lt;http://www.postgresql.org/mailpref/pgsql-general&gt;