How to set a schema search path in Postgresql using ODBC in MS Access?

Question

I am trying to do what at first glance seems pretty simple. I am trying to export a table from MS Access to a Postgresql database using an ODBC driver connection.

If you use the default settings for the driver, the location of the table will be in the "public" scheme. I have this kit read-only for security reasons. I get this error when trying to start export:

When I saw the error, I realized my mistake that I did not want this table to be created in this scheme anyway. The question then became, how to specify a specific scheme for export using ODBC?

After much searching, I found this answer about setting up the search path: stack overflow

Then I tried to add this as Connection Setting to the advanced options of MS Access export windows.

When I do this and try to run, I get the following error:

Based on everything I read, it looks like it should work. The only thing I can think of is that I have a syntax that is incorrect for Connection Setting . If this is not the case, do I need to execute the answer to which I refer and set the default scheme every time I want to do such an export?

I saw a couple of questions discussing this, but they just set the search_path parameter, but that’s all. There is no discussion of syntax.

Answer 1

The problem, I thought, came down to a syntax error. Of course, the error caused by MS Access does not say anything about this, but it is.

Here is a line capture that works. Pay attention to the semicolon ; at the end of the SET search_path .