"'PK_dbo.TableName' is not a valid name" with Access ODBC linked table to SQL Server

Question

I am trying to link an Access 2003 DB to tables in a SQL Server 2008 database using ODBC. When I try to link to tables that have Primary Key established I get the following error message.

" 'Pk_dbo.Batch_Claims' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not to long. "

Pk_dbo.Batch_Claims is the key value I see when looking at the table through SSMS. I have used Access for awhile but am somewhat new to SQL Server and connections using ODBC. Any help would be appreciated.

Thanks

Answer 1

You need to rename the primary key by opening your SQL Server (or Azure) database in SQL Server Management Studio. See the section "Working Around Invalid Primary Key Names" at this blog post: Linking Microsoft Access 2010 Tables to a SQL Azure Database

Answer 2

I have create a stored procedure what do the job for you. first we have to remove all foreign keys otherwise we can't drop the primairy key constraint. Alter that we loop to the keys drop and recreate them

Create procedure proc_changepk
As

    --first drop all references
    declare @sql nvarchar(max)
    declare cursRef cursor for
        SELECT 
        'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
        '].[' + OBJECT_NAME(parent_object_id) + 
        '] DROP CONSTRAINT [' + name + ']' as ref
        FROM sys.foreign_keys   
    open cursRef
        fetch next from cursRef into @sql
        while @@fetch_status = 0 begin
            exec(@sql)
            fetch next from cursRef into @sql
        end
    close cursRef   
    deallocate cursRef

    --drop and recreate primairy keys
    declare @pktable table (constraintname nvarchar(255),tablename nvarchar(255),colname nvarchar(255))
    insert into @pktable(constraintname,tablename,colname)
    SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1

    declare @pkname nvarchar(255),@tablename nvarchar(255),@cols nvarchar(255)

    declare Mycurs cursor for
        --maybe more than one col for primairy key
        SELECT 
        p.constraintname,p.tablename,
        STUFF((SELECT '. ' + colname from @pktable where constraintname=p.constraintname
                FOR XML PATH('')), 1, 1, '') [cols]
        FROM @pktable p
        GROUP BY constraintname,tablename

    open mycurs
        fetch next from mycurs into @pkname,@tablename,@cols
        while @@fetch_status = 0 begin
            --drop key
            set @sql='alter table ' + @tablename + ' drop CONSTRAINT [' + @pkname + ']'
            print @sql
            exec(@sql)
            --create key
            set @sql='alter table ' + @tablename + ' add CONSTRAINT [pk_' + @tablename + '] primary key NONCLUSTERED (' + ltrim(@cols) + ')'
            print @sql
            exec(@sql)

            fetch next from mycurs into @pkname,@tablename,@cols
        end
    close MyCurs    
    deallocate MyCurs

GO