Thanks again Hiroshi,
This has given me some direction, I’ll try recode to use a prepared statement (from a little googling this seems to imply using CreateQueryDef()
on vba… its for a word vba script but should be same across office)
I’ll also check if there are alternatives to DAO for opening the database connection… most likely this will be a bridge too far changewise but at least I’ll learn something
Regards
Dan
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 use of 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 of information security and assessment of internal compliance with Accenture policy.
Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.
From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: 22 June 2016 11:48
To: Machet, Daniel <daniel.machet@accenture.com>
Cc: tsunakawa.takay@jp.fujitsu.com; Ratnakar Shetty, E. <e.ratnakar.shetty@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Issue with Save and Release points
Hi Daniel,
Thanks Hiroshi,
Is there anywhere I can do some reading up about the SQLStatistics() function and how to cache its results so that ODBC doesn’t need to run these repeatedly for each statement? Will I be able to call SQLStatistics() from VBA?
One way is to PREPARE an insert statement and EXECUTE the PREPARED statement repeatedly.
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