1. Timelines > The long term solution is? I just meant I'm hoping to switch to Postgres soonish. > So at some point you will switch the link from being Access native to a ODBC DSN pointing at a Postgres database, correct? The switch will be to ODBC but no DSN. You can specify the connection properties directly: https://docs.microsoft.com/en-us/office/troubleshoot/access/create-dsn-less-connection-linkted-table. Switching to ODBC proved the most challenging part of the transition so far, but it's done. I don't mean that going DSN-less was challenging; I mean switching to ODBC was challenging. For the gory details: https://social.msdn.microsoft.com/Forums/en-US/eea6a780-488a-4154-97ac-ae318a870993/unable-to-relink-tables?forum=accessdev This was something that should have worked through the Access GUI (at least using a DSN), and in fact MS's documentation said it would work. The actual Linked Table Manager did not behave as documented. 2. Case Sensitivity > How about: > test=# select 'abc123' ilike 'ABC123'; That would work, but it depends on finding and rewriting all the places such a search is made. I would also have to remember to do it whenever I did a search. Hence my interest in a more wholesale solution. 3. "Relations" I wrote >>> That raises one other question: what is the relation between the relations expressed on the server via FOREIGN KEY declarations and relations known to Access? At first blush, they are completely different. In Access a relation is an object that lives on the front-end, while the server relations obviously live on the server. I don't know if Access, perhaps via the ODBC spec, does anything to guarantee their consistency. Clearly since Access relations can have "suggested relations" which have no backend equivalent, the 2 sets of relations cannot in general be mirror images. You replied: > Trying to parse the above out: > 1) When you say relations you mean Foreign Key or equivalent, correct? > Just confirming as relation is often used to mean table. Correct: I do not mean a table when discussing relations above. I do mean either a foreign key relationship or a Relation object as defined in Access (represented in the Access GUI on the relationship tab). > 2) As I remember it an 'enforced relationship' in Access is actually a FK, whereas one that is not is just stored as metadata somewhere. That raises the possibility that some of the Access Relation objects are constructed dynamically from info on foreign keys in the backend. If that's the case it would diminish the coordination problems. > 3) Are you are asking whether it possible for a table in an Access table can have a direct relationship with a table stored in Postgres and connected via ODBC? If so my answer is that I am not sure. I believe you can use the Relations tab to set up a faux relationship, but that will only be honored in the context of Access and only if you want it to. No, not my question. My concern is that, since there are two different representations of the the relations in the data, one on the server expressed as foreign keys and one on the frontend/Access as Relation objects, they could be out of sync. > 4) You can set up an actual FK on the server between server tables, with the data clean up caveats you mention below. Yes. > 5) At this point it seems your data is going to live in two worlds and in the end it will fall on you to maintain the integrity between the worlds. That's what I'm afraid of. The data itself lives only on the backend, but information about it lives in both places. I'm not sure that the relationship synchronization issue is limited to cases with a server backend. Even the file based data, managed directly by Access, has foreign key relations in it (I'm pretty sure), and those too could be out of sync with the Relation objects describing that same data. I also probably need a better understanding of when operations happen in Access SQL vs being passed through to the server. Ross ________________________________________ From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx> Sent: Sunday, December 22, 2019 2:23 PM To: Boylan, Ross; Ron; pgsql-general@xxxxxxxxxxxxxxxxxxxx Subject: Re: Semi-unable to add new records to table--primary key needed? On 12/22/19 11:59 AM, Boylan, Ross wrote: >> From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx> > > Sent: Sunday, December 22, 2019 10:35 AM > ..... >> 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? > > Basically, yes. > 1) The current file is in the older Access .mdb format, and will stay that way for the immediate conversion to Win 10/Access 2016. > 2) I'd like to go to Postgres medium term. I have converted the Access data to Postgres via an Access/VBA program that writes out SQL files. It clearly needs some work. The long term solution is? More answers inline below. > I think I may have everything I need to tweak it. > 3) Basically yes, though in detail the GUI and the code behind it directly use "linked tables" that are local to the front end. These linked tables are the Access equivalent of symlinks to a particular table on a particular database. They will use the PG ODBC driver to do the talking. The app uses those linked tables now, but the link simply points to the backend file on the disk. So at some point you will switch the link from being Access native to a ODBC DSN pointing at a Postgres database, correct? > > One other issue came up in testing: searches against the Access backend are case insensitive, while searches against the PG backend are case-sensitive. It would be nice to make them case-insensitive, and I've been reading about PG collation options to do so. > > Case-sensitivity is completely distinct from all the other issues I've discussed. The sense was also that the problem/difference was not a show-stopper. Current behavior is that if you search for id abc123 it will retrieve an id stored as ABC123. With the PG backend, searching on abc123 retrieves nothing. How about: test=# select 'abc123' ilike 'ABC123'; ?column? ---------- t (1 row) > >> More below. > >>>> 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 disqualifying for a specimen. Simple example: freezer type is an id to be looked up in a small table of freezer type ids and their names. If the freezer type is missing or nonsense, we may still want the sample. That can be expressed as a 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://urldefense.proofpoint.com/v2/url?u=https-3A__support.office.com_en-2Dus_article_Guide-2Dto-2Dtable-2Drelationships-2D30446197-2D4fbe-2D457b-2Db992-2D2f6fb812b58f&d=DwIDaQ&c=iORugZls2LlYyCAZRB3XLg&r=nh70E5-mX2XsDe5lrDDMt_ZRqqGMcdTmTrRLvNmttYA&m=qa1d513KOtyWtV0u2K81rduJ0TiRaucF3gbnjVTESkc&s=F10pVnItttte6dDN-4R4t13xDrJr8zAoIp63mrYNMh0&e= > > Thanks for the pointer. I'm not sure if we're using any of those suggested relationships, but we're definitely using relationships that one infers only by looking at the SQL code. In other words, queries link tables by fields that are not mentioned on the relationships tab. > > ...... > >> 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); > > The direct translation of our current app would be to omit the foreign key relation in the table definition, but to have queries that include a left join from sample_tbl to freezer_info. > > That raises one other question: what is the relation between the relations expressed on the server via FOREIGN KEY declarations and relations known to Access? At first blush, they are completely different. In Access a relation is an object that lives on the front-end, while the server relations obviously live on the server. I don't know if Access, perhaps via the ODBC spec, does anything to guarantee their consistency. Clearly since Access relations can have "suggested relations" which have no backend equivalent, the 2 sets of relations cannot in general be mirror images. Trying to parse the above out: 1) When you say relations you mean Foreign Key or equivalent, correct? Just confirming as relation is often used to mean table. 2) As I remember it an 'enforced relationship' in Access is actually a FK, whereas one that is not is just stored as metadata somewhere. 3) Are you are asking whether it possible for a table in an Access table can have a direct relationship with a table stored in Postgres and connected via ODBC? If so my answer is that I am not sure. I believe you can use the Relations tab to set up a faux relationship, but that will only be honored in the context of Access and only if you want it to. 4) You can set up an actual FK on the server between server tables, with the data clean up caveats you mention below. 5) At this point it seems your data is going to live in two worlds and in the end it will fall on you to maintain the integrity between the worlds. > > Identifying and enforcing all foreign key relations, is something I'm planning to defer til after the migration to PG. There is a noticeable amount of funky stuff in the data, which could use some housekeeping regardless of platform. The cleanup is not likely to be simple. > > One of the long-run benefits of using a database server is that it should limit the possibilities for funkiness by enforcing referential integrity and properly cleaning up after incomplete operations, instead of leaving pieces of them in the database. But we can't enforce referential integrity until our data exhibit it! And the transactional integrity probably depends more on proper coding on the front-end app than the choice of backend; file-based Access backends do support transactions. > > Ross > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx
