SharePoint linked table - Integration rule violation

[vgarzon]

Hi all,

I'm very concerned about the error message I'm getting, because it seems to simple but yet I haven't been able to solve it....

What I'm trying to do is to make an Access file, that is going to be used by many people, that gets some accounts information (basic info as name, group and country) to build a form and then submmit the forms information into another SharePoint List.

Everything works fine, but I also need to make an automated form that helps me to update the Master Accounts list that I use, based on some Excel files.

I have already made the code to read the Excel files, store the needed fields into an Access table and it works fine. But when I try to do the same with a linked SharePoint List, instead of the table, I get the following message:


Error:
Microsoft Access can't Append all the record(s) to the table Microsoft Office Access (...) didn't add 4 record(s) due to validation rule violations
I have already checked the required fields, the fields sizes and types, but I can´t get it working.

Does anybody knows what could I be doing wrong?

Thanks in advance,

Victor

 

[gemma-the-husky]

the records cant be added , because some setting is violated

it might be a duplicate (although i think you get a different message)

most l likely you have a text field set as allow zero length = false. and you have new data with a blank in that particular field

do you know which records werent added - do an unmatched query to find them

 

[vgarzon]

Yes, some setting is violated. That's the point. Now I need to know how to do not to violate it.

I dont quite understand what you're saying about the zero length field. I'm not inserting nulls nor blanks in any field of any record, I've already tested that. The thing is that no record is added not that some records are not added. That is why I don't understand what could be the problem.

 

[gemma-the-husky]

Microsoft Access can't Append all the record(s) to the table Microsoft Office Access (...) didn't add 4 record(s) due to validation rule violations
I have already checked the required fields, the fields sizes and types, but I can´t get it working.

this message indicates that some were added, and some werent - although if there are only 4 records then obviously none would be added


definitely check your access table

there will definitely be something like

a) a field marked as required, where you have a blank
b) or a text field that says zerolength string = no, and you havent a value for it
c) or a number thats required, and you have a blank
d) or maybe a constraint (validation rule) that says a field must be in a certain range, and its out of range.

if theres only 4 records not being added. try to add the detaisl mnaually - then you will see what is wrong

i dont think a duplicate key would give that message, but it might. in that case the records you are trying to add may already be in the access table, and you have a unique/non-duplicates key stopping them being added

 

[vgarzon]

Hey, thanks for the advise.

I've already tested that. No length nor required, nor data type errors... Just that my SharePoint linked table seems not to be allowing me to enter data from Access.

The text that you're quoting is from a text that I made with just 4 records. That's why I get the "didn't add 4 record(s)" message specificly.

Any other ideas?

many thanks Gemma

 

[gemma-the-husky]

sorry, i thought you were trying to write to the access table

you are trying to write to the sharepoint table?

how do you make the connection - is this with an odbc driver? if so, i suspect that may be the issue. if the connection is not set up correctly. it may be readonly.

 

[vgarzon]

To add a linked table from SharePoint to a database in Access 2007 you only need to select your datasource. No ODBC connections need to be created.

I've also tryed unlinking and then linking the list again, but I always get the same message when trying to write on it from and Access query.

The only way I can write on the linked SharePoint list is through a new record addition (writing on a form's text fields and the using DoCmd.GoToRecord , , acNewRec), but it takes several second to insert just 1 record. Now imagine tat I need to insert about 7 thousand records... It would be hell.

Thanks a lot for your concern. I hope we can figure out how to deal with these validation rule violations.

Cheers,

Victor

 

[vgarzon]

Hey, good news!

After the last time I wrote you, I tyied another thing that worked.

What I tryied last time was to relink the SharePoint list. That did not work.

But then I tryed deleting the list from my Access DB, and linking the list again, and suddenly IT WORKED!

It is not working as fast as I want it, but that will do.

You made me came to think of that. Cheers!