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 = """"
delimStart = ""
delimEnd = ""
Set db = CurrentDb
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile("C:\pgindexes.sql")
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
strSQL = ""
For Each ndx In tdf.Indexes
If ndx.Primary Then
strSQL = "ALTER TABLE " & delimStart & tdf.Name & delimEnd & " ADD CONSTRAINT " & _
delimStart & "pk_" & tdf.Name & delimEnd & " PRIMARY KEY"
If ndx.Unique Then
strSQL = "CREATE UNIQUE INDEX "
strSQL = "CREATE INDEX "
strSQL = strSQL & delimStart & _
"idx_" & tdf.Name & "_" & Replace(ndx.Name, " ", "_") & delimEnd & " ON " & _
delimStart & tdf.Name & delimEnd
strSQL = strSQL & " ("
strFlds = ""
For Each fld In ndx.Fields
strFlds = strFlds & "," & delimStart & fld.Name & delimEnd
strSQL = strSQL & Mid(strFlds, 2) & ") "
f.WriteLine strSQL & ";" & vbCrLf
Tracked: Jan 28, 17:56