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
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