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 1r83fo-0081hF-W7 for pgsql-odbc@arkaria.postgresql.org; Tue, 28 Nov 2023 19:23:09 +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 1r83fk-007EPA-Cw for pgsql-odbc@arkaria.postgresql.org; Tue, 28 Nov 2023 19: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 1r83fj-007EP0-TD for pgsql-odbc@lists.postgresql.org; Tue, 28 Nov 2023 19:23:04 +0000 Received: from mail-il1-x12a.google.com ([2607:f8b0:4864:20::12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r83fc-007obX-Gn for pgsql-odbc@lists.postgresql.org; Tue, 28 Nov 2023 19:23:02 +0000 Received: by mail-il1-x12a.google.com with SMTP id e9e14a558f8ab-35d2621aa26so2159035ab.3 for ; Tue, 28 Nov 2023 11:22:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1701199375; x=1701804175; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=DT2erIqkJi3gjKa9VhQ43G1PNSK5OsIRrSHAOiqoDOA=; b=Y483pbq6aQHn9Rish7ndJ4sx8s/Y+TnSgHJJiAghOHpQs5vvtkx/NbqR5I0HbIs2qm v6pjqPLPUB+QPH47L8yO1uY3z+VEgKKbGcJdp9SJ704Uxb5dqsFi9V26YsKWbFpTWyGq i2NJW5h6NyLpskFtatJ/hiTRU8Qch8EYouKG581heiSaJTDvSdnhOCKpYUNAp7G6pS4i Um1v6kjx6hwIfjFUlMVoqGDLXzxjnHSMaFb3lXnQ03b8Y7j9xWQD2falkfFQg1ayM5rM amerSGb1avacj58jfS+mc6ztZEXgn6mqOROiUCl5CW0SwnQbuFzWpuK64gM5YHCveO8y nX+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1701199375; x=1701804175; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=DT2erIqkJi3gjKa9VhQ43G1PNSK5OsIRrSHAOiqoDOA=; b=voFZ3pDQgFUKVePiygvwPBatzQtWi2PLjwL6M1H6/QHdLc0O5IzmXO1AGot5QkmRbH jNdOG+g9Oy6MlzuKsbaGssdY3YFbQhamNPz1zRArADHB4GIAmm2unq4FFJ0xPNTFr8Sx 9IRNKHLGiFaRnNGcvyUBHyp0RJVNgy6kbgYoLHOAUnIKA49DGM3RNbhX1R4HWCs/iamO BNVqVFidiDQkLOuJQChRA/aVdVlKEQrnL11Zzd8MjmNoCRE2bDo12lKEBW5PQ/ACCoea wKrhHp815DlWxW91L8E7pppuhKpryQ8yW/IAUDbsrWx9mCjOnrKZxPiX8THvkMfGg8il 7+YQ== X-Gm-Message-State: AOJu0YyrP69HGy+IfsK54p5Akfjz3ybYenvqrbIqCimMUsoxK1QlVNUP Tw9443/zh3exPLN7I00Ko951aWMnw70= X-Google-Smtp-Source: AGHT+IGrOjFM2Cbj+yj6fgRkRWNy2uL/s+hEpfBYMxSvXWoekqJwAiOddBIntKy97ZQUeQ1kspuiJg== X-Received: by 2002:a05:6e02:1c0d:b0:35c:c4ea:f3d with SMTP id l13-20020a056e021c0d00b0035cc4ea0f3dmr9619645ilh.4.1701199375024; Tue, 28 Nov 2023 11:22:55 -0800 (PST) Received: from [10.10.50.239] (72-46-51-178.lnk.ne.static.allophone.net. [72.46.51.178]) by smtp.gmail.com with ESMTPSA id z1-20020a029381000000b0046403a50eb2sm3124089jah.134.2023.11.28.11.22.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 28 Nov 2023 11:22:54 -0800 (PST) Content-Type: multipart/alternative; boundary="------------2xmLaY804YF0ClLtqhnkMmCQ" Message-ID: Date: Tue, 28 Nov 2023 13:22:54 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.0.2 Subject: Re: MS Access connection and insert issues Content-Language: en-US To: pgsql-odbc@lists.postgresql.org References: From: Brad White In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------2xmLaY804YF0ClLtqhnkMmCQ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/26/2023 7:55 PM, Evan Hallein wrote: > 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 > successfully, however there are some (related?) issues. Using version > 16 of the 64bit 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 table manager in Access always shows this same connection > string options: DRIVER={PostgreSQL > Unicode(x64)};DATABASE=wamtram;SERVER=127.0.0.1;PORT=5432;UID=###;PWD=###;CA=d;A6={};A7=100;B0=254;B1=8190;BI=0;C2=;D6=-101;CX=1c3810489;A1=7.4 The first obvious thing is that the connection string shouldn't have {brackets}. They are just placeholders that should be replaced by the actual values. Do you need the dynamic approach, or is the current linked table sufficient? For example, I read from a config file. If the string matches, all good, but if not, reset the connection and restart the app. Clearly easier if you can just set it and be done. If there are connection issues, you can enable logging and that can sometimes help debug the issue. Set B2 and B3 each to 1. You may have to search for the logs. I've oddly had it show up in different locations on different machines. But check under your user folder first. Root of C second. > > I've also tried using a DSN, but the same issue happens. I didn't prove this with extensive testing, but it appeared to me that it used the settings in the connection string first, and then any settings from the DSN as defaults. Even when I wasn't using a DSN. A little confusing when we got different results on different machines. > > The second issue is to do with inserting records and getting an > autoincrement ID back from the new record.  The autoincremented > ENTRY_BATCH_ID 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".  If hard code an ENTRY_BATCH_ID  it > works. This is working with SQLServer. I agree with Jan. You will have to re-read the record to get any values set on the server. Dates can be an issue if you have any default date values. In the example shown, where you are setting the entry date from the client side, you shouldn't have any issues. > > This is the VBA Code: > -------------------- > ' Set the connections >     Set MainConn = CurrentProject.Connection >     UserConn.Open "Provider=Microsoft Office 12.0 Access Database > Engine OLE DB Provider;Data Source=" & strFileName I didn't have any luck setting the connection string and then using it without restarting the app. It was very confusing because it would seem to go back and forth between working and not working until I finally saw the pattern. >   ' Start transaction >     MainConn.BeginTrans > >     ' Add a record for the batch >     MainRs.Open "TRT_ENTRY_BATCHES", MainConn, adOpenDynamic, > adLockOptimistic >     MainRs.AddNew > MainRs!FILENAME = strFileName > MainRs!ENTRY_DATE = Date   ' UserRs!ENTRY_DATE > > MainRs.Update > lngEntryBatchID = MainRs!ENTRY_BATCH_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_BATCHES" > ( >     "ENTRY_BATCH_ID" integer NOT NULL DEFAULT > nextval('"TRT_ENTRY_BATCHES_ENTRY_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 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 > 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, please notify the sender immediately by replying to > this message, then delete it from your system./ -- Quote Signature I talk with clients, find out where their pain points are, and solve those. On-call IT Management for small companies and non-profits. SCP, Inc. bwhite@inebraska.com 402-601-7990 Quote of the Day    The Tenth Commandment [thou shalt not covet] sends a message to    collectivists, to people who believe wealth is best obtained by    redistribution. And the message is clear and concise: Go to hell.     -- P. J. O'Rourke --------------2xmLaY804YF0ClLtqhnkMmCQ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/26/2023 7:55 PM, Evan Hallein wrote:
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 successfully, however there are some (related?) issues. Using version 16 of the 64bit 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 table manager in Access always shows this same connection string options: DRIVER={PostgreSQL Unicode(x64)};DATABASE=wamtram;SERVER=127.0.0.1;PORT=5432;UID=###;PWD=###;CA=d;A6={};A7=100;B0=254;B1=8190;BI=0;C2=;D6=-101;CX=1c3810489;A1=7.4

The first obvious thing is that the connection string shouldn't have {brackets}. They are just placeholders that should be replaced by the actual values.


Do you need the dynamic approach, or is the current linked table sufficient?

For example, I read from a config file. If the string matches, all good, but if not, reset the connection and restart the app.

Clearly easier if you can just set it and be done.


If there are connection issues, you can enable logging and that can sometimes help debug the issue.

Set B2 and B3 each to 1.

You may have to search for the logs. I've oddly had it show up in different locations on different machines.

But check under your user folder first. Root of C second.


I've also tried using a DSN, but the same issue happens.
I didn't prove this with extensive testing, but it appeared to me that it used the settings in the connection string first, and then any settings from the DSN as defaults.
Even when I wasn't using a DSN. A little confusing when we got different results on different machines.

The second issue is to do with inserting records and getting an autoincrement ID back from the new record.  The autoincremented ENTRY_BATCH_ID 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".  If hard code an ENTRY_BATCH_ID  it works. This is working with SQLServer.

I agree with Jan. You will have to re-read the record to get any values set on the server.

Dates can be an issue if you have any default date values.

In the example shown, where you are setting the entry date from the client side, you shouldn't have any issues.


This is the VBA Code:
--------------------
' Set the connections
    Set MainConn = CurrentProject.Connection
    UserConn.Open "Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;Data Source=" & strFileName

I didn't have any luck setting the connection string and then using it without restarting the app.

It was very confusing because it would seem to go back and forth between working and not working until I finally saw the pattern.

    
  ' Start transaction
    MainConn.BeginTrans

    ' Add a record for the batch
    MainRs.Open "TRT_ENTRY_BATCHES", MainConn, adOpenDynamic, adLockOptimistic
    MainRs.AddNew
   
    MainRs!FILENAME = strFileName  
    MainRs!ENTRY_DATE = Date   ' UserRs!ENTRY_DATE

    MainRs.Update
    lngEntryBatchID = MainRs!ENTRY_BATCH_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_BATCHES"
(
    "ENTRY_BATCH_ID" integer NOT NULL DEFAULT nextval('"TRT_ENTRY_BATCHES_ENTRY_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 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 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, please notify the sender immediately by replying to this message, then delete it from your system.
--
Quote Signature I talk with clients, find out where their pain points are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite@inebraska.com
402-601-7990


 Quote of the Day
   The Tenth Commandment [thou shalt not covet] sends a message to
   collectivists, to people who believe wealth is best obtained by
   redistribution. And the message is clear and concise: Go to hell.
    -- P. J. O'Rourke
--------------2xmLaY804YF0ClLtqhnkMmCQ--