How to recreate MS Access primary keys and indexes in PG

This exercise is a continuation of our How to bulk export tables from MS Access. Now while this approach will work for other databases besides PostgreSQL, you'll probably need to fiddle with the subroutine to make it work for some other databases. PostgreSQL is fairly ANSI-SQL standard so not too much fiddling should be required to port to SQL Server, MySQL, Oracle etc.

Unlike the prior Visual basic subroutine we showed that exports the tables, this just creates an SQL script that you run on the already created PostgreSQL database that contains the exported data. We didn't test the quote option though we coded it in the subroutine, since like we said we hate having to quote fields. If perchance you are one of those folks that likes to put spaces in your field names to make it more englishy, then you'll need to quote or revise the other routine to convert your spaces to _ or some other thing.

Generate SQL script to make the keys

This routine should be saved in a module in the database you exported data from similar to the other routine. It creates a file on the C drive. Fill free to rename or even revise the sub routine to take it in as an argument.

Sub buildkeysInPG(Optional quoteFields As Boolean = False)
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim ndx As DAO.Index
    Dim strSQL As String
    Dim strFlds 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 path to where you want script generated
    Set f = fs.CreateTextFile("C:\pgindexes.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
            'Indexes and Primary Keys
            strSQL = ""
            For Each ndx In tdf.Indexes
                If ndx.Primary Then
                    '-- MS Access has a habit of calling all primary keys PrimaryKey 
                    '-- if you do not explicitly name them
                    '-- and key and index names have to be 
                    '-- unique in PostgreSQL within each schema
                    '-- so lets just name it after the table: pk_tablename
                     strSQL = "ALTER TABLE " & delimStart & tdf.Name & delimEnd & " ADD CONSTRAINT " & _
                        delimStart & "pk_" & tdf.Name & delimEnd & " PRIMARY KEY"
                Else
                    If ndx.Unique Then
                        strSQL = "CREATE UNIQUE INDEX "
                    Else
                        strSQL = "CREATE INDEX "
                    End If
                    '-- Get rid of spaces in index names replace with underscore
                    '-- , prefix table name in index name
                    '-- because names need to be unique in postgresql
                    strSQL = strSQL & delimStart & _
                        "idx_" & tdf.Name & "_" & Replace(ndx.Name, " ", "_") & delimEnd & " ON " & _
                            delimStart & tdf.Name & delimEnd
                End If

                strSQL = strSQL & " ("

                strFlds = ""

                For Each fld In ndx.Fields
                    '-- Loop thru the fields of index so this handles compound keys
                    strFlds = strFlds & "," & delimStart & fld.Name & delimEnd
                Next

                '-- Get rid of dangling , we introduced
                strSQL = strSQL & Mid(strFlds, 2) & ") "
                
                f.WriteLine strSQL & ";" & vbCrLf
            Next
        End If
    Next

    f.Close
End Sub