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
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 = """"
delimStart = ""
delimEnd = ""
Set db = CurrentDb
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile("C:\pgsequences.sql")
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
For Each fld In tdf.Fields
strFlds = strFlds & ",[" & fld.Name & "] "
strSQL = ""
If fld.Type = dbLong Then
If (fld.Attributes And dbAutoIncrField) = 0& Then
strSQL = "CREATE SEQUENCE " & delimStart & tdf.Name & "_" & fld.Name & "_seq" & delimEnd & _
" START " & (Nz(DMax(fld.Name, tdf.Name), 0) + 1) & "; "
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