June 13th, 2002, 09:26 AM
Linked Tables in Postgres ?
My company has MS ACCESS database. We are in a process of moving from MS ACCESS to Postgresql. However MS Access database is still used partly. I have a table in Ms Access which gets updated every hour. I want the data in this table to be reflected in a similar table within the Postgresql database, so that the moment this table gets updated, the change is also reflected in the postgres database table . Now, Linked table seems to be an answer, but does postgres have anything called Linked Tables and if yes, how do i create Linked Tables in the Postgres ?????
Your help would be greatly appreciated
June 13th, 2002, 10:45 AM
No, PostgreSQL does not have linked tables. Access is both a database and an application front-end. It is that application front-end that does the linking, not the internal Access database.
But, you can do things the other way around. If you have ODBC set up for PostgreSQL, then you can just export that Access table to PostgreSQL, and then have Access link out to that PostgreSQL table. This will be transparent to Access, but will make the data automatically be available in PostgreSQL.
If this is not an option, then you will have to do some sort of programming, to regularly send updates to the PostgreSQL database from the Access database.
June 13th, 2002, 03:55 PM
Macro to export table
Hey thank you Rycamore,
Now I have written a macro in Ms Access that exports the table to Postgres database (as per ur suggestion I did use ODBC) . But for a macro to execute properly - it requires the destination table name to be hardcoded. So if the name of the table in Access is order_file, the macro will execute fine for the first time. As soon as there is anohter update with the Access table in an hour - I would try to execute the same macro - however this time it will fail since the table / relation with name order_file already exists in the postgres database. So i m kinda stuck with te problem, i have to manaully go and drop the table before I run the macro. This defeats the whole purpose of automation since i have to check whether the order_file table has been updated every hour -If yes, I have to manually drop the table in Postgres and then run the macro.
Do you have any workaround so that the old order_file table within postgres automatically gets dropped and the macro is able to run successfully evertime.
Your help would be apreciated
June 13th, 2002, 06:26 PM
I will have to think about it a bit more. I'm sure there is a way to export to a table as an update, rather than as a new table.
I'm not at my Windows workstation right now, but I seem to remember something: when you are getting ready to export an Access dataset, the final dialog box has a button at the bottom that lets you set further options to the export. (somewhere in there, there is a button with further options). If you find that button, I think you might find a choice in there that allows you to overwrite a table, or just add updated records.
If this does not work, then your best bet is to simply export the table to PostgrkeSQL once, and then drop the table in Access, and replace it by connecting to the PostgreSQL table as a linked table. Then you don't need any macros to update PostgreSQL.
June 13th, 2002, 07:43 PM
but you can link postgres tables same way you link tables from mdb/mde/msSQL, so no problem there, you can do queries on them.. We are working on migration too, and trying to decide wether to go with linking or just with pure code.
another good thing is that postgres supports auto Id in much better way than access. ie if you enter null or "" it will work as auto id, if you specify id it will keep it