Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7YVB-005oFe-Ht for pgsql-odbc@arkaria.postgresql.org; Mon, 27 Nov 2023 10:06:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r7YV9-00CQuA-MC for pgsql-odbc@arkaria.postgresql.org; Mon, 27 Nov 2023 10:06:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7Qqs-00A5Pf-Bs for pgsql-odbc@lists.postgresql.org; Mon, 27 Nov 2023 01:55:58 +0000 Received: from mail-me3aus01on2138.outbound.protection.outlook.com ([40.107.108.138] helo=AUS01-ME3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7Qqo-008hzG-54 for pgsql-odbc@postgresql.org; Mon, 27 Nov 2023 01:55:56 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=hxY5kSDePaueZRy+ZgnbQaBuNbb+n/qg2qMUhv/F5uYDtPW14HjFzaE9sULiDN2YhF7c5X/DRq+ZzkpvTSQdnU/7+WZmNeaQVf965o6nzeB8yODnYKuHy0XQnjUQFdEXQa4TblJgwPBjQ5BbzLAvABuOr9q6Ns075DV4tlpNim6bAhES3alOwXOp0Xrhif5Go3e36jiv669CcPPjDkHzJFVWl4mR260BaHCyiqElZiyW6dpLVoEK+yI1LcvjxkOaY5sVQ2BPPx03/rbDa4UAs/SvX/mdp6oU+N/Cd+OsZO+G3+NTNvX+jw4cu6ONUIn7jS8XcVIK/1cyn7ks7YIv7w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=Ooo46afkBUYz/Q7QGqTZZboXdxTePXFsh2U/LlgzyJs=; b=YkoHs6OwCgrt5eBiU20JwjB99motohJLqSI1QHrrdUS3Xj6fuv3Em6aMCy2GbFMRBWmZ7Z9/2ADiQKD2fEqdIEqaWydOfhZnwsgQ72JlHxRijRqOwgrxwjbwj+KDdN98l4XBdw/pz47SDi+q2/2RJjDX6OZvFp5nFiBUo9zzmwXvcBqvbkGmcHjBl7JFmf/NWWsPhRwwR7NIL5ycURzVpB779ZeJqBloFPbrl7J5Y2VFCuKOmAIn5sfl3ajoslVhWVpHBeB/QkeoKq1UxQWu7WHAVzA7vfK9GgcBgyb+UC9S4ticVNs06t7dBdDsC7PaO39yBrmMhVuqZDo6fqiSSQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=dbca.wa.gov.au; dmarc=pass action=none header.from=dbca.wa.gov.au; dkim=pass header.d=dbca.wa.gov.au; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dbca.wa.gov.au; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=Ooo46afkBUYz/Q7QGqTZZboXdxTePXFsh2U/LlgzyJs=; b=Jz4nAIRbtED8gp+6JiKNKjCJEAURyddFz4Ub26awodAnbMqJc/kihmNItGVFsnT/5yDaA3h6/1hu10ejZupkl5hSHyTuXT8rhU+b7fyA2V4ZehJS7WGGzRySkmxIHJC0VLEAEAKbhofAfJ1gIxMjjj1UWjWU5jqTUUZrELPIlI0QiNWaOFbKSkCuwv0hrcLxqoz/KUyRC24rdljVHYEGZTbBR/ao85SitKnCq80l5iwLY0AaeOm8VTp7ZKJ2R6z/ahiS/IEFkhLhJRhSZZoL8xy4avkVyTt/UXVGfniH3Kk3dgyR1i07U6P2UWgnny+O/AB1ogZZ7ygZTEtloL7orA== Received: from SYBPR01MB4043.ausprd01.prod.outlook.com (2603:10c6:10:6::19) by SY7PR01MB8479.ausprd01.prod.outlook.com (2603:10c6:10:1f2::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7025.27; Mon, 27 Nov 2023 01:55:45 +0000 Received: from SYBPR01MB4043.ausprd01.prod.outlook.com ([fe80::a263:443:a38b:4c83]) by SYBPR01MB4043.ausprd01.prod.outlook.com ([fe80::a263:443:a38b:4c83%4]) with mapi id 15.20.7025.022; Mon, 27 Nov 2023 01:55:45 +0000 From: Evan Hallein To: "pgsql-odbc@postgresql.org" Subject: MS Access connection and insert issues Thread-Topic: MS Access connection and insert issues Thread-Index: AQHaINBCeKafx/BMAE2hrfaWmCtetQ== Date: Mon, 27 Nov 2023 01:55:45 +0000 Message-ID: Accept-Language: en-AU, en-US Content-Language: en-AU X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=dbca.wa.gov.au; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SYBPR01MB4043:EE_|SY7PR01MB8479:EE_ x-ms-office365-filtering-correlation-id: 0e35c2f0-5e14-4cff-9504-08dbeeebf858 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: ufrQ1/3otIBgJA5OfxNfjtL3AqqkIdC4C2YDIoMDpjFMdjId/SJdq+6DNsrIpTLPtl5qJ1899j2SpWyFitOd+GyEIHPrvUoNTVOuy0w9C34HY8fecF2ZxmFk62BGzjTapZ8UVk0RrUZjB8egWgcvjll48+iT09eP/GA4KrmrB2KVRbfBRmMbokDhKsSl53sTu9wzOz/x58D2uA9vdf/pPtfkWdYuU4kkUUBLQD2Lg378OKsw0NeAzBwkdLpYxgYID86wzZLnpE0eOltcadvyXC0IcNd0mB9m1Uc512dJkGxGPYxT+c7gPc00lMZLpLOfPvPvGmvcq1cXSrg98D8phh5UqcDp5TlaRWE1HAA+FuNb6P25r3pPeeVo0adzomMAstxBFbPznWUSHUW84kBEzFRs8ZNFAW//9MAVFNFmsuw91J3Q87UIVbq4sF7jx58Z5X5b0rSGdng6pY2Cx+/c8sH/t/EoaZ+zz2N5k0q7ABaCLHhCEaraZfuPTRSVzntWbX+hndPR9Ld91ylWQNny03v3NR+8dgMkivFlgq9kNngJZZGEVm8QWs7BQe8+df3mRN28n2ns1ygpYnbHNCSKJZIwQvHUesGBso/fUAIdsVY= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SYBPR01MB4043.ausprd01.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230031)(346002)(376002)(39860400002)(136003)(396003)(366004)(230922051799003)(451199024)(64100799003)(186009)(1800799012)(26005)(6506007)(7696005)(71200400001)(9686003)(52536014)(8936002)(55016003)(8676002)(21615005)(44832011)(86362001)(1015004)(5660300002)(45080400002)(478600001)(316002)(91956017)(76116006)(966005)(66946007)(66446008)(66476007)(66556008)(6916009)(64756008)(166002)(38100700002)(83380400001)(19627405001)(122000001)(38070700009)(2906002)(41300700001)(33656002);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?9V8QW95P3KnNIc9zee5ZhXd/7aTnOIvV8znU1FfAXwvSD+M3NOjXpMM5lp?= =?iso-8859-1?Q?RHRoSaOntDHpcJifTAJ2xdVRlWLN+nkbU70IDqzeM2szVKrLun7x6jGJMS?= =?iso-8859-1?Q?yHNFFU1FIYY5r0BPHoEY7JOQYmTkREiql6N1iVvbRifo1rAvjG1FLtOSBh?= =?iso-8859-1?Q?8lDdveXuqw8FHvbh3kviOs2n3IdvYP120MsIuEnR2pABuxX8nSv7U89Aum?= =?iso-8859-1?Q?8xG6o8JpOtcotp0CSc6nKW+Qv1m+2iRHn3oGQ01pORIVIEjdrhhd3I5tPE?= =?iso-8859-1?Q?6e23cmcD0WinqSW9YSv/L+47wV/9P8WAKrgyBuGcUfr8oRwIGACCQIcCSr?= =?iso-8859-1?Q?wrWwEE2xLshjowMM1mmJ/CmBKR+QDppDyC8GFqcKLztFBsz4HVmaAWXu+x?= =?iso-8859-1?Q?f6QnM5ozf621PGz09So3jfd8ZrsFEyBdUvt68NBJ4NzpUgooGU3B8V3ru/?= =?iso-8859-1?Q?CPzY1fWIyJnaYav6xssRJUbRuS0rElwiUM6S+7BjsY18ECnDIQ+opFDkVu?= =?iso-8859-1?Q?oK5FO7CRk1GW0mjhPT/TVv3KMBrh92mXGF9fj/zg30is1g4d/w25L2klLu?= =?iso-8859-1?Q?pt8MAWsMza6qSEIYhAAD1rsxxFVqBUSRSE+Qhb1GpzwdskHSaIFY1//Hzh?= =?iso-8859-1?Q?ynVG1sXUqPgLRb3yiQgwsffaojRhYQmliDtd4hPMzdA7YWXBNNMyG4ETCH?= =?iso-8859-1?Q?r0PoQT/brJ2I522O5UGqmFr8lYb5WcSG7uVNSpdyj9mQgQGW5dOgQ7vuNe?= =?iso-8859-1?Q?lB4GW5aJ7HEib+f7Zy80JEbC5ZLFC5MDsJmIAUIsHJwgZnT6dSPNd6l0g2?= =?iso-8859-1?Q?2h8HmhoXMzKaamJ0jsDSbYAYAvl27p5Jjys6VDtcVgM9NFwQEH7iFSjsvj?= =?iso-8859-1?Q?7Yf7LVxkF/OGlLzZkIdtXsmJ/xrtld/CEwPAq7qPRpc7zxe+hZ4BFhgz6S?= =?iso-8859-1?Q?w9PtNdNYG8hR6iHzdKk/AiEVwnCLn4+56aHOOC+YCmaNCEcBp0c3tORP27?= =?iso-8859-1?Q?AC7y1Crag0jdeS7UAgjny6N3st8J8xhIvWFlp5FXf9So+axOOOTTkW4Sl1?= =?iso-8859-1?Q?waoHsE5CqU4bJAvrY+EC859kMxPOKDzVD3SGnAAKF8mfxdI1zah//y7Kn4?= =?iso-8859-1?Q?kKIVno08J/a9nBeXhoEqWiEkWRHTJYvfY7kR70GXMEU7nOFx8pyw9qbss1?= =?iso-8859-1?Q?wGnmnYTsl+asq0bu67sS2HpcwN8ZXKv1LvVHYdxI+S9OPQdF+D7DGL9uRl?= =?iso-8859-1?Q?qJ9brKMgnF37SjNchiTI4HcYaee7SfWPWLz6gKoZ1TPk9erDUg8NF72lR3?= =?iso-8859-1?Q?pp8lj1hpqN0nzV/kRl1BOXS+VO+3Zl1gI+YomvLrA78jT9RhxEAz/HKs0q?= =?iso-8859-1?Q?HjK4hS8aZu1zgv6tzW6nohsxSVMQNQOjr+HYtc5mGXt7v7Zi71s+G+xVEH?= =?iso-8859-1?Q?8dMZXNxydp4nCaIUIJ5e2Hs0L4OE1Nuvj48OUld2OhxJGpGqwsTUoucTRb?= =?iso-8859-1?Q?BukwLua61I5qbWxyoJ7I3+qtNT+EqL9CD9Rjkvz5EUvXRJKfCD8552nwBF?= =?iso-8859-1?Q?y0/YyxlimHD7KAcfU4vRcFcC+aeum/99guP+PjHMR5yfi83RYZla3FN3ew?= =?iso-8859-1?Q?93c0J6dmqZ+un3hOuTfECy8pjMbMweuMnBkkjvh8J99hWwJvY4cgfYKQ?= =?iso-8859-1?Q?=3D=3D?= Content-Type: multipart/alternative; boundary="_000_SYBPR01MB404338E9F19D153179B69FABF5BDASYBPR01MB4043ausp_" MIME-Version: 1.0 X-OriginatorOrg: dbca.wa.gov.au X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SYBPR01MB4043.ausprd01.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 0e35c2f0-5e14-4cff-9504-08dbeeebf858 X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Nov 2023 01:55:45.3934 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7b934664-cdcf-4e28-a3ee-1a5bcca0a1b6 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: wJB/XV4+/9YVPPWoKOa9hTPEOSPTCW36l1l8GYPRwGnMoH6QS2m5rFSt4s7s/Un8bE8tYMSQjXZ7ohY6VcRmpUoJaoOBUxyEVMxh3L8wTG4= X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY7PR01MB8479 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SYBPR01MB404338E9F19D153179B69FABF5BDASYBPR01MB4043ausp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am trying to migrate an old MS Access application with an SQL Server back= end to PostgreSQL backend. I'm able to connect and read the data successfu= lly, however there are some (related?) issues. Using version 16 of the 64bi= t unicode ODBC driver on windows 11 with postgres 14.9 (linux) The first is that I don't seem to be able to set the ODBC driver options in= Access using a connection string, they just seemed to get ignored. I have= followed this: psqlODBC HOWTO - Access VBA (postgresql.org) It connects, but whatever config options I put get ignored and the linked t= able manager in Access always shows this same connection string options: DR= IVER=3D{PostgreSQL Unicode(x64)};DATABASE=3Dwamtram;SERVER=3D127.0.0.1;PORT= =3D5432;UID=3D###;PWD=3D###;CA=3Dd;A6=3D{};A7=3D100;B0=3D254;B1=3D8190;BI= =3D0;C2=3D;D6=3D-101;CX=3D1c3810489;A1=3D7.4 I've also tried using a DSN, but the same issue happens. The second issue is to do with inserting records and getting an autoincreme= nt ID back from the new record. The autoincremented ENTRY_BATCH_ID is alwa= ys empty after inserting. I can see the record gets added to the database, = but the record in Access in not updated with the new "ENTRY_BATCH_ID". If = hard code an ENTRY_BATCH_ID it works. This is working with SQLServer. This is the VBA Code: -------------------- ' Set the connections Set MainConn =3D CurrentProject.Connection UserConn.Open "Provider=3DMicrosoft Office 12.0 Access Database Engine = OLE DB Provider;Data Source=3D" & strFileName ' Start transaction MainConn.BeginTrans ' Add a record for the batch MainRs.Open "TRT_ENTRY_BATCHES", MainConn, adOpenDynamic, adLockOptimis= tic MainRs.AddNew MainRs!FILENAME =3D strFileName MainRs!ENTRY_DATE =3D Date ' UserRs!ENTRY_DATE MainRs.Update lngEntryBatchID =3D MainRs!ENTRY_BATCH_ID '<------------- fails here, n= o value in ID and returns "record is deleted" ------------------------------------- and this is the table schema: ------------------ CREATE TABLE IF NOT EXISTS dbo."TRT_ENTRY_BATCHES" ( "ENTRY_BATCH_ID" integer NOT NULL DEFAULT nextval('"TRT_ENTRY_BATCHES_E= NTRY_BATCH_ID_seq"'::regclass), "ENTRY_DATE" timestamp with time zone, "ENTERED_PERSON_ID" integer, "FILENAME" character varying(255) COLLATE pg_catalog."default" DEFAULT = NULL::character varying, "COMMENTS" character varying(255) COLLATE pg_catalog."default" DEFAULT = NULL::character varying, CONSTRAINT "idx_237586_PK_TRT_ENTRY_BATCHES" PRIMARY KEY ("ENTRY_BATCH_= ID") ) ------------------------------- Any ideas? Is there config that I need to set, which I can't? I'm new to V= BA, so maybe there is something I need to change? thanks -------------------------------- Evan Hallein Senior Technical Officer North West Shelf Flatback Turtle Conservation Program https://flatbacks.dbca.wa.gov.au/ Department of Biodiversity, Conservation and Attractions 17 Dick Perry Av., Kensington, WA, 6151 Mob 0419 874 211 ________________________________ This message is confidential and is intended for the recipient named above.= If you are not the intended recipient, you must not disclose, use or copy = the message or any part of it. If you received this message in error, pleas= e notify the sender immediately by replying to this message, then delete it= from your system. --_000_SYBPR01MB404338E9F19D153179B69FABF5BDASYBPR01MB4043ausp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,

I am trying to migrate an old MS Access application with an SQL Server back= end to PostgreSQL backend. I'm able to connect and read the data successfu= lly, however there are some (related?) issues. Using version 16 of the 64bi= t unicode ODBC driver on windows 11 with postgres 14.9 (linux)

The first is that I don't seem to be able to set= the ODBC driver options in Access using a connection string, they just seemed to get ignored.  = I have followed this: psqlODBC HOWTO - Access VBA (postgresql.org)
It connects, but whatever config options I put g= et ignored and the linked table manager in Access always shows this same connection string options: DRIVER=3D{Post= greSQL Unicode(x64)};DATABASE=3Dwamtram;SERVER=3D127.0.0.1;PORT=3D5432;UID= =3D###;PWD=3D###;CA=3Dd;A6=3D{};A7=3D100;B0=3D254;B1=3D8190;BI=3D0;C2=3D;D6= =3D-101;CX=3D1c3810489;A1=3D7.4

I've also tried using a DSN, but the same issue = happens.

The second issue is to do with inserting records= and getting an autoincrement ID back from the new record.  The autoincremented ENTRY_BATCH_I= D is always empty after inserting. I can see the record gets added to the= database, but the record in Access in not updated with the new "ENTRY_BATCH_ID".&= nbsp; If hard code an ENTRY_BATCH_ID  it works. This is working with SQLServer.

This is the VBA Code:
--------------------
' Set the connections
    Set MainConn =3D CurrentProject.Co= nnection
    
  ' Start transaction
    MainConn.BeginTrans

    MainRs!FILENAME =3D strFileName&nb= sp; 
    MainRs!ENTRY_DATE =3D Date   = ' UserRs!ENTRY_DATE

    MainRs.Update
    lngEntryBatchID =3D MainRs!ENTRY_B= ATCH_ID '<------------- fails here, no value in ID and returns "record is deleted"
-------------------------------------

and this is the table schema:
------------------
CREATE TABLE IF NOT EXISTS dbo."TRT_ENTRY_B= ATCHES"
    "COMMENTS" character var= ying(255) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
-------------------------------

Any ideas?  Is there config that I need to = set, which I can't? I'm new to VBA, so maybe there is something I need to change?

thanks

= --------------------------------

= Evan Hallein

= Senior Technical Officer

= North West Shelf Flatback Turtle Conservation Program

= https://flatbacks.dbca.wa.gov.au/

= Department of Biodiversity, Conservation and Attractions

= 17 Dick Perry Av., Kensington, WA, 6151

= Mob 0419 874 211

=  

This message is confidential and is intended for the recipient named abo= ve. If you are not the intended recipient, you must not disclose, use or co= py the message or any part of it. If you received this message in error, pl= ease notify the sender immediately by replying to this message, then delete it from your system. --_000_SYBPR01MB404338E9F19D153179B69FABF5BDASYBPR01MB4043ausp_--