• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Programmatically assign primary key linked dbf tables in MS Access

How do I programmatically assign primary keys to linked dbf tables in MS Access.  

When I try to link to DBF tables in Access using TransferDatabase I am requested to select the primary keys for the table.  I do not want the users to have this responsibility and wish to assign them programmatically.

How do I do this?

1 Solution
GreenbaseAuthor Commented:
This helped point me in the right direction.  

In the end I used a different ALTER TABLE data definition query as included below:

Public Function RelinkODBCTable(DSN As String, SourceTable As String, DestTable As String, Key As String) As Boolean
    Dim TableExists As Variant
    TableExists = DLookup("[Id]", "[MSysObjects]", "[Name]='" & DestTable & "'")
    If Not IsNull(TableExists) Then DoCmd.DeleteObject acTable, DestTable
    On Error Resume Next
    DoCmd.SetWarnings = False
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & DSN, acTable, SourceTable, DestTable, False
 '-------------------------The code that does the trick is below ---------------------------------------------------------------------------------------
    Dim qdf As QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", "ALTER TABLE " & DestTable & " ADD CONSTRAINT PrimaryKey PRIMARY KEY (" & Key & ")")
    '"CREATE UNIQUE index1 On " & DestTable & " (" & Key & ")")
    Set qdf = Nothing

    DoCmd.SetWarnings = True
    RelinkODBCTable = True
End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now