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 :
DoCmd.RunSQL runs the SQL string through the Access Database Engine. There [..] is interpreted as parameter, therefore the error message.\n\nYou have opened a db connection to the PostgreSQL database, but you don't use it.\n\nIt might be as simple as changing \n\nDoCmd.RunSQL strsql & salida\n\n\nto\n\ndb.Execute strsql & salida, dbSQLPassThrough\n\n\nWith a Pass-Through query the SQL string is passed directly to the database.",
2016-07-20T08:44:01