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 1r7Yld-005ooe-J2 for pgsql-odbc@arkaria.postgresql.org; Mon, 27 Nov 2023 10:23: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 1r7Ylc-00CaRa-6h for pgsql-odbc@arkaria.postgresql.org; Mon, 27 Nov 2023 10:23:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7Ylb-00CaRR-Ba for pgsql-odbc@lists.postgresql.org; Mon, 27 Nov 2023 10:23:03 +0000 Received: from mail-db3eur04on071d.outbound.protection.outlook.com ([2a01:111:f400:fe0c::71d] helo=EUR04-DB3-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7YlW-007aJm-Aw for pgsql-odbc@postgresql.org; Mon, 27 Nov 2023 10:23:01 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=RHFpZkSOpRFAhvqO/wysmopPMMiVXJqpSWbxSmbMwAkXsjZbUk3jx2HpkRWxgvSDGUxj5f6IrVzOxxwQJfrlGWaU2q6Fo3w8MddIrwM1/j/OXuVrknisReaIEGLvAIqzT2AciZ2UMsug+GDVnspRaAzCsJWkbo5w/qfbF5yFdNgfPffSeYvEC28/MmfmD9ipynrzBLWaryXU1Zk9ToGfzqhpnqi8L9pS5TKqsECVun/T819SIrRfAvKgA2qlDwS3iXMKb+zHIYKixgmicc1qpVx1DjNDv/WC0RZJ+/CCmBjUZCBpdJw2Q9Mf8Q+cMHakGKWVrNG5L7gWHXb29C0Xxw== 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=fb2kf4rknaEV7wP+8E+6aGtzB1+lhSWofFLTx/hCQek=; b=UiY4kj+0i6nSRkChrVI8+YGtW4BojSS5hGmmOIHXxon7hGjuhp/iv3A/iDnKw2y0toir+vbsT7SfOtgJRURf/WCdYcty8z87CUAGVmdv61skAswvaQpMkQGlifarZNzXN9KYnig21K+AwaOLbl69ujSEmZyFu1n4LgITC9WT1FuZcqNvcylmDwWVi6SFIgyUegXUHN/InvaKsMf9/GiF/Er9m4vSJrXFv392pLoH2Kx/EGMHMZCms5jpjj0WogI7oTnAT2VEEXJxdF4yr1AjF//wMxiE15DAK6u/NvSEToFH6l+Tu1VnH0So/5tN/41pPMco36n8tVj6fHGD+E3Abg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=wur.nl; dmarc=pass action=none header.from=wur.nl; dkim=pass header.d=wur.nl; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=wur.nl; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=fb2kf4rknaEV7wP+8E+6aGtzB1+lhSWofFLTx/hCQek=; b=FwOuHQjTbW0HP7nVOfZd/YYqqG78PGHfotrB3ze9i1cKyYSdtwZwEvMk6q1pmuTP/HMKwNFY0gWDQWXQuY7r1IgAKmowvybL+yWyPE1s1xBN69bpeYtzrRYhNzHybJkxUP+ODBsWYoHxB1kykv7f9fQEvAWxXQo6r9rBcvD6i8Uxq96LHfp71AIgZZ+yK5ApqyPp3h0Wrs7Zo5GhK7MvJfcL3a2XmGDdSjGrx6KlRmddPYiFCT9S7Pu+GMwAS9oYOnre3ATaPcBYBAX1yXA66wz+RuwCV1i3chTGCIu/i8hM2sOfBZkm70bXq0Bop2G3qYv9lw1IbTbDMQ6AfgQ5pg== Received: from AM0PR01MB5634.eurprd01.prod.exchangelabs.com (2603:10a6:208:171::11) by DBAPR01MB6566.eurprd01.prod.exchangelabs.com (2603:10a6:10:185::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7025.28; Mon, 27 Nov 2023 10:22:50 +0000 Received: from AM0PR01MB5634.eurprd01.prod.exchangelabs.com ([fe80::8bc:2a73:81ae:992a]) by AM0PR01MB5634.eurprd01.prod.exchangelabs.com ([fe80::8bc:2a73:81ae:992a%3]) with mapi id 15.20.7025.022; Mon, 27 Nov 2023 10:22:50 +0000 From: "Wal, Jan Tjalling van der" To: Evan Hallein , "pgsql-odbc@postgresql.org" Subject: RE: MS Access connection and insert issues Thread-Topic: MS Access connection and insert issues Thread-Index: AQHaINBCeKafx/BMAE2hrfaWmCtetbCN8j5A Date: Mon, 27 Nov 2023 10:22:50 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US, en-NL Content-Language: en-US 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=wur.nl; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AM0PR01MB5634:EE_|DBAPR01MB6566:EE_ x-ms-office365-filtering-correlation-id: bf71bd0b-3f95-4eb0-a0ae-08dbef32cf15 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: RcW1J9e8QeyQsVO1DKUjmWc+kkLnKZWat6shluLajcPTJ8Nd0SKWo0hBvQMITvkNwo/yn2vrIYB1scBSRkKEz7Zye+S/4TAJspbeaIkDPktJGXPUOF6dhB6OkMkLp83ck0p4mTBdfb0eT9zESXclM5CBlB8XhomCHB1TiJKXT2slRjdUCG1Cr3lpWeY0VK2olHR8QhbBkU4PrIJYq6FLVbNKkaqm1YpOHhIu2CTtqLUQ0j+/DLVq0ik26f5J/pRGm4aamrncp8wSN2Bq9HDHbcEvAq96B33pgNAY1QGzAxV0CHcmDSQV+Hmx9trx2h1F3UwyhcqfO7VQhdZh2CUTGd5gP8cV4jU6Lmm6XFgg6F0+QAWpowpk6l5FxiZ1zLsgg8SAo4YlxGbTsmKUCv7jkaYqy5QMujvbr/yXmU2+lNP7nr63Rp55TU4tegmM5cRcvg2jbpMbapikyygkt0NCV46cpRO67qvL4IvAS1DwsTPGFsIXfWktSmWlyw+ZKJFWXsGOuIBuDCG28HEReOc6cC7SB6f4UrvjU4D/4FBakvW2nZZ6lAcz+3lYN4QzZGhju1Y2UcZpOTLzT38jeogwTWT5TDHq/2nT9I7z4KYyoKA= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AM0PR01MB5634.eurprd01.prod.exchangelabs.com;PTR:;CAT:NONE;SFS:(13230031)(396003)(366004)(346002)(39850400004)(136003)(376002)(230922051799003)(451199024)(186009)(64100799003)(1800799012)(52536014)(71200400001)(9326002)(45080400002)(8936002)(8676002)(9686003)(6506007)(7696005)(53546011)(66446008)(66556008)(66476007)(64756008)(66946007)(110136005)(76116006)(786003)(316002)(966005)(478600001)(2906002)(166002)(122000001)(55016003)(33656002)(38100700002)(41300700001)(38070700009)(86362001)(26005)(83380400001)(5660300002);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?P4MOcL1siO00mp7GE/MWEcuYhNcQNF6HCgXw3RQj3cmzI75KgcB0YHcM3g?= =?iso-8859-1?Q?OjXk+D/2LJWopXrASigQ5jj0DeONysGaMqWxpbW7rCRrSC81rIRTiu6LGi?= =?iso-8859-1?Q?55cJH8fJDcJYf2/W5rJWyOfGkVgjoKxCEwy800R/zm00muD185o7kOeC6H?= =?iso-8859-1?Q?c3/5V+mU6Cs2VQycWH0o8d7DvCo8tUkoiOchZpOEFwZZqHauSZr/coIZvq?= =?iso-8859-1?Q?q6kqGjD7qZfSekh8f6FCInWqiFmCSnnNt15QZphzR86xMOu76+t5P+Znkd?= =?iso-8859-1?Q?MkOz2rNME3bDqpNHMhUfoUfW75T3Oh6Qq/5hElxJe86FojH+EiNgA9k2nv?= =?iso-8859-1?Q?0IY+Mr3uMcfH/4V/LVJmpHdUo6rH593r5P7kuyJMwKzmuxsi5BRVnACI3F?= =?iso-8859-1?Q?BuUbLvj/qONV1Ejc+oG0oTxtjUZ1HVGA0mmOEyHXWQcrbE+7oHrAJPLweS?= =?iso-8859-1?Q?WMwu47Xkp0MEHQJBlfg0mi2frv6U/N7FNaIcKp/o9eV6orSnbxZc6EQXRM?= =?iso-8859-1?Q?XzYQZqEybf2aeSV8nq28GeIJfMW248owZ1OlOKwTrqXtDFf8+cKb4hbwcT?= =?iso-8859-1?Q?O+WCzne8HpZ2jVxPdR61N7WFjDluqswgx513xrZZUrHjYAGVo58g1I19dC?= =?iso-8859-1?Q?megxPWbSzFRSiYdcWVitD4hLPhFSpn1xVRKes+GVfAcket1b+qPmHUexhy?= =?iso-8859-1?Q?naFBo5ukQUXDMt06e0emQDUtM5ba7zsOcxhBVLuSCw3ZytO/mvNjNkYve7?= =?iso-8859-1?Q?6izTrM67BDrHHyK1vwRbNoUuubO6RRr22ElizKyKNbkaUmEsfGyvXRVnF+?= =?iso-8859-1?Q?uG/4kIh8DKo3RrBADhC+N2hZ2TTt3T9rxm4bM8W0pGq7nl6u+YxQiWHMOX?= =?iso-8859-1?Q?P+kqih2sOESpKIQxcDJ+KrvvjZdE/r3fsBZqQ/Yj8cVKlLoj4MkOyEfaZz?= =?iso-8859-1?Q?4Wo+Q45YN3AAH03ljWG0d8m1ai1yI7vPUvEI5Kuc2sJAr2k9KMxAgHCT0e?= =?iso-8859-1?Q?wzNL8bJZT3+qtjaZycoxP5BvgM9G6XUWjwQ2v6g3KARG+XDgZAoJTJNh+T?= =?iso-8859-1?Q?FJ24cY0OvS54g2mkzH+17bSE0ZrXxrdESSvtrl5umqwPtzCnJcAoiFW+0k?= =?iso-8859-1?Q?l8wsyCO9V4+YObVz9Lwf6LKoUDnhYiEnzKEqXqK4BUwyAiB+4npRRzzbUz?= =?iso-8859-1?Q?owN2S5NJG70MFjZ1KN7SQz4aksbJ0+lrbAEtM+qFmTP983xzu1OfoKN/Jf?= =?iso-8859-1?Q?nhuYIMwGa70svroZ4jK4H5ZGqrh5xM2z5E5YMfK2fOqxDIPJ4P0PX3K2x4?= =?iso-8859-1?Q?ba6rQ1h6nzKPs5mRBh8K21s2TqF3Q83cA6Ypr74hokpKBDGDGO8glxgd4C?= =?iso-8859-1?Q?nbUP4CoWPlfiE2oX9fzQmXoy+eN9P/J76dUFETXE96g0xNwF5GPgsHipQx?= =?iso-8859-1?Q?d+U3/dg0amFg4nbHAvEE+4d3bUBbMPIDA7/F+Z7zLNsSb151NHuwDx90KB?= =?iso-8859-1?Q?wCeQx8UXpp1l9pOaP7F/M6tX4ENxB5Y0t+RZFNNHLD2cTq56dBk3y6iNB8?= =?iso-8859-1?Q?X/nLeOcxtkiawTEA+90/oyCRAiUsbpo4BOs+HCGUD9bWgJg3lNDvuW4Kwi?= =?iso-8859-1?Q?CHOHt07qaEhIi0e4A5BIBSxRIdJqMjReduVE49Xkf++T2UcqZ1lvhe2Q?= =?iso-8859-1?Q?=3D=3D?= Content-Type: multipart/alternative; boundary="_000_AM0PR01MB5634D9FBD4CB731AE54DFF0FDDBDAAM0PR01MB5634eurp_" MIME-Version: 1.0 X-OriginatorOrg: wur.nl X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AM0PR01MB5634.eurprd01.prod.exchangelabs.com X-MS-Exchange-CrossTenant-Network-Message-Id: bf71bd0b-3f95-4eb0-a0ae-08dbef32cf15 X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Nov 2023 10:22:50.4442 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 27d137e5-761f-4dc1-af88-d26430abb18f X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: mBHiUcsTIceRVh/E+7YCtv1uxFOMW4DjhjOnDfbVDZJELLk/C6X2ZZqM0kiT0+pC+4BDAY3R1s5TP/RmZQprHEOR9sMyofPGO7O3LdgCyWM= X-MS-Exchange-Transport-CrossTenantHeadersStamped: DBAPR01MB6566 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM0PR01MB5634D9FBD4CB731AE54DFF0FDDBDAAM0PR01MB5634eurp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Evan, I work with a similar setup, with an Access-frontend to a PostgreSQL-backen= d. The config-options are unreliable as far as my experience goes, I did point= this out in the mailing-list a while back. At the time for psqlodbc 13.02 against a psql 11-server. Setting read-only is not working as an example. My work-around for that is setting up user-roles on the postgres-server. When a user opens a form I first check whether the role is read-only or has= insert/update/delete-rights. If the latter is detected, I use that to allow those actions on the form in= MS-Access. In an On-Load-action could be appropriate. You could skip setting those rights on the form, but then the ro-users may = think they can change things and try to. With appropriate roles in place on the server, it will blocked there. Resul= ting in a cryptic error message appearing in Access. That is likely to caus= e your users to get confused. So I prefer to avoid that. Access does not automatically know what happens on the server-side. Your inserted records ends-up on the server. You probably need to do a .Ref= resh() or .Requery on the form to ensure that the values from the server ar= e fetched and can be shown by Access. I often have one of these actions in= the vba-code of a form after an insert or update of a records has been don= e. BTW this is not unique to PostgreSQL, with an Oracle-backend the behaviour = is very similar. I hope these pointers help you to achieve your goals. Best regards, Jan Tjalling van der Wal Wageningen Marine Reseach (WMR) / formerly IMARES Institute for Marine Re= sources & Ecosystem Studies Ankerpark 27, 1781 AG Den Helder Postbus 57, 1780 AB Den Helder Tel. +31 (0)317-4 87147 # GSM. +31 (0)626120915 (priv= =E9) # # Ma+Di Vr 09:00-18:00, Wo XX, Do+Vr 09:00-18:00 Jan_Tjalling.vanderWal@wur.nl From: Evan Hallein Sent: Monday, November 27, 2023 2:56 AM To: pgsql-odbc@postgresql.org Subject: MS Access connection and insert issues 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_AM0PR01MB5634D9FBD4CB731AE54DFF0FDDBDAAM0PR01MB5634eurp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello Evan= ,

 = ;

I work wit= h a similar setup, with an Access-frontend to a PostgreSQL-backend.

The config= -options are unreliable as far as my experience goes, I did point this out = in the mailing-list a while back.

At the tim= e for psqlodbc 13.02 against a psql 11-server.

Setting re= ad-only is not working as an example.

 = ;

My work-ar= ound for that is setting up user-roles on the postgres-server.

When a use= r opens a form I first check whether the role is read-only or has insert/up= date/delete-rights.

If the lat= ter is detected, I use that to allow those actions on the form in MS-Access= .
In an On-Load-action could be appropriate.

 = ;

You could = skip setting those rights on the form, but then the ro-users may think they= can change things and try to.

With appro= priate roles in place on the server, it will blocked there. Resulting in a = cryptic error message appearing in Access. That is likely to cause your users to get confused. So I prefer to avoid that.<= o:p>

 = ;

Access doe= s not automatically know what happens on the server-side.=

Your inser= ted records ends-up on the server. You probably need to do a .Refresh() or = .Requery on the form to ensure that the values from the server are fetched and can be shown by Access.  I often have= one of these actions in the vba-code of a form after an insert or update o= f a records has been done.

 = ;

BTW this i= s not unique to PostgreSQL, with an Oracle-backend the behaviour is very si= milar.

 = ;

I hope the= se pointers help you to achieve your goals.

 = ;

Jan Tjalling van der Wal

 <= /span>Wageningen Marine Reseach (WMR)  / formerly IMARES Institute for Marine Resources & Eco= system Studies

Ankerpark 27, 1781 AG Den Helder  =      Postbus 57, 1780 AB Den Helder

Tel. +31 (0)317-4 87147 #                     GSM. +31 (0)626120915 (priv=E9) #

# Ma+Di Vr 09:00-18:00, Wo XX, Do+Vr 09:00-18:00=

Jan_Tjalling.vanderWal@wur.nl

From: Evan Hallein <evan.hallein@dbca.wa.gov.au>
Sent: Monday, November 27, 2023 2:56 AM
To: pgsql-odbc@postgresql.org
Subject: MS Access connection and insert issues

 

Hi,

 

I am trying to migrate an old MS Access application with a= n SQL Server back end to PostgreSQL backend. I'm able to connect and read t= he data successfully, however there are some (related?) issues. Using version 16 of the 64bit unicode ODBC driver on windows 11 wi= th 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 table manager in Access always shows this same connection s= tring options: DRIVER=3D{PostgreSQL Unicode(x64)};DATABASE=3Dwamtram;SERVER= =3D127.0.0.1;PORT=3D5432;UID=3D###;PWD=3D###;CA=3Dd;A6=3D{};A7=3D100;B0=3D2= 54;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 getti= ng an autoincrement ID back from the new record.  The autoincremented ENTRY_BATCH_ID is always empty afte= r inserting. I can see the record gets added to the database, but the recor= d 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 Off= ice 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", M= ainConn, adOpenDynamic, adLockOptimistic

    MainRs.AddNew

   

    MainRs!FILENAME =3D strFileName  <= /span>

    MainRs!ENTRY_DATE =3D Date   ' UserRs!E= NTRY_DATE

 

    MainRs.Update

    lngEntryBatchID =3D MainRs!ENTRY_BATCH_ID '&= lt;------------- fails here, no value in ID and returns "record is del= eted"

-------------------------------------

 

and this is the table schema:

------------------

CREATE TABLE IF NOT EXISTS dbo."TRT_ENTRY_BATCHES&quo= t;

(

    "ENTRY_BATCH_ID" integer NOT NULL = DEFAULT nextval('"TRT_ENTRY_BATCHES_ENTRY_BATCH_ID_seq"'::regclas= s),

    "ENTRY_DATE" timestamp with time z= one,

    "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_BAT= CHES" PRIMARY KEY ("ENTRY_BATCH_ID")

)

-------------------------------

 

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

Sen= ior Technical Officer

Nor= th West Shelf Flatback Turtle Conservation Program

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

Dep= artment of Biodiversity, Conservation and Attractions

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

Mob= 0419 874 211

&nb= sp;

This message is c= onfidential and is intended for the recipient named above. If you are not t= he intended recipient, you must not disclose, use or copy the message or an= y part of it. If you received this message in error, please notify the sender immediately by replying to this message= , then delete it from your system.

--_000_AM0PR01MB5634D9FBD4CB731AE54DFF0FDDBDAAM0PR01MB5634eurp_--