How to specify a schema in a DoCmd.TransferDatabase command

I am writing a python script to copy MSaccess tables to Postgres. In this particular case, I'm trying to specify the schema that is being loaded in the Postgres. Most code I found here on SO just loads in generic public. I need to load specific schemas.

a = win32com.client.Dispatch("Access.Application")
a.OpenCurrentDatabase(db_path)

table_list = []

for table_info in cursor.tables(tableType='TABLE'):
    table_list.append(table_info.table_name)

print (table_list)

for table in table_list:
    logging.info(f"Exporting: {table}")

    acExport = 1
    acTable = 0

    a.DoCmd.TransferDatabase(
        acExport, 
        "ODBC Database", 
        "ODBC;DSN=PostgreSQL30;"
        f"DATABASE={db_name};"
        f"UID={pg_user};"
        f"PWD={pg_pwd};"
        f"Schema=Commercial;", 
        acTable, 
        f"{table}", 
        f"{table.lower()}"
        )

   
    logging.info(f"Finished Export of Table: {table}")
    logging.info("Creating empty table in EGDB based off of this")

My issue with this is that while I have tried Schema=Commercial and f"Commercial.{table.lower()}", the tables always land in the public schema. how do I tell the command to export to the correct schema?

Thanks

Solution

This works for me in Access VBA:

Sub pg_export()
    Dim connect As String
    connect = _
        "ODBC;" & _
        "DRIVER=PostgreSQL Unicode(x64);" & _
        "SERVER=192.168.0.199;" & _
        "DATABASE=test;" & _
        "ConnSettings=SET search_path = ""Commercial"";" & _
        "UID=scott;" & _
        "PWD=tiger;"
    DoCmd.TransferDatabase acExport, "ODBC Database", connect, acTable, "my_table", "my_table"
End Sub

The Python equivalent for assigning the connect variable would be

    connect = (
        'ODBC;'
        'DRIVER=PostgreSQL Unicode(x64);'
        'SERVER=192.168.0.199;'
        'DATABASE=test;'
        'ConnSettings=SET search_path = "Commercial";'
        'UID=scott;'
        'PWD=tiger;'
    )