0

I'm trying to connect to a postgres database with adodb so that to get data within a recordset and then update this data.

On postgres side :

create table test (description varchar) ;

I establish the connection to the database :

dim test_conn As ADODB.Connection
dim test_str As String

Set test_conn = New ADODB.Connection
If test_conn.State = 0 Then
    test_str = "PROVIDER=MSDASQL.1;DSN=PostgreSQL35W" _
             & ";DATABASE=database_name" _
             & ";SERVER=database_server" _
             & ";PORT=database_port" _
             & ";UID=user_name" _
             & ";PWD=user_pwd"
    test_conn.CursorLocation = adUseServer
    test_conn.Mode = adModeReadWrite
    test_conn.Open test_str
End If

Then I open the recordset :

Dim test As ADODB.Recordset

Set test = New ADODB.Recordset
test.Open "select * from test", test_conn, adOpenDynamic, adLockOptimistic, adCmdText

Then I check if the recorset is updateable :

test.Supports(adAddNew) => True
test.Supports(adUpdate) => True
test.Supports(adDelete) => True

But when I try to add a new row in the recordset :

test.AddNew "description", "test_descr"

I systematically get the error :

Run-time error '-2147217887 (80040e21)':
Multiple-step operation generated errors. Check each status value.

Do you know where I'm wrong / I missed something ?

Edouard
  • 6,577
  • 1
  • 9
  • 20
  • Have you tried adding the new record and then setting the values as shown here - https://stackoverflow.com/a/2297013/478884 ? – Tim Williams Nov 27 '22 at 17:06
  • Yes I tried the different solutions as proposed in [stackoverflow.com/a/2297013/478884](https://stackoverflow.com/a/2297013/478884) with the same error. I even tried first `test.AddNew`which works and adds a row with Null values, and then `test.Fields("description").value = "test_descr"` which fails with the same error. – Edouard Nov 27 '22 at 17:23

0 Answers0