public inbox for [email protected]
help / color / mirror / Atom feedMS Access connection and insert issues
3+ messages / 3 participants
[nested] [flat]
* MS Access connection and insert issues
@ 2023-11-27 01:55 Evan Hallein <[email protected]>
2023-11-27 10:22 ` RE: MS Access connection and insert issues Wal, Jan Tjalling van der <[email protected]>
2023-11-28 19:22 ` Re: MS Access connection and insert issues Brad White <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Evan Hallein @ 2023-11-27 01:55 UTC (permalink / raw)
To: pgsql-odbc
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)<https://odbc.postgresql.org/howto-accessvba.html;
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
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_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.
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
' 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: MS Access connection and insert issues
2023-11-27 01:55 MS Access connection and insert issues Evan Hallein <[email protected]>
@ 2023-11-27 10:22 ` Wal, Jan Tjalling van der <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Wal, Jan Tjalling van der @ 2023-11-27 10:22 UTC (permalink / raw)
To: Evan Hallein <[email protected]>; pgsql-odbc
Hello Evan,
I work with 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 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. 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.
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 .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 of a records has been done.
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 Resources & 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é) #
# Ma+Di Vr 09:00-18:00, Wo XX, Do+Vr 09:00-18:00
[email protected]<mailto:[email protected]>
From: Evan Hallein <[email protected]>
Sent: Monday, November 27, 2023 2:56 AM
To: [email protected]
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 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)<https://odbc.postgresql.org/howto-accessvba.html;
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
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_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.
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
' 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: MS Access connection and insert issues
2023-11-27 01:55 MS Access connection and insert issues Evan Hallein <[email protected]>
@ 2023-11-28 19:22 ` Brad White <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Brad White @ 2023-11-28 19:22 UTC (permalink / raw)
To: [email protected]
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) <https://odbc.postgresql.org/howto-accessvba.html;
> 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.
[email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-11-28 19:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-27 01:55 MS Access connection and insert issues Evan Hallein <[email protected]>
2023-11-27 10:22 ` Wal, Jan Tjalling van der <[email protected]>
2023-11-28 19:22 ` Brad White <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox