Cannot Create Index On Given Field Error

How To Get Around The Cannot Create Index On Given Field Error

In this post I am going to show you a way to get around the “Cannot Create Index On Given Field Error”

If you got this error before, I am sure you are frustrated.

I am working on upgrading an Access database to SQL Server. There are a lot of tables, but for this post I’m only going to focus on one:

I called it “tblPrimaryPortfolioObjective”

Here it is after I used the SQL Server Data Migration Utility (SQL Server Import and Export Wizard):

Since these Access tables have spaces in their field names, I normalize the naming of the table when I migrate it, and then use a SQL View with the Access table name (with spaces), so I can link back to Access.

When we try to link from the SQL Server table (view) to the Access Database:

Since the table has only 1 field and has a field property setting that MS Access does not like. So if we select it, we’ll get an error:

So here is how you can fix it

We need to add another field that can be unique (like an “id” field), so we need to add one:

First add this statement to a new query in the SQL SMS:

ALTER TABLE dbo.tblPrimaryPortfolioObjective
ADD ID INT IDENTITY

Now press F5 to run the query.

If you did it correctly, you’ll see that it was successful.



Now a field called “ID” has been added to the table.

I probably know what you’re thinking, “Well why not just add the field in design view?”

Well, here’s the reason, you’ll probably get this message:

So you need to use a SQL statement to add a new field.

ALTER TABLE dbo.tblPrimaryPortfolioObjective
ADD ID INT IDENTITY

Then use the New Query SQL editor to make the new field “ID”, the PK field:

ALTER TABLE dbo.tblPrimaryPortfolioObjective
ADD CONSTRAINT PK_tblPrimaryPortfolioObjective
PRIMARY KEY(ID)

Now you added your field:

Now, do the import again:

After you select to use the “ID” field as your unique field, you can add the new linked table and add new rows that autoincrement:

Have any questions?

Facebooktwittergoogle_plusredditpinterestlinkedinmail
Tags: , , , , , , , , , , , , , ,
 
Next Post
sql server import table from access database
MS Access SQL Server

How To Import An Access Database Table Data To SQL server