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