Programmatically assign primary key linked dbf tables in MS Access

Posted on 2006-05-16
Last Modified: 2008-01-09
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?

Question by:Greenbase
    LVL 65

    Accepted Solution


    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now