MS Access connection and insert issues
| От | Evan Hallein |
|---|---|
| Тема | MS Access connection and insert issues |
| Дата | |
| Msg-id | SYBPR01MB404338E9F19D153179B69FABF5BDA@SYBPR01MB4043.ausprd01.prod.outlook.com обсуждение исходный текст |
| Ответы |
RE: MS Access connection and insert issues
("Wal, Jan Tjalling van der" <jan_tjalling.vanderwal@wur.nl>)
Re: MS Access connection and insert issues (Brad White <b55white@gmail.com>) |
| Список | 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)
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
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.
В списке pgsql-odbc по дате отправления:
Следующее
От: "Wal, Jan Tjalling van der"Дата:
Сообщение: RE: MS Access connection and insert issues