CODEWITHSUNDEEP
ms-accessvba
A Hughes
A Hughes

Reputation: 265

Creating Primary Key across Multiple Access Tables

I have recently imported over 40 excel files into Access, but now I want to add a PK to each.

I have the following code, but it doesn't go past the db.execute line, I keep getting error 3291 syntax error in CREATE INDEX statement. It looks ok but not sure why this is happening?

Public Sub AddPrimaryKey()

Dim db As DAO.Database
Dim td As DAO.TableDef



Set db = CurrentDb()

    For Each td In db.TableDefs

    If Left(td.Name, 6) = "REPORT" Then

    db.Execute "CREATE INDEX Employee No ON td.Name (Employee No) WITH PRIMARY"


    End If

Next td


End Sub

Upvotes: 1

Views: 252

Answers (1)

HansUp
HansUp

Reputation: 97101

Seems the field name includes a space, so bracket it.

Add the value of td.Name into the string.

db.Execute "CREATE INDEX [Employee No] ON " & td.Name & _
    " ([Employee No]) WITH PRIMARY"

Upvotes: 1

Related Questions