Re: Semi-unable to add new records to table--primary key needed?

On 12/21/19 6:40 PM, Boylan, Ross wrote:
>> From: Adrian Klaver <adrian.klaver@aklaver.com>
>> Sent: Saturday, December 21, 2019 3:37 PM
> 
>> This might be easier to figure out if you outline what is going on:
> 
> Since I seem to have gone on in my responses, let me do one-line answers before the fuller ones.
> 
>> 1) The purpose of the migration?
> 
> Primarily to use currently supported software.  Secondarily to improve data integrity, security and auditability.
> 
>> 2) A general sense of what the application is and what it does.
> 
> A GUI for managing medical specimens and associated information for multiple research studies.
> 
> 
>> 3) Have you looked at the Relations tab in Access to see what if any relationships are there?
> 
> Yes and yes.  The migration program doesn't currently use that information, and there are some complexities.
> 
> 
> Now the more elaborate answers:
> 
>> 1) The purpose of the migration?
> 
> The immediate purpose of the migration is to use software that is supported.  The application currently runs on
Windows7, Office 2010 32 bit.  As of Jan 14 we have to be off Win 7 because the University says so (because MS said so)
andwill be disabling such systems.  Office 2010, even now, can't be installed because of licensing.  We have to use Win
10,Office 2016 (even though 2019 is available).  We can do either 32 or 64 bit office and decided to target 64 bit.
 
> 
> We currently use Access's split database configuration, meaning the "backend" is a file on a shared drive.
> 
> Since we had to go to the pain of migrating anyway, this seemed a good time to switch to a server-based backend.
Althoughthe Access  split configuration has worked, having multiple users touching the same file always makes me
uncomfortable,and a real database server would seem to offer better assurances of data integrity, security, and
auditability. Since the databases store sensitive medical information, these are concerns not only for us but for our
fundersand other oversight bodies.  Historically, the requirements have gotten increasingly stringent, and it seems to
methere is some possibility that the Access "backend" will fall short of the requirements in the future.
 
> 
> Another consideration is that MS is increasingly deemphasizing using Access as a data store.  Of course, they want
peopleto go to MS SQL Server.  When I visited MS's web page for Access 2016 I couldn't find any statement that it could
beused without a server-based backed, even though it can.  But depending on a feature that's getting so studiously
ignoredseems risky.
 
> 
> Finally, I had some really bad experiences--that is, lost a day--trying to get queries to work that wouldn't, because
MSAccess SQL just isn't quite SQL.  I was hoping to avoid that in the future.
 
> 
> Because of the time pressure, we'll be sticking with the file-based backend for now.

Alright this is the part where I got confused. I think what is going on is:

1) The immediate change is going to be to Access 2016 on Windows 10 64 
keeping the data in Access files(.accdb)

2) The long term plan is to move the data from the Access files to a 
stand alone SQL server, presumably Postgres. You are looking for 
assistance in converting Access tables and relationships to Postgres.

3) At some point the GUI will point to the data in the server instead of 
in the Access files.

Is any of the above correct?

More below.

> 
> The front-end application (described next) is built on Access and is fairly substantial; migrating it to another
platformseems not worth it.
 
> 
>> 2) A general sense of what the application is and what it does.
> 
> The application is a GUI for relatively non-computer-technical users.  They run medical research studies, and each
timesomeone comes in various tests are performed and recorded, and specimens collected.  Other health-related
informationis also collected.  The core function is the management of biological specimens that result.
 
> 
> We also serve as a repository for specimens collected at other sites.  There are various types of specimens and
variousprocedures that can be performed on each.
 
> 
> Researchers then query the database by outlining what kind of specimens they want and getting a list of specimens.
Usuallythey do it by asking me, and I do the queries.
 
> 
> The actual amount of data is not trivial, but is not that large by current standards.  The file-based backends are
around20MB (after a compact and repair), and the largest tables have around 100K records.  I don't think there's
anythingthere that requires us to use 64 bits.  The data are very valuable, in that they represent over a decade's
work,lots of $ of effort, and without them the physical specimens would be essentially useless.
 
> 
> The number of users, esp simultaneous users, is also relatively small, around 10.
> 
> 
>> 3) Have you looked at the Relations tab in Access to see what if any relationships are there?
> 
> Yes, but the export program doesn't :)  The relations tab documents many, but not all, of the relations in the
database. The relations are also a little tricky because sometimes the lack of a relation should not be considered
disqualifyingfor a specimen.  Simple example: freezer type is an id to be looked up in a small table of freezer type
idsand their names.  If the freezer type is missing or nonsense, we may still want the sample.  That can be expressed
asa left join; the "Access SQL is not SQL" problems centered on left joins.
 

Postgres is going to be stricter about this. Access has the concept of 
suggested relationships that are not enforced:

https://support.office.com/en-us/article/Guide-to-table-relationships-30446197-4fbe-457b-b992-2f6fb812b58f

"A table relationship is represented by a relationship line drawn 
between tables in the Relationships window. A relationship that does not 
enforce referential integrity appears as a thin line between the common 
fields supporting the relationship. When you select the relationship by 
clicking its line, the line thickens to indicate it is selected. If you 
enforce referential integrity for this relationship, the line appears 
thicker at each end. In addition, the number 1 appears over the thick 
portion of the line on one side of the relationship, and the infinity 
symbol (∞) appears over the thick portion of the line on the other side."

Postgres Foreign Key relationships either exist or they don't. Now you 
can employee 'cheats' with you own triggers, but that is another subject.

As to your freezer example:

create table freezer_info(freezer_id int PRIMARY KEY);
create table sample_tbl(sample_id serial PRIMARY KEY, freezer_fk int 
REFERENCES freezer_info ON UPDATE CASCADE);

insert into freezer_info values (1), (2);
select * from freezer_info ;
  freezer_id
------------
           1
           2
insert into sample_tbl(freezer_fk) values (1), (null);
select * from sample_tbl;
  sample_id | freezer_fk
-----------+------------
          1 |          1
          2 |       NULL

The above will not cover the case where freezer_fk is a nonsense 
value(no relation to freezer_info). In that situation you would probably 
need to change the nonsense value to NULL or some placeholder value.


> 
> Ross
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com