How to map MS Access auto number to PostgreSQL serial

This exercise is a continuation of our How to bulk export tables from MS Access and How to recreate MS Access primary keys and indexes in PostgreSQL. As mentioned in the first article, when you use the built-in export feature of MS Access, it exports autonumber fields as integers instead of the more appropriate PostgreSQL pseudo serial type.

The serial type in PostgreSQL is not really a type though, so this behavior is expected. The serial type is really short-hand for defining an integer column and a corresponding sequence object with a specific naming convention and setting the default value to the next value of the sequence. So this is what we will do in this exercise after we have already exported our data to PostgreSQL. The script we are about to demonstrate will generate an sQL script containing all the CREATE SEQUENCE, and ALTER TABLE ALTER COLUMN SET DEFAULT .. needed to convert our integer column to a serial column.

As a side note, Mark mentioned a similar approach to what we've been describing here, but builds all the table , key structures utilizing python. You can check out the python script at http://code.activestate.com/recipes/52267-reverse-engineer-ms-accessjet-databases

Generate CREATE SEQUENCE ALTER TABLE from MS Access table definition


Sub buildsequencesInPG(Optional quoteFields As Boolean = False)
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    
    Dim fs, f
    Dim delimStart As String
    Dim delimEnd As String
    
    If quoteFields Then
        delimStart = """"
        delimEnd = """"
    Else
        delimStart = ""
        delimEnd = ""
    
    End If

    Set db = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    '-- change this to where you want the file created
    Set f = fs.CreateTextFile("C:\pgsequences.sql")

    For Each tdf In db.TableDefs
        '-- No system tables or temp tables
        If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
            '-- loop thru field set of table looking for autonumber fields
            For Each fld In tdf.Fields

                strFlds = strFlds & ",[" & fld.Name & "] "
                strSQL = ""

                If fld.Type = dbLong Then
                    If (fld.Attributes And dbAutoIncrField) = 0& Then
                        'it is not an autonumber field
                    Else
                        '-- Create a postgresql sequence object
                        '-- with start being one after the max value of the current column value
                        '-- we name the sequence in convention tablename_field_seq
                        '-- so that PostgreSQL will display as a serial
                        strSQL = "CREATE SEQUENCE " & delimStart & tdf.Name & "_" & fld.Name & "_seq" & delimEnd & _
                            " START " & (Nz(DMax(fld.Name, tdf.Name), 0) + 1) & "; "
                        ' -- set table column default to next value of sequence
                        ' -- this effectively makes it an autonumber
                        strSQL = strSQL & "ALTER TABLE " & delimStart & tdf.Name & delimEnd & _
                            " ALTER COLUMN " & delimStart & fld.Name & delimEnd & _
                            " SET DEFAULT nextval('" & delimStart & tdf.Name & "_" & fld.Name & "_seq" & delimEnd & "'::regclass);"
                        f.WriteLine strSQL & vbCrLf
                    End If
                End If

            Next

        End If
    Next
    f.Close
End Sub