How do I escape database table and column names for PostgreSQL in VBA?

Question

I'm trying to migrate an Access database to a PostgreSQL DB, and lots of table names or column names have space or number, for instance, table name: "test history" and "123 people", and column name: "test history". I'm keeping getting SQL syntax errors because of these spaces and numbers.

 'create tables in PostgreSQL database
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
        Dim strCreateTable As String
        strCreateTable = "CREATE TABLE " &  tdf.Name  & " ("
        For Each fld In tdf.fields
            strCreateTable = strCreateTable &  fld.Name  & " " & GetPostgreSQLDataType(fld.Type) & ","
        Next fld
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1) & ")"
        'MsgBox strCreateTable
        cnn.Execute strCreateTable
    End If
Next tdf

Just wondering if there is a way to transfer the output of table name and column name to a string.

[Postgres uses double-quotes `"` to escape object identifiers like table-names and column-names](https://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres). — Dai, Mar 07 '23 at 00:03
I've tried to put " " in the code, but the output becomes "CREATE TABLE tdf.Name". — Shen super, Mar 07 '23 at 00:09

Dai · Accepted Answer · 2023-03-07T01:52:52.550

I'm trying to migrate an Access database to a PostgreSQL DB

Good _{(I'm of the opinion MS Access needs to die a dignified death instead of circling the drain for another 20 years...)}

and lots of table names or column names have space or number, for instance, table name: "test history" and "123 people", and column name: "test history". I'm keeping getting SQL syntax errors because of these spaces and numbers.

PostgreSQL follows ANSI/ISO SQL so it uses double-quotes " to delimit object-identifiers, whereas Access (aka JET Red and ACE) uses square-brackets [].

So it's just a matter of ensuring object-identifiers in your SQL strings are delimited with " within the VBA Strings - which isn't that bad as VBA uses "" within a string as an escape-sequence for double-quotes.

So this should work for you (I've also added some additional whitespace and NULL/NOT NULL` handling too):

Sub GenerateAndExecuteCreateTableStatements()

    ' Create tables in PostgreSQL database
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
        
            Dim createStmt As String
            createStmt = GetCreateTableSql(tdf)

            ' Consider using Debug.Print instead of MsgBox to dump variables:
            Debug.Print createStmt
            cnn.Execute createStmt

        End If
    Next tdf

End Sub

Function GetCreateTableSql(tbl As DAO.TableDef)

    Dim createStmt As String
    createStmt = "CREATE TABLE """ &  tbl.Name  & """ ("
    
    Dim fld As DAO.Field
    Dim i As Integer
    i = 0
    For Each fld In tbl.Fields

        Dim column As String
        column = vbTab & """" & fld.Name & """" & " " & GetPostgreSQLDataType(fld.Type) & Iif(fld.Required, " NOT NULL", " NULL")

        If i > 0 Then
            column = "," & vbCrLf & column
        End If
        i = i + 1

        createStmt = createStmt & column
     Next
     
    ' Add closing parenthesis:
    createStmt = createStmt & vbCrLf & ")"

    ' Return it:
    GetCreateTableSql = createStmt

End Function

Thanks mate, but it's giving me an error message: runtime error 424 "object required". — Shen super, Mar 07 '23 at 00:59
@Shensuper I've corrected my answer - it's because I renamed `tdf` to `tbl` for clarity. — Dai, Mar 07 '23 at 01:34
Thanks, but somehow the tailing comma is still there, here is one of the output： CREATE TABLE "Treatment" ( "TreatmentName" VARCHAR(255) NULL, "WitholdingPeriod" SMALLINT NULL, "ExportSlaughterInterval" SMALLINT NULL, "BatchNo" VARCHAR(255) NULL, ) — Shen super, Mar 07 '23 at 01:47
@Shensuper Try again now - I've changed the code that added commas and newlines between columns. — Dai, Mar 07 '23 at 01:54

How do I escape database table and column names for PostgreSQL in VBA?

1 Answers1