Product Properties Primary Key

[jmedinger]

Hi there

Have recently upgraded from v9 to v10 and we're getting an error on startup that a table lacks a primary key and Actinic couldn't create one automatically.

The table is 'ProductProperties'

Anyone know whether this is something we can fix ourselves or what to look for in the mdb file to be able to rectify it? (We've tried setting a primary key directly in Access however it tells us it can't save the table once this is done...

[Mike Hughes]

This sounds quite complicated so I think at this stage I'd get Actinic support involved.

Mike

[jmedinger]

tbh - that's what the dialog recommends we do...

I just thought if someone here had had the same error that they may have already had a fix...

[Jan]

What error message do you get when you save? It's likely that you have some corrupt data rows in the table, if you can work out what they are then you can fix them if not you might need to get help from Actinic.

The primary key for productproperties should be on nPropertyID and should be called ProductProperties_Primary.

It's worth fixing up the keys because your system should work better and faster afterwards.

Regards,

[jmedinger]

The error we get is

Index or primary key cannot contain a null value

When I open that table in Access, the very first row doesn't have a value in either the 'sProductRef', 'nType' or 'nPropertyID' (and many others)
The second row also has inconsistencies (weird values in fields which are otherwise consistent throughout i.e. nType of 45 where nearly everything else is 4 and nPropertyID of 3801137 whereas everything else is no more than 6 digits)

I could try backing the file up, removing those rows and seeing if that resolves the issue and lets me set a primary key?

[Jan]

if you sort by nPropertyID so that row is shown first you will be able to easily identify the corrupt rows, removing them will probably solve your problem, but as you say, backup first to be safe.

The primary keys are often lost when the database is in use when a program using it crashes, these same crashes can cause corrupt rows like these.

Regards,

[jmedinger]

OK - there appear to be 2 corrupt rows in the table (well, rows that look completely different to all the others in the content that's in them)
If I remove these I get another error message when I then try to set the primary key:-

The changes you requested to the table were not successful because they would 
create duplicate values in the index, primary key, or relationship. 
Change the data in the field or fields that contain the duplicate data, remove the
 index or redefine the index to permit duplicate entries and try again

I've no idea where this duplicate data now is... there are thousands of rows of data in the table

[Jan]

You can use SQL to work out which rows are valid and which aren't sometimes but I think you have reached the stage where you should contact Actinic and get them to look at the database.

Regards,

[jmedinger]

I think I agree with you there Jan

Thanks for the help though