CODEWITHSUNDEEP
vbapostgresqlms-access-2010dao
Gregory West
Gregory West

Reputation: 149

Access 2010 accessing PostgreSQL can not update

I have an access program that I am using that needs to access some data on our PostgreSQL server. The problem I am having is that the link is being created read-only. If I use the menu items in access to create the links, then it works fine. My end users will be using the runtime access so do not have access to the menu.

Here is the code that is doing the linking:

Sub createLinkedTable_PostgreSQL(PostgreSQL_tableName As String)
    Dim cdb As DAO.Database, tbd As DAO.TableDef
    Set cdb = CurrentDb
    Set tbd = New DAO.TableDef

    tbd.Connect = "ODBC;Driver={PostgreSQL ANSI};Server=10.10.10.200;Port=5432;Database=BIO-Static;" & _
    "UID=UserID;PWD=Password;"
    tbd.SourceTableName = PostgreSQL_tableName

    tbd.Name = Replace(PostgreSQL_tableName, ".", "_", 1, -1, vbTextCompare)
    ' e.g. "public.table1"->"public_table1"

    cdb.TableDefs.Append tbd
    Set tbd = Nothing
    Set cdb = Nothing
End Sub

How do I get this to open the table in an update mode?

Upvotes: 2

Views: 403

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123779

When creating an ODBC linked table, Access will use the primary key from the remote table if it has one. However, if

  • the remote table does not have a primary key, or
  • you are linking to an unindexed view in the remote database

then the ODBC linked table will have no primary key. In that case you will be able to read from the ODBC linked table but you will not be able to update it.

The possible solutions are:

  1. Add a primary key to the remote table and then re-create the ODBC linked table, or
  2. Create the ODBC linked table and then use CREATE UNIQUE INDEX ... to give it a primary key, as explained in the related answer here

Upvotes: 1

Related Questions