Re: Issue with Save and Release points

Thanks Mike, 

This is vba for Word, not sure if that makes any difference. Just tried rewriting the query using querydefs (as
demonstratedbelow), issued the bulk sql insert statement as strSql but am still receiving 3137 Missing semicolon (;) at
endof SQL statement.
 


This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private
information.If you have received it in error, please notify the sender immediately and delete the original. Any other
useof the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its
affiliates,including e-mail and instant messaging (including content), may be scanned by our systems for the purposes
ofinformation security and assessment of internal compliance with Accenture policy.
 

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered
addressat 30 Fenchurch Street, London EC3M 3BD.
 



-----Original Message-----
From: Relyea, Mike [mailto:Mike.Relyea@xerox.com] 
Sent: 22 June 2016 14:53
To: pgsql-odbc@postgresql.org
Cc: Machet, Daniel <daniel.machet@accenture.com>
Subject: RE: [ODBC] Issue with Save and Release points

>> On a separate note – another way to tackle this might be to issue 
>> bulk insert statements but my approach so far has been to issue bulk 
>> statements using the postgres syntax from vba
>>
>> i.e. to populate a string with an insert statement like
>> 
>> INSERT INTO tblA(field1, field2) VALUES (1, 999), (2, 888);
>> 
>> And to issue via the driver using following:
>> 
>> ThisDB.Execute strSQL, dbFailOnError
>> 
>> 
>> ThisDB is created using :
>> 
>> Set ThisDB = OpenDatabase("", False, False, DBName)
>> 
>> But I keep getting a syntax error for missing ; even though this 
>> exact query works on pgAdmin
>
> OpenDatabase() seems a DAO method.
> Unfortunately it seems DAO( or Access) doesn't allow to insert multiple rows.
>
> regards,
> Hiroshi Inoue

Daniel,

If this is an Access limitation, you may be able to work around it by creating a pass-through query to run your SQL.  I
usethis function to create a pass-through on the fly.  I then run the query and delete it when I'm done.
 

Function DefineQuery(strName As String, _
                    strConnect As String, _
                    intTimeout As Integer, _
                    strSql As String, _
                    boolReturnsRecords As Boolean _
                    )
'A function to create a query given the listed parameters On Error GoTo ErrorHandler Dim db As dao.Database Dim qrydef
Asdao.QueryDef Dim StsBar As Variant
 

Set db = CurrentDb

StsBar = SysCmd(acSysCmdSetStatus, "Defining the query...")

db.QueryDefs.Delete (strName) 'Delete the query first if it exists 'Create the query
create_query:
Set qrydef = db.CreateQueryDef(strName)
    qrydef.Connect = strConnect
    qrydef.ODBCTimeout = intTimeout
    qrydef.sql = strSql
    qrydef.ReturnsRecords = boolReturnsRecords

StsBar = SysCmd(acSysCmdClearStatus)
    
ErrorHandler:
Select Case Err.Number
    Case 0
        Err.Clear
    Case 2501
        Err.Clear
    Case 3125
        MsgBox "The query name " & strName & " is not valid.  Make sure it does not contain any punctuation and is not
longerthan 64 characters."
 
    Case 3141
        MsgBox "I couldn't define the query."
    Case 3265
        Err.Clear
        GoTo create_query
    Case 3151
        MsgBox "Connection to database was lost.  Please close and reopen this program."
    Case 3359
        MsgBox "I couldn't create the query properly.  Please close and reopen this program."
    Case Else
        Dim test As Variant
        Dim strCommand As String
        strCommand = "Define Query"
        test = EmailError(Err.Number, Err.Description, strCommand) End Select End Function


Mike