From: | "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com> |
---|---|
To: | "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org> |
Cc: | "daniel(dot)machet(at)accenture(dot)com" <daniel(dot)machet(at)accenture(dot)com> |
Subject: | Re: Issue with Save and Release points |
Date: | 2016-06-22 13:53:04 |
Message-ID: | 91A46FD6517A7941B6309C2F7CDD238F31505A3F@USA7109MB022.na.xerox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
>> 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 use this 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 As dao.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 longer than 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
From | Date | Subject | |
---|---|---|---|
Next Message | George Weaver | 2016-06-22 18:50:32 | Re: Multilevel inserts issue with ODBC |
Previous Message | Adrian Klaver | 2016-06-22 13:21:54 | Re: Multilevel inserts issue with ODBC |