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 ?

2
  • Have you tried adding the new record and then setting the values as shown here - stackoverflow.com/a/2297013/478884 ? Commented Nov 27, 2022 at 17:06
  • Yes I tried the different solutions as proposed in stackoverflow.com/a/2297013/478884 with the same error. I even tried first test.AddNewwhich works and adds a row with Null values, and then test.Fields("description").value = "test_descr" which fails with the same error. Commented Nov 27, 2022 at 17:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.