postgres to MS Access

Question

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?

Answer 1

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.

Answer 2

You don't need to be stuck using the Access GUI table design tools. You can actually write a SQL 'script' (a semi-colon-separated list of SQL DDL commands), save it somewhere in your filesystem and then use a little bit of VBA to execute the script in Access and build up all your tables, views (queries), indexes and other constraints.

Original idea here: https://stackoverflow.com/a/1839290/20371, but I have modified the VBA a bit to my liking:

Public Sub ExecSqlScript(fileName As String)
  intF = FreeFile()

  Open fileName For Input As #intF
  strSql = Input(LOF(intF), #intF)
  Close intF

  strSql = Replace(Replace(strSql, Chr(10), " "), Chr(13), " ")
  vSql = Split(strSql, "; ")

  On Error GoTo MessageError
  For Each s In vSql
    s = Trim(s)
    If Left(s, 2) <> "--" Then

      Debug.Print "Execute: " & s
      Debug.Print
      CurrentProject.Connection.Execute s
    End If
  Next

  Exit Sub
MessageError:
  Debug.Print "ERROR: " & Err.Description
  Debug.Print
  Resume Next
End Sub

So with the above you can spread out each statement over as many lines as you like; and just put a -- in front of the first line of any statement to 'comment it out'.

Using this, you then build up a script to iteratively design your database: just drop/delete and re-create your tables, views, indexes etc. as many times as you need to. Access ANSI-92 SQL, which is what the above VBA will accept, has its own syntax variations just like most other SQLs. I've put up a sample database creation script for you to get the hang of it: https://gist.github.com/yawaramin/6802876