I am about to start working on a project that requires the database to be loaded in MS Access 2003 (I think it uses JET db). I can't stand the idea of using the MS Access gui/interface and was wondering if it were at all possible to build the database in Postgres and port it to MS Access or output an
.mdb that I could load on windows. From my searches, it seems there are lots of MS Acess to Postgres migration tutorials/facilities but I couldn't find anything going in the opposite direction.
How can I build my db in Postgres and output some kind of dump file that will load up in MS Access?
Install the PostgreSQL ODBC driver on your Windows machine.
Create a DSN which points to your PostgreSQL database.
Start an Access session and use the DSN to import the PostgreSQL tables.
I've done this a few times in the past and found it quick and easy. Access with the ODBC driver will translate the PostgreSQL column data types to Access-compatible types.
This approach should work for simple tables. However if your design uses advanced PostgreSQL features, those may not translate well (or at all) to Access.
However, since you haven't created the database yet, I encourage you to tolerate the Access table design GUI. It seems simpler to me to design the database in Access in the first place.