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 · Accepted Answer · 2016-07-20 08:44:01Z

2

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.

answered Jul 20, 2016 at 8:44

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

E. Salas Over a year ago

Thanks marlan and Andre, It worked fine. Thank You very much!!

marlan · Accepted Answer · 2016-07-20 08:43:32Z

2

The line DoCmd.RunSQL strsql & salida executes the SQL it the Access currentDb. Replace that line with this:

db.Execute strsql & salida

So that the SQL gets executed from the Database object you have opened, and connected to PostgreSQL.

answered Jul 20, 2016 at 8:43

marlan

1,4851 gold badge12 silver badges18 bronze badges

2 Comments

Andre Over a year ago

Great minds think alike. :) The dbSQLPassThrough parameter may be necessary though.

marlan Over a year ago

Possibly necessary, definitely more efficient :)

Collectives™ on Stack Overflow

Insert values in PostgreSQL database from VBA MS-ACCESS

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related