2

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?

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Gregory West
  • 149
  • 1
  • 9
  • Does this help? Not sure, but it may be a start: http://support.microsoft.com/kb/276035 – Joe Love Nov 30 '14 at 06:07
  • possible duplicate of [VBA Code to Add Linked Table with Primary Key](http://stackoverflow.com/questions/9602687/vba-code-to-add-linked-table-with-primary-key) – Gord Thompson Nov 30 '14 at 16:19
  • Thanks! Looks like it is a Primary Key issue. When I add a PK to the table it works just fine. – Gregory West Dec 01 '14 at 06:51

1 Answers1

1

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
Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418