Insert values in PostgreSQL database from VBA MS-ACCESS

Question

I've seen this topic before How to insert values into the database table using VBA in MS access But it didn't solve my issue. I'm creating a form to insert values into a table in postgresql that contains arrays [] text. The user must select the items to add and then execute the SQL statement by clicking a button. So I first create a button and a text box to load the items to add into the array, here is the code

Private Sub cmdSig_Click()
 Dim registros As String
 registros = "'" & [memo] & "'" & ", "
 [resu] = registros & [resu]
End Sub

Then I create the button to execute the SQL INSERT INTO statement. Here is the code:

    Private Sub cmdPruebas_Click()
    Dim strsql As String, resultado As String, salida As String

        Dim db As Database
        dbconnect = "ODBC;DRIVER=PostgreSQL ANSI;UID=***;PWD=***;PORT=5432;SERVER=***.***.**.**;DATABASE=somedb;DSN=C:\***"
        Set db = OpenDatabase("", False, False, dbconnect)
        resultado = [resu]

        strsql = "insert into prueba_arrays (tipo_intervencion) values "
        salida = "(array[" & resultado & "]);"




        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql & salida
        DoCmd.SetWarnings True
End Sub

If I execute the result in PostgreSQL like "Insert into sometable (array_field) values (array['value1','value2','value3']);" it works fine. But in MS-Access it says: Error 3075 'execution time' Missing operator in the query 'array['value1','value2','value3']'. Any idea of what's happening? Thanks in advance!

Andre Andre · Accepted Answer · 2016-07-20T08:44:01

DoCmd.RunSQL runs the SQL string through the Access Database Engine. There [..] is interpreted as parameter, therefore the error message.

You have opened a db connection to the PostgreSQL database, but you don't use it.

It might be as simple as changing

DoCmd.RunSQL strsql & salida

to

db.Execute strsql & salida, dbSQLPassThrough

With a Pass-Through query the SQL string is passed directly to the database.

Insert values in PostgreSQL database from VBA MS-ACCESS

2 Answers