Setting a primary key in code

Question

I have a database that needs to have changes made to the structure and data. There is already data in the table.

I also want the process to be as automatic as possible.

This is the process:

1. export the existing data

2)Rename the existing table

3. Delete the renamed table

4)Create the new table with the new structure.

5. Import the modified data records.

6)If no errors, delete the renamed table

7)If errors delete the new table and rename the renamed table to the original name

I have done this in Visual Basic(6) and it works great

However in Access 2000 I cannot find a way to indicate the Key field. In VB6 it is "primary_key" and I can set it to true

How do I set this in Access?

Any help would be appreciated.

John Crawford

Answer 1

Bill,

I agree that DDL is fast and efficient.  The only problem is that documentation (complete) and functional examples are difficult to find.  Many examples that I have found online, do not work and become impossible to troubleshoot.  MS' own site has very little information beyond a very basic example or 2.

I think it is much easier to find complexe VBA example, which at least work, rather than DDL examples.

Answer 2

Bill:

    Thanks.

    We do what has historically worked. Not that it is the best, and as things change we need to be open to those changes.

    The code that this is based on came from VB6 in about 1996. A lot has happened since then.

    However, that being said, what I produced SHOULD work. I want to learn why it does not.

    AllowZeroLength is a valid property for Field. So .AllowZeroLength = True (or False) should not cause an error.

    Changing Key to MyKey makes no difference.

    I still do not know what is missing so I cannot add the table.

    I do appreciate your suggestion. It does work. As with all learning I need to understand your code.

    John

Answer 3

John

I'll never understand why anyone would use DAO to create a table when DDL is simple and quick.

For one thing, Key is a reserved word. The creation of the table is failing because of that. Change it to MyKey or something equally safe.

Dim strSQL As String

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Const conPropName = "AllowZeroLength"

'Use ADO to create table

strSQL = "CREATE TABLE MyTable(" _

& "MyKey int IDENTITY(1,1) Primary Key," _

& "Data varchar(25)" _

& ")"

CurrentProject.Connection.Execute strSQL

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")

Set fld = tdf.Fields("Data")

fld.Properties(conPropName) = True

Set fld = Nothing

Set tdf = Nothing

Set db = Nothing

Answer 4

Daniel

    There is a lot of code involved with the VB6 solution.

    I have, however tried to create some code in Access to:

a) create a table in an existing database

b) Add a Key field

c) add a text field

d) add the table.

This is the code:

---

Public Function CreateTable(TableName As String) As Boolean

  Dim DB As Database

  Dim IDX As Index

  Dim TBL As TableDef

  Dim FLD As field

  Set DB = DBEngine.Workspaces(0).Databases(0)      'Get correct database

  Set TBL = DB.CreateTableDef(TableName)

    With TBL

     'Create the index

     Set IDX = .CreateIndex("Index")

      With IDX

        .CreateField ("Key")

        .Primary = True

        .Unique = True

      End With

      .Indexes.Append IDX       'Add the index

      .Indexes.Refresh

      'Create the key field

      Set FLD = .CreateField("Key", vbLong)

      'Set the values

      With FLD

        .Required = True

        .ValidationRule = ""

        .ValidationText = ""

        .Attributes = dbAutoIncrField

      End With

      'Add the field

      .Fields.Append FLD

      'Create another field

      Set FLD = .CreateField("Data", vbString, 25)

      'Set the values

      With FLD

        .Required = True

        '.AllowZeroLength = True        'Why doesn't this work (Invalid operation)?

        .ValidationRule = ""

        .ValidationText = ""

      End With

      'Add the field

      .Fields.Append FLD

    End With

  'Now add the table

  DB.TableDefs.Append TBL           'Fails (Invalid argument)

  DB.Close

End Function

---

The TableName passed is "Dummy"

I had two problems with this code.

1. Trying to set "AllowZeroLength" caused an error. I do not think that this should fail since it is a string
2. Trying to add the table definition caused an error.

This code should be enough to show what I am trying to accomplish. It is very simple, just 2 fields. But if it works for 2 it will work for as many as needed.

Thanks for looking into this

    John

Answer 5

You'd have to show us some of your existing code and where exactly you are trying to designate the primary key, then we should be able to better guide you to success, but not knowing how you are even approaching this task makes it impossible for us to advise you.