[Last Call] Learn how to a build a cloud-first strategyRegister Now


Programmatically assign primary key linked dbf tables in MS Access

Posted on 2006-05-16
Medium Priority
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

rockiroads earned 1500 total points
ID: 16688659

Author Comment

ID: 16696953
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

831 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